JSP 2.0 + ORACLE 9i 下高效率分页的一个例子

import java.sql.Connection;
import java.sql.SQLException;
import javax.sql.DataSource;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;

public class DatabaseConn
{

public DatabaseConn()
{
}

public static synchronized Connection getConnection()throws Exception
{
Connection conn = null;
try
{
Context initCtx=new InitialContext();
Context envCtx=(Context)initCtx.lookup("java:comp/env");
DataSource ds=(DataSource)envCtx.lookup("jdbc/jspdev");
return ds.getConnection();
}
catch(SQLException e)
{
throw e;
}
catch(NamingException e)
{
throw e;
}
}
}




package com.cwbnig;

import java.sql.*;
import java.util.Vector;

public class OperateDB
{
private Connection conn=null;
private Statement stmt=null;
PreparedStatement pstmt=null;
CallableStatement cstmt=null;
private ResultSet rs=null;
private ResultSetMetaData rsmd=null;
private String strsql="";

public OperateDB()
{
try
{
conn = DatabaseConn.getConnection();
}
catch (Exception e)
{
System.out.println("Error:com.cwbnig.OperateDB:Structure Method");
}
}

public Connection getConnection() throws SQLException
{
return this.conn;
}

public ResultSet executeQuery(String sql) throws SQLException
{
stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
return stmt.executeQuery(sql);
}

public ResultSet listDataFromMSSQL(int curpage,int pagesize,String tablename,String sid,String con,String order) throws SQLException
{
int SELECTNUM=(curpage-1)*pagesize+1;
if(order.equalsIgnoreCase("asc"))
{
String min="SELECT MAX("+sid+") AS id FROM (SELECT TOP "+SELECTNUM+" "+sid+" FROM "+tablename+" ORDER BY "+sid+" ASC) tbl";

strsql="SELECT TOP "+pagesize+" * FROM "+tablename+" WHERE "+sid+" >="+min+con+" ORDER BY "+sid+" ASC";
}
else
{
String max="SELECT MIN("+sid+") AS id FROM (SELECT TOP "+SELECTNUM+" "+sid+" FROM "+tablename+" ORDER BY "+sid+" DESC) tbl";

strsql="SELECT TOP "+pagesize+" * FROM "+tablename+" WHERE "+sid+" <="+max+con+" ORDER BY "+sid+" DESC";
}
return this.executeQuery(strsql);
}

public ResultSet listDataFromMySQL(int curpage,int pagesize,String tablename,String sid,String con,String order) throws SQLException
{
int SELECTNUM=(curpage-1)*pagesize;
if(order.equalsIgnoreCase("asc"))
{
strsql="SELECT * FROM "+tablename+con+" ORDER BY "+sid+" ASC LIMIT "+SELECTNUM+","+pagesize;
}
else
{
strsql="SELECT * FROM "+tablename+con+" ORDER BY "+sid+" DESC LIMIT "+SELECTNUM+","+pagesize;
}
return this.executeQuery(strsql);
}

public ResultSet listDataFromOracle(int curpage,int pagesize,String tablename,String sid,String con,String order) throws SQLException
{
int SELECTNUM=(curpage-1)*pagesize+2;
if(order.equalsIgnoreCase("asc"))
{
String min="SELECT MAX("+sid+") AS SID FROM (SELECT "+sid+" FROM "+tablename+" WHERE ROWNUM <"+SELECTNUM+" ORDER BY "+sid+" ASC) TBL";
strsql="SELECT * FROM "+tablename+" WHERE "+sid+" >=("+min+") AND ROWNUM <"+(pagesize+1)+con+" ORDER BY "+sid+" ASC";
}
else
{
String max="SELECT MIN("+sid+") AS SID FROM (SELECT "+sid+" FROM "+tablename+" WHERE ROWNUM <"+SELECTNUM+" ORDER BY "+sid+" DESC) TBL";
strsql="(SELECT * FROM "+tablename+" WHERE "+sid+" <=("+max+") ORDER BY "+sid+" DESC)TBL2";
strsql="SELECT * FROM "+strsql+" WHERE ROWNUM<"+(pagesize+1)+con;
}
return this.executeQuery(strsql);
}

public Vector getDataFromMSSQL(int curpage,int pagesize,String tablename,String sid,String con,String order) throws Exception
{
Vector v=new Vector();
rs=this.listDataFromMSSQL(curpage,pagesize,tablename,sid,con,order);
rsmd=rs.getMetaData();
int colnum=rsmd.getColumnCount();
while(rs.next())
{
Object[] obj=new Object[colnum];
for(int i=0;i<colnum;i++)
{
obj[i]=new String(this.getCol(rs,rsmd.getColumnType(i+1),i+1));
}
v.add(obj);
}
stmt.close();
rs.close();
conn.close();
return v;
}

public Vector getDataFromMySQL(int curpage,int pagesize,String tablename,String sid,String con,String order) throws Exception
{
Vector v=new Vector();
rs=this.listDataFromMySQL(curpage,pagesize,tablename,sid,con,order);
rsmd=rs.getMetaData();
int colnum=rsmd.getColumnCount();
while(rs.next())
{
Object[] obj=new Object[colnum];
for(int i=0;i<colnum;i++)
{
obj[i]=new String(this.getCol(rs,rsmd.getColumnType(i+1),i+1));
}
v.add(obj);
}
stmt.close();
rs.close();
conn.close();
return v;
}

public Vector getDataFromOracle(int curpage,int pagesize,String tablename,String sid,String con,String order) throws Exception
{
Vector v=new Vector();
rs=this.listDataFromOracle(curpage,pagesize,tablename,sid,con,order);
rsmd=rs.getMetaData();
int colnum=rsmd.getColumnCount();
while(rs.next())
{
Object[] obj=new Object[colnum];
for(int i=0;i<colnum;i++)
{
obj[i]=new String(this.getCol(rs,rsmd.getColumnType(i+1),i+1));
}
v.add(obj);
}
stmt.close();
rs.close();
conn.close();
return v;
}

public String getCol(ResultSet rs,int type,int colNum)throws Exception
{
String ret="";
switch(type)
{
case(1):ret=rs.getString(colNum);break;
case(2):ret=rs.getString(colNum);break;
case(4):ret=String.valueOf(rs.getInt(colNum));break;
case(5):ret=String.valueOf(rs.getInt(colNum));break;
case(6):ret=String.valueOf(rs.getFloat(colNum));break;
case(8):ret=String.valueOf(rs.getDouble(colNum));break;
case(12):ret=rs.getString(colNum);break;
default:ret="not know";
}
return ret;
}

public Object[] getColName(String tablename)throws Exception
{
stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
rs=stmt.executeQuery("SELECT * FROM "+tablename+" WHERE ROWNUM<2");
rsmd=rs.getMetaData() ;
int colnum=rsmd.getColumnCount();
Object[] obj=new Object[colnum];
for(int i=0;i<colnum;i++)
{
obj[i]=rsmd.getColumnName(i+1);
}
return obj;
}

public int getRowCount(String tablename) throws SQLException
{
stmt=conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_READ_ONLY);
strsql="SELECT COUNT(*) AS COUNT FROM "+tablename;
rs=stmt.executeQuery(strsql);
if(rs.next())
{
return rs.getInt(1);
}
else
{
return 0;
}
}

public int getRowCount(ResultSet rs) throws SQLException
{
int rowCount = 0;
rs.last();
rowCount = rs.getRow();
rs.beforeFirst();
return rowCount;
}

public int executeUpdate(String sql) throws SQLException
{
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);

return stmt.executeUpdate(sql);
}

public int[] executeUpdateBatch(String[] sqls) throws SQLException
{
conn.setAutoCommit(false);
stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,ResultSet.CONCUR_UPDATABLE);
try
{
for (int i = 0; i < sqls.length; i++)
{
stmt.addBatch(sqls[i]);
}
int[] updateCounts=stmt.executeBatch();
conn.commit();
conn.setAutoCommit(true);
return updateCounts;
}
catch (SQLException e)
{
conn.rollback();
throw e;
}
}

public PreparedStatement prepareStatement(String sql) throws SQLException
{
pstmt = conn.prepareStatement(sql);
return pstmt;
}

public CallableStatement callableStatement(String sql) throws SQLException
{
cstmt = conn.prepareCall(sql);
return cstmt;
}

public void setAutoCommit(boolean s) throws SQLException
{
conn.setAutoCommit(s);
}

public void commit() throws SQLException
{
conn.commit();
}

public void rollback() throws SQLException
{
conn.rollback();
}

public void close() throws SQLException
{
if (stmt != null)
{
stmt.close();
stmt = null;
}
if (conn != null)
{
conn.close();
conn = null;
}
}
}



<%@ page contentType="text/html; charset=gb2312" language="java" errorPage="errorinfo.jsp"%>
<html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=gb2312">
<title>testOracle</title>
</head>
<body>
<jsp:useBean id="operatedb" scope="page" class="com.cwbnig.OperateDB"/>
<%
Object[] obj=null;
obj=operatedb.getColName("TBL_STRUCTS");
for(int temp=0;temp<obj.length;temp++)
{
out.println(obj[temp]);
}

out.println("<br>");

java.util.Vector v=operatedb.getDataFromOracle(1,10,"TBL_STRUCTS","SID","","desc");
java.util.Enumeration e=v.elements();
while(e.hasMoreElements())
{
obj=(Object[])e.nextElement();
for(int i=0;i<obj.length;i++)
{
out.println(obj[i]);
}
out.print("<br>");
}
%>
</table>
<br>
<a href="testOracle.do?forward=bdgk">testOracle.do?forward=bdgk</a>
</body>
</html>

原文转自：http://www.ltesting.net