Mysql 通用分页

 

今天做了一个通用的针对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 + ";\ncode : " + code + ";\nname : " + name + ";\nsex : " +sex+
				";\nage : " +age+ ";\npolitical : " +political+ ";\norigin : "
				+ origin +";\nprofessional : " + 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