分页resultset

一、定义Pageable接口 package com.teksun.common.dao; public interface Pageable extends java.sql.ResultSet { public int getPageCount(); public int getPageRowsCount(); public int getPageSize(); public void gotoPage(int page) ; public void setPageSize(int pageSize); public int getRowsCount(); public int getPageFirstNum(); public int getPageLastNum(); public void pageFirst() throws java.sql.SQLException; public void pageLast() throws java.sql.SQLException; public int getCurPage(); } 二、定义接口实现类PageableResultSet package com.teksun.common.dao; import java.io.InputStream; import java.io.Reader; import java.math.BigDecimal; import java.net.URL; import java.sql.Array; import java.sql.Blob; import java.sql.Clob; import java.sql.Date; import java.sql.NClob; import java.sql.Ref; import java.sql.ResultSetMetaData; import java.sql.RowId; import java.sql.SQLException; import java.sql.SQLWarning; import java.sql.SQLXML; import java.sql.Statement; import java.sql.Time; import java.sql.Timestamp; import java.util.Calendar; import java.util.Map; public class PageableResultSet implements Pageable { // 数据集 protected java.sql.ResultSet rs=null; // 数据集总行数 protected int rowsCount; // 每页数量 protected int pageSize = 0; // 当前页 protected int curPage = 1; //构造方法： public PageableResultSet(java.sql.ResultSet rs) throws java.sql.SQLException { if (rs == null) { throw new SQLException("given ResultSet is NULL"); } rs.last(); rowsCount = rs.getRow(); rs.beforeFirst(); this.rs = rs; } @Override public int getPageCount() { if (rowsCount == 0)  { return 0; } if (pageSize == 0) { //pageSize==0不分页 return 1; } //calculate PageCount double tmpD = (double)rowsCount/pageSize; int tmpI = (int)tmpD; if (tmpD > tmpI) { tmpI++ ; } return tmpI; } @Override public int getPageRowsCount() { if (pageSize == 0) { //pageSize==0不分页 return rowsCount; } if (getRowsCount() == 0) { return 0; } if (curPage != getPageCount()) { // 不是最后一页 return pageSize; } // 是最后一页 return rowsCount - (getPageCount() - 1)*pageSize; } @Override public int getPageSize() { return pageSize; } @Override public void gotoPage(int page) { if (rs == null) { return; } if (page < 1) { page = 1; } if (page > getPageCount()) { page = getPageCount(); } int row = (page - 1) * pageSize + 1; try { rs.absolute(row); curPage = page; } catch (java.sql.SQLException e) { } } @Override public void setPageSize(int pageSize) { if (pageSize >= 0) { this.pageSize = pageSize; curPage=1; } }  @Override public int getRowsCount() { return rowsCount; } @Override public int getPageFirstNum() { return (curPage - 1)*pageSize + 1; } @Override public int getPageLastNum() { return (curPage - 1) * pageSize + getPageRowsCount(); } @Override public void pageFirst() throws java.sql.SQLException { rs.absolute(getPageFirstNum()); } @Override public void pageLast() throws java.sql.SQLException { rs.absolute(getPageLastNum()); } @Override public int getCurPage() { return curPage; } @Override public boolean next() throws SQLException { return rs.next(); } //......很多ResultSet的接口都需要Override @Override public <T> T unwrap(Class<T> iface) throws SQLException { return rs.unwrap(iface); } } 三、使用例 注意事项：prepareStatement的时候需要传参数，使executeQuery返回的ResultSet可以双向滚动。默认是单向的，只能next。 PreparedStatement ps = null; Pageable prs = null; try { // 数据库连接 Connection dbconn = DbcpConnection.getConnection(); //ResultSet.TYPE_FORWARD_ONLY 结果集只能通过rs.next();方法逐条读取 //ResultSet.TYPE_SCROLL_INSENSITIVE 双向滚动，但不及时更新，就是如果数据库里的数据修改过，并不在ResultSet中反应出来。 //ResultSet.TYPE_SCROLL_SENSITIVE 双向滚动，并及时跟踪数据库里的更新 //ResultSet.CONCUR_READ_ONLY 只读取ResultSet   //ResultSet.CONCUR_UPDATABLE 用ResultSet更新数据库  ps = dbconn.prepareStatement(sqlStr, ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY); if (queryCondition != null) { int i = 1; for (String condition : queryCondition) { ps.setString(i*2-1, condition); ps.setString(i*2, condition); i++; } } // 分页检索 prs = new PageableResultSet(ps.executeQuery()); // 设置每页显示数量 prs.setPageSize(page_size); // 跳转到当前页 prs.gotoPage(curPage); //根据检索结果，拼接结果列表的html内容 for (int i = 0; i < prs.getPageRowsCount(); i++) { returnValue.append("<tr>"); for (int j = 0; j < colNum; j++) { String field = (String)prs.getObject(j + 1); returnValue.append("<td align=\"center\" valign=\"middle\">"); returnValue.append(field == null ? " ":field); returnValue.append("</td>"); } returnValue.append("</tr>"); prs.next(); } //根据检索结果，拼接分页链接的html内容 returnValue.append(getPageLink(prs, colNum)); DbcpConnection.closeConn(dbconn); } catch (SQLException e) { log.fatal("数据库异常。", e); } finally { if(prs != null){ try { prs.close(); } catch (SQLException e) { } } if(ps != null){ try { ps.close(); } catch (SQLException e) { } } }