在数据库中有一个很重要的查询,叫分页查询,因为每每可能做查询操作时符合查询条件的数据太多,导致无法全部显示在一个页面上,不方便浏览,所以便想把数据一页一页的分别显示,由此,便产生了分页查询这个操作。做一个对某个具体表的简单的分页查询很简单,只需在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);
}
}