1:MySQL 是基于limit的:
select * from t_user where limit ?, ? ; 第一个参数表示起始位置, 第二个参数表示页面大小
2:Orcal 是基于ROWNUM的:
在Oracle中,用SQL来实现分页有很多种实现方式,但有些语句可能并不是很通用,只能用在一些特殊场景之中; 以下介绍三种比较通用的实现方案;在以下各种实现中,ROWNUM是一个最核心的关键词,在查询时他是一个虚拟的列,取值为1到记录总数的序号; 首先来介绍我们工作中最常使用的一种实现方式: SELECT * FROM (SELECT ROW_.*, ROWNUM ROWNUM_ FROM (SELECT * FROM TABLE1 WHERE TABLE1_ID = XX ORDER BY GMT_CREATE DESC) ROW_ WHERE ROWNUM <= 20) WHERE ROWNUM_ >= 10; 其中最内层的查询SELECT为不进行翻页的原始查询语句,可以用自己的任意Select SQL替换;ROWNUM <= 20和ROWNUM >= 10控制分页查询的每页的范围。 分页的目的就是控制输出结果集大小,将结果尽快的返回;上面的SQL语句在大多数情况拥有较高的效率,主要体现在WHERE ROWNUM <= 20这句上,这样就控制了查询过程中的最大记录数。 上面例子中展示的在查询的第二层通过ROWNUM <= 20来控制最大值,在查询的最外层控制最小值。而另一种方式是去掉查询第二层的WHERE ROWNUM <= 20语句,在查询的最外层控制分页的最小值和最大值。此时SQL语句如下,也就是要介绍的第二种实现方式: SELECT * FROM (SELECT A.*, ROWNUM RN FROM (SELECT * FROM TABLE1 WHERE TABLE1_ID = XX ORDER BY GMT_CREATE DESC) A) WHERE RN BETWEEN 10 AND 20; 由于Oracle可以将外层的查询条件推到内层查询中,以提高内层查询的执行效率,但不能跨越多层。 对于第一个查询语句,第二层的查询条件WHERE ROWNUM <= 20就可以被Oracle推入到内层查询中,这样Oracle查询的结果一旦超过了ROWNUM限制条件,就终止查询将结果返回了。 而第二个查询语句,由于查询条件BETWEEN 10 AND 20是存在于查询的第三层,而Oracle无法将第三层的查询条件推到最内层(即使推到最内层也没有意义,因为最内层查询不知道RN代表什么)。因此,对于第二个查询语句,Oracle最内层返回给中间层的是所有满足条件的数据,而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成,显然这个效率要比第一个查询低得多。 以上两种方案完全是通过ROWNUM来完成,下面一种则采用ROWID和ROWNUM相结合的方式,SQL语句如下: SELECT * FROM (SELECT RID FROM (SELECT R.RID, ROWNUM LINENUM FROM (SELECT ROWID RID FROM TABLE1 WHERE TABLE1_ID = XX ORDER BY GMT_CREATE DESC) R WHERE ROWNUM <= 20) WHERE LINENUM >= 10) T1, TABLE1 T2 WHERE T1.RID = T2.ROWID; 从语句上看,共有4层Select嵌套查询,最内层为可替换的不分页原始SQL语句,但是他查询的字段只有ROWID,而没有任何待查询的实际表字段,具体查询实际字段值是在最外层实现的; 这种方式的原理大致为:首先通过ROWNUM查询到分页之后的10条实际返回记录的ROWID,最后通过ROWID将最终返回字段值查询出来并返回; 和前面两种实现方式相比,该SQL的实现方式更加繁琐,通用性也不是非常好,因为要将原始的查询语句分成两部分(查询字段在最外层,表及其查询条件在最内层); 但这种实现在特定场景下还是有优势的:比如我们经常要翻页到很后面,比如10000条记录中我们经常需要查9000-9100及其以后的数据;此时该方案效率可能要比前面的高; 因为前面的方案中是通过ROWNUM <= 9100来控制的,这样就需要查询出9100条数据,然后取最后9000-9100之间的数据,而这个方案直接通过ROWID取需要的那100条数据; 从不断向后翻页这个角度来看,第一种实现方案的成本会越来越高,基本上是线性增长,而第三种方案的成本则不会像前者那样快速,他的增长只体现在通过查询条件读取ROWID的部分; 当然,除了以上提了这些方案,我们还可以用以下的SQL来实现: SELECT * FROM TABLE1 WHERE TABLE1_ID NOT IN (SELECT TABLE1_ID FROM TABLE1 WHERE ROWNUM <= 10) AND ROWNUM <= 10; SELECT * FROM TABLE1 WHERE ROWNUM <= 20 MINUS SELECT * FROM TABLE1 WHERE ROWNUM <= 10; ……………… 注意:当ROWNUM作为查询条件时,他是在order by之前执行,所以要特别小心; 比如我们想查询TABLE1中按TABLE1_ID倒序排列的前10条记录不能用如下的SQL来完成: SELECT * FROM TABLE1 WHERE ROWNUM <= 10 ORDER BY TABLE1_ID DESC;
3:基于JDBC的分页:
conn.prepareStatement(sql,游标类型,能否更新记录); // 游标类型: // ResultSet.TYPE_FORWORD_ONLY:只进游标 // ResultSet.TYPE_SCROLL_INSENSITIVE:可滚动。但是不受其他用户对数据库更改的影响。 // ResultSet.TYPE_SCROLL_SENSITIVE:可滚动。当其他用户更改数据库时这个记录也会改变。 // 能否更新记录: // ResultSet.CONCUR_READ_ONLY,只读 // ResultSet.CONCUR_UPDATABLE,可更新 PreparedStatement pstat = conn.prepareStatement(sql,ResultSet.TYPE_SCROLL_INSENSITIVE,ResultSet.CONCUR_READ_ONLY); preparedStatement.setMaxRows(((pageIndex - 1) * pageSize) + pageSize);//查询的最大行数 rs = preparedStatement.executeQuery(); rs.absolute((pageIndex - 1) * pageSize + 1);//利用绝对定位定位到结果集的每页第二条数据 rs.relative(-1);//利用结果集的相对定位定位到每页的第一条数据 然后再便利rs即可。 也可以先查出来,然后利用rs.absolute(int row) 将指针移动到此 ResultSet 对象的给定行编号 利用循环来控制
4:Hibernate HQL的分页:
Query q = s.createQuery(hql.toString()); for (int i = 0; i < params.size(); ++i) { q.setParameter(i, params.get(i)); } q.setFirstResult((currentPage - 1)*pageSize); //设置起始位置 q.setMaxResults(pageSize);//这只一面大小 我们不用管底层是什么实现,Hibernate会帮我们调用相应的实现。