实现数据库查询分页Mysql

 

//最近在笔试中遇到了一个要求写连接数据库及数据分页的程序的问题，当时只是写出了数据库连接的code，现经过搜集资料，列出程序:

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) + " ");
        }
    }

}