jdbc分页程序.txt

JDBC分页JDBC分页JDBC分页JDBC分页JDBC分页JDBC分页

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);

}

}

}