這個分頁代碼基于 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.aygfsteel.com/beansoft/archive/2007/10/23/155318.html