基于_JDBC_2.0_驱动的分页代码实现

最新推荐文章于 2025-10-21 22:09:11 发布

转载最新推荐文章于 2025-10-21 22:09:11 发布 · 1.6k 阅读

文章标签：

#jdbc #exception #vector #sqlserver #string #database

技术文章同时被 2 个专栏收录

52 篇文章

订阅专栏

转载

5 篇文章

订阅专栏

本文介绍了一种使用JDBC2.0滚动游标机制实现数据库分页的方法，该方法在MySQL、SQLServer、Access、Oracle等数据库下经过测试验证，相较于复合SQL语句查询分页方案，性能更优。文中提供了详细的JavaDoc解释和代码实现，包括如何使用ResultSet的absolute方法定位结果集中的特定行，以及如何计算总记录数和总页数。此外，还包含了一个简单的测试方法来验证分页功能。

这个分页代码基于 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