MySQL数据库查询分页实现

package com.jdbc.page;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

/**
 * 注：FinalParm.PAGER_ECORD 為每頁顯示數據量這裡為3
 * @author Administrator
 *
 */

public class JDBCPageTest {

	private Connection conn = null;

	private PreparedStatement pre = null;

	private ResultSet rs = null;

	public JDBCPageTest() {
		this.setConection();
	}

	/**
	 * 获得连接对象
	 */
	public void setConection() {
		try {
			Class.forName("org.gjt.mm.mysql.Driver");
			conn = DriverManager.getConnection(
					"jdbc:mysql://localhost:3306/test", "root", "");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 获得总页数
	 * 注：主要用於頁面跳轉傳參和總頁數顯示
	 * @return
	 */
	public int getTotalPage() throws Exception {
		int totalPage = 0;	//总页数
		int totalRecord = 0; //总记录数
		String sql = "select count(*) as totalRecord from t_user";
		pre = conn.prepareStatement(sql);
		rs = pre.executeQuery();
		while (rs.next()) {
			totalRecord = rs.getInt("totalRecord");
		}
		System.out.println("总记录数:" + totalRecord);

		//根据总记录数算出总页数
		if (totalRecord % FinalParm.PAGER_ECORD == 0) {
			totalPage = totalRecord / FinalParm.PAGER_ECORD;
		} else {
			totalPage = totalRecord / FinalParm.PAGER_ECORD + 1;
		}
		return totalPage;
	}

	/**
	 * 获得每页数据 注：根据不同的数据库其SQL分页语句也有不同 这里采用MySQL数据库
	 * 这里将查出数据放到一个LIST，具体返回根据自己业务而定
	 * @param currentPage
	 * @return
	 */
	public List getReslutList(int currentPage) throws Exception {
		List list = new ArrayList();
		String sql = "select * from t_user limit " + (currentPage - 1)
				* FinalParm.PAGER_ECORD + "," + FinalParm.PAGER_ECORD + "";
		pre = conn.prepareStatement(sql);
		rs = pre.executeQuery();
		while (rs.next()) {
			list.add(rs.getInt("id"));
			list.add(rs.getString("name"));
			list.add(rs.getString("password"));
		}
		return list;
	}

	/**
	 * 关闭所有对象
	 * 
	 * @throws Exception
	 */
	public void closeAll() throws Exception {
		if (rs != null) {
			rs.close();
		} else if (pre != null) {
			pre.close();
		} else if (conn != null) {
			conn.close();
		}
	}

	/**
	 * @测试 列出第二页的数据
	 */
	public static void main(String[] args) throws Exception {

		JDBCPageTest test = new JDBCPageTest();
		System.out.println("总页数：" + test.getTotalPage());
		List list = test.getReslutList(2);
		for (int i = 0; i < list.size(); i++) {
			System.out.print(list.get(i) + " ");
		}
	}

}