mysql中用存储过程做分页操作

在数据库中有一个很重要的查询，叫分页查询，因为每每可能做查询操作时符合查询条件的数据太多，导致无法全部显示在一个页面上，不方便浏览，所以便想把数据一页一页的分别显示，由此，便产生了分页查询这个操作。做一个对某个具体表的简单的分页查询很简单，只需在select语句的最后加上limit  currentPage,PageSize即可，那么，如何做对任意一个表的分页查询呢，今天，我便来和大家讲一讲如何做一个通用的分页查询工具，即可用任意的查询条件，任意的排序列和排序类型来对任意表的任意的列进行查询。

本文章分为两部分：

1、存储过程部分：在mysql数据库中用存储过程做分页操作

2、jdbc中执行存储过程：在java中用jdbc连接数据库执行带输出参数的存储过程

一、存储过程部分

要用任意的查询条件，任意的排序列和排序类型来对任意表的任意的列进行查询，则输入参数必须要有表名称，要查询的列名称（或者列名称的列表），查询条件，排序列的名称，排序的排序类型（降序desc或升序asc），另外，还需传入需要显示的当前页的页数和每页的大小（即每页需要显示的数据条数），也可通过该查询的存储过程传出查询得到的数据总条数和总页数。

CREATE procedure pro_page2(
vtable_name varchar(20), 	#任意表   必填
vclumn_name varchar(30),	#任意的查询列 可选	
vtiaojian varchar(50),		#任意查询条件 可选
sortcolumn varchar(20),		#任意的排序列 可选
sorttype varchar(4),			#任意的排序类型（asc,DESC） 可选
currentPage int,					#当前页码 必填
recordNum int,						#页面大小 必填
out countPage int,				#符合条件数据的总页数
out countNum int)					#符合条件的数据的总条数

写好了存储过程头部的参数准备部分，就要正式做查询了，不过，在查询之前，先要对传入的参数进行预处理，在输入参数中表名称，当前页的页数和每页的大小为必须传入的查询参数，而要查询的列名称，查询条件，排序列的名称，排序的排序类型则为可选的输入参数，即可传入具体的值，也可为null值。

查询列为空时，则默认为对所有的列进行查询，即其值可为" * " ，查询条件为空时，则默认为对所有的数据进行查询，即其值可为恒成立的" 1=1 "，排序列为空时，则可默认为按主键即按每个表的id列进行排序，排序类型为空时，可默认为按降序排序。

BEGIN
#设置查询的起始位置
declare startNum int;

#判断是否填写查询列,查询条件,排序列,排序类型
if vclumn_name is null or vclumn_name = '' THEN
	set vclumn_name='*';
end if;
#判断是否填写查询条件
if vtiaojian is null or vtiaojian='' then 
	set vtiaojian = '1=1';
end if;
#判断是否填写排序列,若未填写则使用默认的排序列“_id”,需要事先约定好
if sortcolumn is null or sortcolumn = '' THEN
	set sortcolumn = '_id';
end if;
#判断是否填写排序类型,若未给定或者不符合要求，则默认使用升序（ASC）
if lower(sorttype) <> 'asc' and lower(sorttype) <> 'desc' then
	set sorttype = 'asc';
end if;

要把这些参数都用到select 语句中，不能直接将查询语句写成select  clumn_name  from tablb_name  where  limit     ,这样的话，字符串" tablb_name "会被当做是一个表的表名，字符串"clumn_name"会被当做是tablb_name这个表中的某一个列的列名，其余的参数也是如此，不是将参数传入select语句中，而是取其字面的意思。所以，要将参数传入select语句中，需用concat()函数对select语句进行连接，然后对sql语句进行预编译，再执行其预处理对象。

set startNum=(currentPage-1)*recordNum;

set @sql=concat("select ",vclumn_name," from ",vtable_name,
" where ",vtiaojian," order by ",sortcolumn," ",sorttype,
" limit ",startNum,",",recordNum);

#对sql语句进行预编译
prepare stmt from @sql;
execute stmt;
deallocate prepare stmt;

对于输出参数的获取要用一个临时变量将其值存储起来，再将该临时变量的值赋给输出参数。

set @sql2=concat("select count(*) into @temptotalnum from ",vtable_name," where ",vtiaojian);
prepare stmt2 from @sql2;
execute stmt2;
set countNum = @temptotalnum;#将临时变量中的数据赋值给输出参数
deallocate prepare stmt2;
SET countPage=CEILING(countNum/recordNum);
end;

二、在jdbc中执行存储过程

鉴于要执行的存储过程中的输入输出参数较多，所以，为数据获取和存储的方便起见，新建了一个工具类来存储各参数中的数据，命名为PageUtils

public class PageUtils {

	private int currentPage;
	private int pagesize;
	private String tableName;
	private String selections;
	private String condition;
	private String sortColumn;
	private String sortType;
	
	private int totalNum;
	private int totalPage;
	//存储当前页的数据
	private List<Object[]> datas;
	
	public int getCurrentPage() {
		return currentPage;
	}
	public void setCurrentPage(int currentPage) {
		this.currentPage = currentPage;
	}
	public int getPagesize() {
		return pagesize;
	}
	public void setPagesize(int pagesize) {
		this.pagesize = pagesize;
	}
	public String getTableName() {
		return tableName;
	}
	public void setTableName(String tableName) {
		this.tableName = tableName;
	}
	public String getSelections() {
		return selections;
	}
	public void setSelections(String selections) {
		this.selections = selections;
	}
	public String getCondition() {
		return condition;
	}
	public void setCondition(String condition) {
		this.condition = condition;
	}
	public String getSortColumn() {
		return sortColumn;
	}
	public void setSortColumn(String sortColumn) {
		this.sortColumn = sortColumn;
	}
	public String getSortType() {
		return sortType;
	}
	public void setSortType(String sortType) {
		this.sortType = sortType;
	}
	public int getTotalNum() {
		return totalNum;
	}
	public void setTotalNum(int totalNum) {
		this.totalNum = totalNum;
	}
	public int getTotalPage() {
		return totalPage;
	}
	public void setTotalPage(int totalPage) {
		this.totalPage = totalPage;
	}
	public List<Object[]> getDatas() {
		return datas;
	}
	public void setDatas(List<Object[]> datas) {
		this.datas = datas;
	}	
}

另建一个执行类来用jdbc连接数据库执行存储过程，并将所得的查询结果和输出参数全部存储到PageUtils对象中，然后通过操作对象PageUtils中的list集合即可查看所得数据（这里主要要注意在执行存储过程中对输出参数的处理）。

public class ProcedureDemo{


	public PageUtils procPaging(PageUtils pu) throws SQLException{
		
		//获取数据库连接对象
		Class.forName("com.mysql.jdbc.Driver");
 		//获取预处理命令
		Connection conn=DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306//mydb?username=root&password=123456");

		CallableStatement cs=conn.prepareCall("{call pro_page2 (?,?,?,?,?,?,?,?,?)}");
		cs.setInt(1, pu.getCurrentPage());
		cs.setInt(2, pu.getPagesize());
		cs.setString(3, pu.getTableName());
		cs.setString(4,pu.getSelections());
		cs.setString(5,pu.getCondition());
		cs.setString(6, pu.getSortColumn());
		cs.setString(7,pu.getSortType());
		
		//注册输出参数
		cs.registerOutParameter(8, java.sql.Types.INTEGER);
		cs.registerOutParameter(9, java.sql.Types.INTEGER);
		//执行存储过程
		cs.execute();
		//获取指定位置的输出参数集
		int totalNum=cs.getInt(8);
		int totalPage=cs.getInt(9);
		pu.setTotalNum(totalNum);
		pu.setTotalPage(totalPage);
		
		//存储查询所得的结果集
		List<Object[]> list=new ArrayList<>();
		//获取查询的结果集
		ResultSet rs =cs.getResultSet();
		ResultSetMetaData rsmd = rs.getMetaData();
		// 获取数据的总列数
		int count = rsmd.getColumnCount();
		while (rs.next()) {
			Object[] objs = new Object[count];
			for (int i = 1; i <= count; i++) {
				String label = rsmd.getColumnLabel(i);
			
				// 获取一列数据
				Object obj = rs.getObject(label);
				objs[i - 1] = obj;
			}
			list.add(objs);
		}
		pu.setDatas(list);
		return pu;
	}	
	
	public static void main(String[] args) {
		PageUtils pu=new PageUtils();
		pu.setCurrentPage=1;
		pu.setPageSize=10;
		pu.setTableName="employee";		
		procPaging(pu);
	}
}