CSDN博客

img cwbnig

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

发表于2004/9/15 16:16:00  1068人阅读

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 * 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>

0 0

相关博文

我的热门文章

img
取 消
img