An example of high efficiency paging under JSP 2.0 + Oracle 9i

Package com.cwbnig;

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 * " 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.EQUALSINORECASE ("ASC")) {string min = "SELECT MAX (" SID ") AS SID FROM (SID" SID "from" Tablename "Where rownum < selectnum " ORDER BY " SID " ASC) TBL "; strsql =" SELECT * " 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 * " TABLENAME " 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 ( {Object [] obj = new object [color]; for (int i = 0; i

Rs.getMetadata (); int colnum = rsmd.getColumnCount (); while (rs.next ()) {object [] obj = new object [color]; for (int i = 0; i

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

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 SQLIC VOID Close () THROWS SQLIC VOID Close () THROWS SQLIC VOID Close () THROWS SQLIC VOID CLOSE () {stmt.close (); stmt = null;} if (conn! = null) { CONN.CLOSE (); conn = null;}}}