Mysql 通用分页

第一步，建立数据库：

create table student(
    id int primary key auto_increment,
    code varchar(50),
    name varchar(50),
    sex varchar(10),
    age int,
    political varchar(50),
    origin varchar(50),
    professional varchar(50)
) ;

insert into student(code, name, sex, age, political, origin, professional)
values('200820459432', 'zhangsan', 'male', 24, 'tuan_yuan','China', 'SoftWare') ;
insert into student(code, name, sex, age, political, origin, professional)
values('200820233442', 'lisi', 'femal', 23, 'dang_yuan','China', 'Computer') ;

第二步，建立javabean：

package com.page.domain ;

public class Student {

    private Integer id ;

    private String code ;

    private String name ;

    private String sex ;

    private Integer age ;

    private String political ;

    private String origin ;

    private String professional ;

    public String toString(){
        return "id : " + id + ";
code : " + code + ";
name : " + name + ";
sex : " +sex+
                ";
age : " +age+ ";
political : " +political+ ";
origin : "
                + origin +";
professional : " + professional;
    } ;

    public int getId() {
        return id;
    }

    public void setId(int id) {
        this.id = id;
    }

    public String getCode() {
        return code;
    }

    public void setCode(String code) {
        this.code = code;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getPolitical() {
        return political;
    }

    public void setPolitical(String political) {
        this.political = political;
    }

    public String getOrigin() {
        return origin;
    }

    public void setOrigin(String origin) {
        this.origin = origin;
    }

    public String getProfessional() {
        return professional;
    }

    public void setProfessional(String professional) {
        this.professional = professional;
    }

}

 第三步，写分页工具page.java和domainPage.java

package com.ext.util;

import java.util.* ;
public class Page {

    //结果集
    private List<?> list ;

    //查询总记录数
    private int totalRecords ;

    //每页多少条数据
    private int pageSize ;

    //第几页
    private int pageNo ;

    /**
     * 总页数
     * @return
     */
    public int getTotalPages(){
        return (totalRecords + pageSize -1) / pageSize ;
    }

    /**
     * 取得首页
     * @return
     */
    public int getTopPageNo(){
        return 1 ;
    }

    /**
     * 上一页
     * @return
     */
    public int getPreviousPageNo(){
        if(pageNo <= 1){
            return 1 ;
        }
        return pageNo - 1 ;
    }

    /**
     * 下一页
     * @return
     */
    public int getNextPageNo(){
        if(pageNo >= getBottomPageNo()){
            return getBottomPageNo() ;
        }
        return pageNo + 1 ;
    }

    /**
     * 取得尾页
     * @return
     */
    public int getBottomPageNo(){
        return getTotalPages() ;
    }

    public List<?> getList() {
        return list;
    }

    public void setList(List<?> list) {
        this.list = list;
    }

    public int getTotalRecords() {
        return totalRecords;
    }

    public void setTotalRecords(int totalRecords) {
        this.totalRecords = totalRecords;
    }

    public int getPageSize() {
        return pageSize;
    }

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

    public int getPageNo() {
        return pageNo;
    }

    public void setPageNo(int pageNo) {
        this.pageNo = pageNo;
    }
}

 package com.ext.util;

import java.lang.reflect.Field;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import com.ext.util.DBUtil;
import com.ext.util.Page;

public class DomainPage {

    private static Class<?> c ;
    private static String tableName ;
    private static Field[] field ;
    private static String[] attributes ;

    private static void init(String domainClass) throws Exception{
        c = Class.forName(domainClass) ;
        tableName = c.getSimpleName() ;
        field = c.getDeclaredFields() ;
        attributes = new String[field.length] ;
        for(int i=0; i<field.length; i++){
            attributes[i] = field[i].getName() ;
        }
    }

    public static Page getDomainPage(Connection conn, int pageNo, int pageSize, String domainClass, Object sort) throws Exception{
        Page page = null ;
        List list = null ;
        Object domainObj = null ;
        Object attributeObj = null ;
        String sql = null ;
        PreparedStatement pstmt = null ;
        ResultSet rs = null ;

        init(domainClass) ;
        sql = getSql() ;


        pstmt = conn.prepareStatement(sql) ;
        pstmt.setObject(1, sort) ;
        pstmt.setInt(2, (pageNo-1)*pageSize) ;
        pstmt.setInt(3, pageNo*pageSize) ;
        rs = pstmt.executeQuery() ;
        list = new ArrayList() ;
        while(rs.next()){
            domainObj = c.newInstance() ;
            for(int i=0; i<field.length; i++){
                field[i].getClass() ;
                attributeObj = rs.getObject(field[i].getName()) ;
                field[i].setAccessible(true) ;
                field[i].set(domainObj, attributeObj) ;
            }
            list.add(domainObj) ;
        }
        page = new Page() ;
        page.setList(list) ;
        page.setTotalRecords(getTotalRecords(conn,tableName)) ;
        page.setPageNo(pageNo) ;
        page.setPageSize(pageSize) ;


        return page ;
    }

    private static String getSql(){
        StringBuffer sbSql = new StringBuffer("select ") ;
        for(int i=0; i<field.length; i++){
            sbSql.append(attributes[i]) ;
            if(i<field.length-1){
                sbSql.append(", ") ;
            }
        }
        sbSql.append(" from ")
             .append(tableName)
             .append(" order by ? limit ?,?") ;
        return sbSql.toString() ;
    }

    private static int getTotalRecords(Connection conn, String tableName) throws Exception{
        String sql = "select count(*) from " +  tableName;
        PreparedStatement pstmt = null ;
        ResultSet rs = null ;
        int count = 0 ;
        try{
            pstmt = conn.prepareStatement(sql) ;
            rs = pstmt.executeQuery() ;
            rs.next() ;
            count = rs.getInt(1) ;
        }finally{
            DBUtil.close(rs) ;
            DBUtil.close(pstmt) ;
        }
        return count ;
    }
}

 第四步：写测试代码：

package com.domain.manager;

import java.sql.Connection;
import java.util.Iterator;

import com.ext.util.DBUtil;
import com.ext.util.DomainPage;
import com.ext.util.Page;
import com.page.domain.Student;

public class StudentManager {

    public static void main(String[] args) throws Exception {
    Student stu = new Student() ;
    Connection conn = DBUtil.getConnection() ;
    Page page = DomainPage.getDomainPage(conn, 1, 10, Student.class.getName(), "id") ;
    for(Iterator<?> iter = page.getList().iterator(); iter.hasNext();){
        stu = (Student) iter.next() ;
        System.out.println(stu) ;
    }
}

}

 第五步，就可以看到运行结果了：

id : 1;
code : 200820459432;
name : zhangsan;
sex : male;
age : 24;
political : tuan_yuan;
origin : China;
professional : SoftWare

id : 2;
code : 200820233442;
name : lisi;
sex : femal;
age : 23;
political : dang_yuan;
origin : China;
professional : Computer