pager.java

地址薄

package javabean;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Vector;

import forms.LinkManForm;
/**
 * Pager, 基于 JDBC 2.0 滚动机制的分页程序, 在MySQL, SQLServer, Access, Oracle 下测试通过.
 * 
 * @author 刘长炯
 * @version 1.0 2004-8-12
 */
public class Pager {
	/** Used database connection */
	private String driverName="com.microsoft.jdbc.sqlserver.SQLServerDriver";
	private String userName="sa";
	private String userPwd="";
	
	private String dbName="test";
//	jdbc:microsoft:sqlserver://127.0.0.1:1433;DatabaseName=test;User=sa;Password=pwd
	private String url="jdbc:microsoft:sqlserver://localhost:1433;DataBaseName="+
	dbName+";User="+userName+";Password="+userPwd;
	
	Connection conn = null;
    private  Statement sm=null;
    private int pageSize;
    private String querySql;
    private String countSql;
    
	public Pager() {
		
	}
	
	public String getQuerySql() {
		return querySql;
	}

	public void setQuerySql(String querySql) {
		this.querySql = querySql;
	}

	public String getCountSql() {
		return countSql;
	}

	public void setCountSql(String countSql) {
		this.countSql = countSql;
	}

	public Pager(int pageSize)
	{
		this.pageSize=pageSize;
	}

	public int getPageSize() {
		return pageSize;
	}

	public void setPageSize(int pageSize) {
		this.pageSize = pageSize;
	}

	public void ConnectDB()
	{
		try {
			Class.forName(driverName).newInstance();
			conn=DriverManager.getConnection(url);
			sm=conn.createStatement();
//			System.out.print("数据库连接成功");
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch(SQLException e)
		{
			e.printStackTrace();
//			System.out.print("数据库连接失败");
		}
		
		
	}
	
	public void CloseDB()
	{
		try {
	        if(sm!=null)
	        {
	        	sm.close();
	        	
	        }
	        conn.close();
		}
		catch(SQLException e)
		{
			e.printStackTrace();
//			System.out.print("数据库关闭失败");
		}
	}
	
	/**
	 * 分页功能, 返回当页的数据(JDBC 2.0 实现).
	 * 
	 * @param currentPage
	 *            当前页面数(取值范围: 从 1 开始有效, 0 自动改为 1)
	 * @param pageSize
	 *            每页显示记录数
	 * 
	 * @return a Vector - 数据列表
	 */
	public Vector pageData(int currentPage) {
		this.ConnectDB();
		Vector results = new Vector();
	//	String tableName = "linkman";// 要处理的表格名
		ResultSet rs = null;
	//	querySql = "SELECT * FROM " + tableName;
		Statement stmt = null;
		try {
			// TODO: open connection
			// 生成可滚动的结果集表达式
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
					ResultSet.CONCUR_READ_ONLY);
			rs = stmt.executeQuery(querySql);
			int count = getTotalCount(); // 总记录数
			int totalPage = ((count + pageSize) - 1) / pageSize; // 总页面数
			if (currentPage <= 0) {
				currentPage = 1;
			}
			// 超出页码范围, 不返回数据
			if (currentPage > totalPage) {
				currentPage = totalPage;
				return results;
			}
			if ((currentPage - 1) * pageSize > 0) {
				// 移动结果集数据到当前页
				rs.absolute((currentPage - 1) * pageSize);
			}
			// rs.absolute(0); 在 ODBC 下会导致如下异常:java.sql.SQLException: Cursor
			// position (0) is invalid
			int i = 0; // Readed pages
			while (rs.next() && i < pageSize) {
				i++;
				// TODO: Read each row and process to value object
				LinkManForm bean = new LinkManForm();
				bean.setId(rs.getInt("id"));
				bean.setUserId(rs.getInt("userid"));
				bean.setName(rs.getString("name"));
				bean.setSex(rs.getInt("sex"));
				bean.setPhone(rs.getString("phone"));
				bean.setAddress(rs.getString("address"));
				// TODO: Read value to value object
				results.add(bean);
			}
		} catch (Exception exception) {
			System.out.println("Occur a error in " + getClass()
					+ ".pageData() : " + exception.getMessage());
			// exception.printStackTrace();
		} finally {
			this.CloseDB();
		}
		return results;
	}

	/**
	 * 返回当前数据库中记录的总数.
	 * 
	 * @return int 记录总数
	 */
	public int getTotalCount() {
		this.ConnectDB();
		int allCount = -1;
	//	String tableName = "linkman";// 要处理的表格名
	//	countSql = "SELECT COUNT(*) FROM " + tableName;
		ResultSet rs = null;
		Statement stmt = null;
		try {
			// TODO: open connection
			stmt = conn.createStatement();
			rs = stmt.executeQuery(countSql);
			if (rs.next()) {
				allCount = rs.getInt(1);
			}
		} catch (Exception exception) {
			System.out.println("Occur a error in " + getClass()
					+ ".recordCount() : " + exception.getMessage());
		} finally {
			
			this.CloseDB();
		}
		return allCount;
	}

	/**
	 * 获取总页面数.
	 * 
	 * @param pageSize
	 *            一页显示数据量
	 * @return 页面总数
	 */
	public int getTotalPage() {
		int totalCount = getTotalCount();
		// 得到页面总数
		int totalPageCount = ((totalCount + pageSize) - 1) / pageSize;
		return totalPageCount;
	}
	

	/**
	 * Test page.
	 * 
	 * @param args
	 */
	public static void main(String[] args) {
		// 分页, 读取第一页数据, 共读取5 个记录
		Pager pager=new Pager(3);
		if(pager.getCountSql()==null)
			System.out.println("null");
		//System.out.println(pager.getQuerySql()+" "+pager.getCountSql());
	}
}