这个分页代码基于 JDBC 2.0 的滚动游标的机制, 核心观念就是利用 ResultSet 类里面的 boolean absolute( int row ) throws SQLException 方法进行数据的跳转.
经过测试(数据小于1万条, SQL Server 2000), 这个方法比用复合 SQL 语句查询分页的方案要快很多.
详细 JavaDoc 如下:
Moves the cursor to the given row number in this ResultSet object.
If the row number is positive, the cursor moves to the given row number with respect to the beginning of the result set. The first row is row 1, the second is row 2, and so on.
If the given row number is negative, the cursor moves to an absolute row position with respect to the end of the result set. For example, calling the method absolute(-1) positions the cursor on the last row; calling the method absolute(-2) moves the cursor to the next-to-last row, and so on.
An attempt to position the cursor beyond the first/last row in the result set leaves the cursor before the first row or after the last row.
Note: Calling absolute(1) is the same as calling first(). Calling absolute(-1) is the same as calling last().
Parameters:
row the number of the row to which the cursor should move. A positive number indicates the row number counting from the beginning of the result set; a negative number indicates the row number counting from the end of the result set
Returns:
true if the cursor is on the result set; false otherwise
Throws:
SQLException if a database access error occurs, or the result set type is TYPE_FORWARD_ONLY
@since
1.2
/*
* @(#)Pager.java 1.00 2004-8-12
*
* Copyright 2004 . All rights reserved.
* PROPRIETARY/CONFIDENTIAL. Use is subject to license terms.
*/
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.Vector;
/**
* Pager, 基于 JDBC 2.0 滚动机制的分页程序, 在 MySQL, SQLServer, Access, Oracle 下测试通过.
* @author 刘长炯
* @version 1.0 2004-8-12
*/
public class Pager {
/** Used database connection */
Connection conn = null;
public Pager() {
}
/**
* 分页功能, 返回当页的数据(JDBC 2.0 实现).
*
* @param currentPage
* 当前页面数(取值范围: 从 1 开始有效, 0 自动改为 1)
* @param pageCount
* 每页显示记录数
*
* @return a Vector - 数据列表
*/
public Vector pageData(int currentPage, int pageCount) {
Vector results = new Vector();
String tableName = "table_name";// 要处理的表格名
ResultSet rs = null;
String sql = "SELECT * FROM " + tableName;
Statement stmt = null;
try {
// TODO: open connection
// 生成可滚动的结果集表达式
stmt = conn.createStatement(ResultSet.
TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
rs = stmt.executeQuery(sql);
int count = recordCount(); // 总记录数
int totalPage = (int) Math.ceil(1.0 * count / pageCount); // 总页面数
if (currentPage <= 0) {
currentPage = 1;
}
// 超出页码范围, 不返回数据
if (currentPage > totalPage) {
currentPage = totalPage;
return results;
}
if ((currentPage - 1) * pageCount > 0) {
// 移动结果集数据到当前页
rs.absolute((currentPage - 1) * pageCount);
}
// rs.absolute(0); 在 ODBC 下会导致如下异常:java.sql.SQLException: Cursor
// position (0) is invalid
int i = 0; // Readed pages
while (rs.next() && i < pageCount) {
i++;
// TODO: Read each row and process to value object
ValueObject bean = new ValueObject();
// TODO: Read value to value object
result.add(bean);
}
} catch (Exception exception) {
System.out.println("Occur a error in " + getClass()
+ ".pageData() : " + exception.getMessage());
// exception.printStackTrace();
} finally {
closeJDBCResource(stmt);
closeJDBCResource(rs);
closeJDBCResource(conn);
}
return results;
}
/**
* 返回当前数据库中记录的总数.
*
* @return int 记录总数
*/
public int recordCount() {
int allCount = -1;
String tableName = "table_name";// 要处理的表格名
String sql = "SELECT COUNT(*) FROM " + tableName;
ResultSet rs = null;
Statement stmt = null;
try {
// TODO: open connection
stmt = conn.createStatement();
rs = stmt.executeQuery(sql);
if (rs.next()) {
allCount = rs.getInt(1);
}
} catch (Exception exception) {
System.out
.println("Occur a error in " + getClass()
+ ".recordCount() : " + exception.getMessage());
} finally {
closeJDBCResource(stmt);
closeJDBCResource(rs);
closeJDBCResource(conn);
}
return allCount;
}
/**
* Close a jdbc resource, such as ResultSet, Statement, Connection.... All
* these objects must have a method signature is void close().
*
* @param resource -
* jdbc resouce to close
*/
public static void closeJDBCResource(Object resource) {
try {
Class clazz = resource.getClass();
java.lang.reflect.Method method = clazz.getMethod("close", null);
method.invoke(resource, null);
} catch (Exception e) {
e.printStackTrace();
}
}
/**
* Test page.
* @param args
*/
public static void main(String[] args) {
// 分页, 读取第一页数据, 共读取5个记录
Vector data = new Pager().pageData(1, 5);
// TODO: process value object, 更改类名
for(int i = 0; results != null && i < data.size(); i++) {
ValueObject bean = (ValueObject)data.get(i);
}
}
}
文章来源:
http://www.blogjava.net/beansoft/archive/2007/10/23/155318.html