分页技术(4)
来源:互联网 发布:网络推手公司哪个好 编辑:程序博客网 时间:2024/05/22 03:31
-
- public RowSetPage executeQuery() throws SQLException{
- System.out.println("executeQueryUsingPreparedStatement");
- Connection conn = DBUtil.getConnection();
- PreparedStatement pst = null;
- ResultSet rs = null;
- try{
- pst = conn.prepareStatement(this.countSQL);
- setParams(pst);
- rs =pst.executeQuery();
- if (rs.next()){
- totalCount = rs.getInt(1);
- } else {
- totalCount = 0;
- }
- rs.close();
- pst.close();
- if (totalCount < 1 ) return RowSetPage.EMPTY_PAGE;
- pst = conn.prepareStatement(this.querySQL);
- System.out.println(querySQL);
- pst.setFetchSize(this.pageSize);
- setParams(pst);
- rs =pst.executeQuery();
- //rs.setFetchSize(pageSize);
- this.rowSet = populate(rs);
- rs.close();
- rs = null;
- pst.close();
- pst = null;
- this.rowSetPage = new RowSetPage(this.rowSet,startIndex,totalCount,pageSize);
- return this.rowSetPage;
- }catch(SQLException sqle){
- //System.out.println("executeQuery SQLException");
- sqle.printStackTrace();
- throw sqle;
- }catch(Exception e){
- e.printStackTrace();
- throw new RuntimeException(e.toString());
- }finally{
- //System.out.println("executeQuery finally");
- DBUtil.close(rs, pst, conn);
- }
- }
-
- protected abstract RowSet populate(ResultSet rs) throws SQLException;
-
- public javax.sql.RowSet getRowSet(){
- return this.rowSet;
- }
-
- public RowSetPage getRowSetPage() {
- return this.rowSetPage;
- }
-
- public void close(){
- //因为数据库连接在查询结束或发生异常时即关闭,此处不做任何事情
- //留待扩充。
- }
- private class BoundParam {
- int index;
- Object value;
- int sqlType;
- int scale;
- public BoundParam(int index, Object value) {
- this(index, value, java.sql.Types.OTHER);
- }
- public BoundParam(int index, Object value, int sqlType) {
- this(index, value, sqlType, 0);
- }
- public BoundParam(int index, Object value, int sqlType, int scale) {
- this.index = index;
- this.value = value;
- this.sqlType = sqlType;
- this.scale = scale;
- }
- public boolean equals(Object obj){
- if (obj!=null && this.getClass().isInstance(obj)){
- BoundParam bp = (BoundParam)obj;
- if (this.index==bp.index) return true;
- }
- return false;
- }
- }
- }
- ///////////////////////////////////
- //
- // PagedStatementOracleImpl.java
- // author: evan_zhao@hotmail.com
- //
- ///////////////////////////////////
- package page;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import javax.sql.RowSet;
- import oracle.jdbc.rowset.OracleCachedRowSet;
- public class PagedStatementOracleImpl extends PagedStatement {
-
- public PagedStatementOracleImpl(String sql){
- super(sql);
- }
-
- public PagedStatementOracleImpl(String sql, int pageNo){
- super(sql, pageNo);
- }
-
- public PagedStatementOracleImpl(String sql, int pageNo, int pageSize){
- super(sql, pageNo, pageSize);
- }
-
- protected String intiQuerySQL(String sql, int startIndex, int size){
- StringBuffer querySQL = new StringBuffer();
- if (size != super.MAX_PAGE_SIZE) {
- querySQL.append("select * from (select my_table.*,rownum as my_rownum from(")
- .append( sql)
- .append(") my_table where rownum<").append(startIndex + size)
- .append(") where my_rownum>=").append(startIndex);
- } else {
- querySQL.append("select * from (select my_table.*,rownum as my_rownum from(")
- .append(sql)
- .append(") my_table ")
- .append(") where my_rownum>=").append(startIndex);
- }
- return querySQL.toString();
- }
-
- protected RowSet populate(ResultSet rs) throws SQLException{
- OracleCachedRowSet ocrs = new OracleCachedRowSet();
- ocrs.populate(rs);
- return ocrs;
- }
- }