JSP 2.0 + ORACLE 9i An example of efficient paging

js|oracle| Paging 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= (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 order) con,string SQLException
{
int selectnum= (curpage-1) *pagesize+1;
if (Order.equalsignorecase ("ASC"))
{
String min= "Select MAX" ("+sid+") as ID from (SELECT top "+selectnum+" "+sid+" to "+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 order) con,string 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 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" +s Id+ "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" +s Id+ "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);
Case (2): Ret=rs.getstring (Colnum);
Case (4): Ret=string.valueof (Rs.getint (colnum));
Case (5): Ret=string.valueof (Rs.getint (colnum));
Case (6): Ret=string.valueof (Rs.getfloat (colnum));
Case (8): Ret=string.valueof (rs.getdouble (colnum));
Case (a): Ret=rs.getstring (Colnum);
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"%>
<meta http-equiv= "Content-type" content= "text/html; charset=gb2312 ">
<title>testOracle</title>
<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>