相信大家都遇到过数据的分页查询,其实就是将过多的结果在有限的界面上分好多页来显示,这个是很多网站常用的功能,也是最基本的功能,今天就做个简单总结。
一般人们将分页查询分为两类:逻辑分页,物理分页,我们先从理论上理解一下:
1、物理分页:
物理分页概述:使用数据库自身所带的分页机制,例如,Oracle数据库的rownum,或者Mysql数据库中的limit等机制来完成分页操作。因为是对数据库实实在在的数据进行分页条件查询,所以叫物理分页。每一次物理分页都会去连接数据库。
优点:数据能够保证最新,由于根据分页条件会查询出少量的数据,所以不会占用太多的内存。
缺点:物理分页使用了数据库自身带的机制,所以这样的SQL语句不通用,导致不能进行数据库的移植。
2、逻辑分页:
逻辑分页利用游标分页,好处是所有数据库都统一,坏处就是效率低。
逻辑分页概述:就是用户第一次访问时,将数据库的所有记录全部查询出来,添加到一个大的集合中,然后存放在session对象,然后通过页码计算出当前页需要显示的数据内容,存储到一个小的list的集合中,并将之存储到request对象中,跳转到JSP页面,进行遍历显示。 当用户第二次访问时,只要不关闭浏览器,我们还会从session中获取数据,来进行显示。为什么叫逻辑分页呢?因为此种方法是在内存的session对象中进行计算分页显示的,而不是真正的将我们数据库进行分页的。
来看它的一些缺点吧:
a,如果需要查询的数据量过大,session将耗费大量的内存;
b,因为是在session中获取数据,如果第二次或者更多此的不关闭浏览器访问,会直接访问session,从而不能保证数据是最新的。
小结:这种分页很少使用。但是在数据量小,不会被修改的数据,使用逻辑分页会提高程序的执行效率。
3、常用orm框架采用的分页技术:
①:hibernate采用的是物理分页;
②:MyBatis使用RowBounds实现的分页是逻辑分页,也就是先把数据记录全部查询出来,然在再根据offset和limit截断记录返回(数据量大的时候会造成内存溢出),不过可以用插件或其他方式能达到物理分页效果。
mybatis的物理分页插件:常见的两种: Mybatis-Paginator Mybatis-PageHelper
为了在数据库层面上实现物理分页,又不改变原来MyBatis的函数逻辑,可以编写plugin截获MyBatis Executor的statementhandler,重写SQL来执行查询
小结:在实际中物理分页还是使用的较多的,但现在有较多开发者使用开源的框架mybatis的分页插件 PageHelper,进行分页查询。
Oracle存储过程分页查询
第一步:这里不再解释package (不了解的可以看我之前写的文章package详细介绍)
create or replace package TESTPACKAGE as
type Test_CURSOR is ref cursor;
end TESTPACKAGE;
第二步:
create or replace procedure OraclePager
(
tableName in varchar2, --表名
fields in varchar2, --查询解果显示字段: 字段1,字段2,..,字段n
wherecase in varchar2, --查询条件
pageSize in number, --一页显示记录数
pageNow in number, --当前页
orderField varchar2, --排序字段,为空表示不排序
orderFlag number, --排序标识 0:正序 1:倒序
myrows out number, --总记录数
myPageCount out number, --总分页
p_cursor out TESTPACKAGE.Test_CURSOR --返回的记录集
) is
--定义部分
--定义sql语句字符串
v_sql varchar2(8000);
--定义两个整数
v_begin number:=(pageNow-1)*pagesize+1; --开始记录
v_end number:=pageNow*pageSize; --结束记录
--排序sql
v_orderSql varchar2(100):='';
v_wherecase varchar2(1000):='';
begin
--执行部分
--如果orderField不为空,则进行排序,如果orderFlag=0为升序,1为降序
if orderField is not null then
if orderFlag=0 then
v_orderSql:=' order by '||orderField; --正序
elsif orderFlag=1 then
v_orderSql:=' order by '||orderField||' desc';--倒序
else
null;
end if;
end if;
--条件判断语句
if wherecase is not null then
v_wherecase:=' where '||wherecase;
end if;
--判断是不是超出索引
--计算myrows和myPageCount
--组织一个sql
v_sql:='select count(*) from '|| tableName || v_wherecase;
--dbms_output.put_line('count='||v_sql);
--执行sql,并把返回的值赋给myrows;
execute immediate v_sql into myrows;
--计算myPageCount分页数
if mod(myrows,Pagesize)=0 then--Oracle mod(number1,number2)返回的值为其余数值
myPageCount:=myrows/Pagesize;
else
myPageCount:=trunc(myrows/pagesize)+1;--Oracle trunc(number)可以对传入值进行四舍五入
end if;
if pageNow>mypageCount then
v_begin:=(mypageCount-1)*pagesize+1; --开始记录
v_end:=mypageCount*pageSize; --结束记录
end if;
v_sql:='select * from
(select t1.* ,rownum rn from(select '|| fields ||' from '|| tableName|| v_wherecase ||' '||v_orderSql ||') t1 where rownum<='|| v_end ||')
where rn>='|| v_begin;
--把游标和sql关联
open p_cursor for v_sql;
--关闭游标
--close p_cursor;
end OraclePager;
第三步:测试代码
public static void main(String[] args) throws SQLException {
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
try {
Class.forName(driver);
conn = DriverManager.getConnection(url, name, password);
CallableStatement proc = null;
proc = conn.prepareCall("{ call OraclePager(?,?,?,?,?,?,?,?,?,?) }"); // 设置存储过程
proc.setString(1, "tab_1");// 表名
proc.setString(2, "id,name,password");// 查询解果显示字段: 字段1,字段2,..,字段n
proc.setString(3, "");// 查询条件
proc.setInt(4, 10);// 一页显示记录数
proc.setInt(5, 1);// 当前页码
System.out.println("当前页码 : 1" );
proc.setString(6, "");// 排序字段,为空表示不排序
proc.setString(7, "");// 排序标识 0:正序 1:倒序
proc.registerOutParameter(8, oracle.jdbc.OracleTypes.NUMBER);// 总记录数
proc.registerOutParameter(9, oracle.jdbc.OracleTypes.NUMBER);// 总分页
proc.registerOutParameter(10, oracle.jdbc.OracleTypes.CURSOR);// 返回的记录集(相当于List集合)
proc.execute();// 执行
String myrows = proc.getString(8);// 输出总记录数
String myPageCount = proc.getString(9);// 总分页
System.out.println("总记录数 : " + myrows);
System.out.println("总分页 : " + myPageCount);
rs = (ResultSet) proc.getObject(10);
while (rs.next()) {
System.out.println(
" ID: " + rs.getString(1) + " 名字: " + rs.getString(2) + " 密码: " + rs.getString(3));
}
} catch (SQLException ex2) {
ex2.printStackTrace();
} catch (Exception ex2) {
ex2.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
}
} catch (Exception e) {
}
}
}
查询第一页运行结果:
当前页码 : 1
总记录数 : 10000
总 分 页 : 1000
ID:7135 名字: 新增用户7135 密码:9187135@126.com
ID:7136 名字: 新增用户7136 密码:9187136@126.com
ID:7137 名字: 新增用户7137 密码:9187137@126.com
ID:7138 名字: 新增用户7138 密码:9187138@126.com
ID:7139 名字: 新增用户7139 密码:9187139@126.com
ID:7140 名字: 新增用户7140 密码:9187140@126.com
ID:7141 名字: 新增用户7141 密码:9187141@126.com
ID:7142 名字: 新增用户7142 密码:9187142@126.com
ID:7143 名字: 新增用户7143 密码:9187143@126.com
ID:7144 名字: 新增用户7144 密码:9187144@126.com
查询第二页运行结果:
当前页码 : 2
总记录数 : 10000
总 分 页 : 1000
ID:7145 名字: 新增用户7145 密码:9187145@126.com
ID:7146 名字: 新增用户7146 密码:9187146@126.com
ID:7147 名字: 新增用户7147 密码:9187147@126.com
ID:7148 名字: 新增用户7148 密码:9187148@126.com
ID:7149 名字: 新增用户7149 密码:9187149@126.com
ID:7150 名字: 新增用户7150 密码:9187150@126.com
ID:7151 名字: 新增用户7151 密码:9187151@126.com
ID:7152 名字: 新增用户7152 密码:9187152@126.com
ID:7153 名字: 新增用户7153 密码:9187153@126.com
ID:7154 名字: 新增用户7154 密码:9187154@126.com