jdbc分页

分页是一个被讲到烂掉的话题，今天我再拾起来踹几脚吧 
（Hibernate的分页做得很好很强大，用的人都知道基于JDBC的通用分页实现 - 北一止水 -  ，这个就不用再说了） 
1.为什么要分页？ 
   首先是数据量太大会影响查询和传输的性能，关键是对用户来说一下看到数万条记录也不是那么友好。 
2.有哪些分页技术？ 
a)存储过程分页 
   在数据库中创建一个存储过程，传入SQL和页码获得当前页的记录。需要对具体数据库的语法相当熟悉才能够编写，当然也可以直接google。性能最好，但不跨数据库平台。 
b)数据库专有sql特性分页 
   使用数据库专有的特性（MSSQL的top、Oracle的rownum、MySQL的limit等）实现当前页记录提取。性能也非常好，但也不跨数据库平台。 
（为什么非要跨数据库平台呢？好吧，如果你的项目确定是不会换数据库的那就这么写吧。） 
c)纯JDBC分页 
   通过Statement的setMaxRow(endIndex)和rs.absoulte(beginIndex)仅取得当前页范围内的记录。此种方式的性能依赖于厂商对JDBC规范的实现，如果厂商的rs读取是以流的形式进行的，性能还是有所保障的（好吧我承认是这不负责的说法）。这种方式的通用性是最好的，完全与数据库平台无关了（牺牲性能换取跨平台特性是Java平台的常用手法，嘿嘿嘿）。 
d)根据数据库类型自动生成数据库专有特性的sql语句   
   其实这就是Hibernate的实现方法，如果你觉得自己分析SQL语法然后将SQL转换为特定数据库语法比较麻烦，那就用Hibernate吧。如果你不想用Hibernate还是想用这种方法，那就麻烦你多辛苦一点，用力写出来给大家分享吧。 

我们来理一理分页的逻辑吧 
首先，对用户而言他是不关心你是怎么分页的，他只要给你一个页码，然后你把那一页的记录给他就行了。 
为了能够取得指定页码所对应的记录，我们还需要两个关键的参数：每页记录数、总记录数。 
   每页记录数可以设个默认值，20、30都行吧。 
   总记录数就需要额外从数据库中取得了，这个也是没办法的事，谁让ResultSet没提供获取记录总数的方法呢。 
通过这两个参数能够计算出来总页数，同时也就可以判断用户给出的页码是否有效了（超出最后一页或者小于第一页），然后根据页码和每页记录数就可以算出来当前页的记录起止范围了。（这些个算术都是小学的，就不用说了吧，其实我也是想了半天的） 
为了表示方便，就把这些参数写成一个类，同时把计算方法也写进去 

Java代码  收藏代码
  1. /** 
  2.  *  
  3.  * @author Lixor(at)live.cn 
  4.  * 
  5.  */  
  6. public class Page {  
  7.     private int rowTotal;// 总记录数  
  8.     private int pageSize = 10;// 每页记录数  
  9.   
  10.     private int count;// 当前页码  
  11.       
  12.     private int total;// 总页数  
  13.     private int beginIndex;//起始记录下标  
  14.     private int endIndex;//截止记录下标  
  15.   
  16.     /** 
  17.      * 使用总记录数、当前页码构造 
  18.      *  
  19.      * @param rowTotal 
  20.      * @param count 
  21.      *            页码，从1开始 
  22.      */  
  23.     public Page(int totalRow, int count) {  
  24.         this.rowTotal = totalRow;  
  25.         this.count = count;  
  26.         calculate();  
  27.     }  
  28.   
  29.     /** 
  30.      * 使用总记录数、当前页码和每页记录数构造 
  31.      *  
  32.      * @param rowTotal 
  33.      * @param count 
  34.      *            页码，从1开始 
  35.      * @param pageSize 
  36.      *            默认30条 
  37.      */  
  38.     public Page(int totalRow, int count, int pageSize) {  
  39.         this.rowTotal = totalRow;  
  40.         this.count = count;  
  41.         this.pageSize = pageSize;  
  42.         calculate();  
  43.     }  
  44.   
  45.     private void calculate() {  
  46.         total = rowTotal / pageSize + ((rowTotal % pageSize) > 0 ? 1 : 0);  
  47.   
  48.         if (count > total) {  
  49.             count = total;  
  50.         } else if (count < 1) {  
  51.             count = 1;  
  52.         }  
  53.   
  54.         beginIndex = (count - 1) * pageSize ;  
  55.         endIndex = beginIndex + pageSize ;  
  56.         if (endIndex > rowTotal) {  
  57.             endIndex = rowTotal;  
  58.         }  
  59.     }  
  60.   
  61.     public int getCount() {  
  62.         return count;  
  63.     }  
  64.   
  65.     public int getTotal() {  
  66.         return total;  
  67.     }  
  68.   
  69.     public int getTotalRow() {  
  70.         return rowTotal;  
  71.     }  
  72.   
  73.     public int getPageSize() {  
  74.         return pageSize;  
  75.     }  
  76.   
  77.     public int getBeginIndex() {  
  78.         return beginIndex;  
  79.     }  
  80.   
  81.     public int getEndIndex() {  
  82.         return endIndex;  
  83.     }  
  84.   
  85. }  



好了，可别散扯了，直接来个纯JDBC分页的示例吧 

Java代码  收藏代码
  1. //从页面取得页码  
  2. int pageCount = 1;  
  3. try {  
  4.     pageCount = Integer.parseInt(request.getParameter("page.count"));  
  5. } catch (Exception ex) {}  
  6.   
  7. //取得总记录数，创建Page对象  
  8. int totalRow = productDao.getProductAmount();//通过select count 取得总记录数  
  9. Page page = new Page(totalRow, pageCount);  
  10. productDao.list(page);  



ProductDao.java 

Java代码  收藏代码
  1. public List<Product> list(Page page) {  
  2.     List<Product> productList = new ArrayList<Product>();  
  3.   
  4.     try {  
  5.         String sql = "SELECT p.id,p.name,p.price,p.productDate,p.image,p.type_id,t.name,p.description FROM tbl_product p,tbl_type t WHERE p.type_id=t.id";  
  6.         Connection conn = null;  
  7.         try {  
  8.             conn = DbUtil.getConnection();  
  9.             Statement st = conn.createStatement();  
  10.             st.setMaxRows(page.getEndIndex());//关键代码，设置最大记录数为当前页记录的截止下标  
  11.             ResultSet rs = st.executeQuery(sql);  
  12.             if (page.getBeginIndex() > 0) {  
  13.                 rs.absolute(page.getBeginIndex());//关键代码，直接移动游标为当前页起始记录处  
  14.             }  
  15.             while (rs.next()) {  
  16.                 Product product = new Product();  
  17.                 ……  
  18.                 productList.add(product);  
  19.             }  
  20.             rs.close();  
  21.             st.close();  
  22.         } finally {  
  23.             if (conn != null) {  
  24.                 conn.close();  
  25.             }  
  26.         }  
  27.     } catch (SQLException e) {  
  28.         // TODO Auto-generated catch block  
  29.         e.printStackTrace();  
  30.     }  
  31.     return productList;  
  32. }  



最后我试了一下在MySQL上产品表里有10W数据时，执行的日志大概是 

Java代码  收藏代码
  1. 第1页 [http-8080-2] DEBUG org.logicalcobwebs.proxool.db_pms  - SELECT p.id,p.name,p.price,p.productDate,p.image,p.type_id,t.name,p.description FROM tbl_product p,tbl_type t WHERE p.type_id=t.id;  (0 milliseconds)  
  2.   
  3. 第3页 [http-8080-2] DEBUG org.logicalcobwebs.proxool.db_pms  - SELECT p.id,p.name,p.price,p.productDate,p.image,p.type_id,t.name,p.description FROM tbl_product p,tbl_type t WHERE p.type_id=t.id;  (0 milliseconds)  
  4.   
  5. 第5页 [http-8080-2] DEBUG org.logicalcobwebs.proxool.db_pms  - SELECT p.id,p.name,p.price,p.productDate,p.image,p.type_id,t.name,p.description FROM tbl_product p,tbl_type t WHERE p.type_id=t.id;  (0 milliseconds)  
  6.   
  7. 第10页 [http-8080-1] DEBUG org.logicalcobwebs.proxool.db_pms  - SELECT p.id,p.name,p.price,p.productDate,p.image,p.type_id,t.name,p.description FROM tbl_product p,tbl_type t WHERE p.type_id=t.id;  (3 milliseconds)  
  8.   
  9. 第30页 [http-8080-1] DEBUG org.logicalcobwebs.proxool.db_pms  - SELECT p.id,p.name,p.price,p.productDate,p.image,p.type_id,t.name,p.description FROM tbl_product p,tbl_type t WHERE p.type_id=t.id;  (6 milliseconds)  
  10.   
  11. 第100页 [http-8080-2] DEBUG org.logicalcobwebs.proxool.db_pms  - SELECT p.id,p.name,p.price,p.productDate,p.image,p.type_id,t.name,p.description FROM tbl_product p,tbl_type t WHERE p.type_id=t.id;  (15 milliseconds)  
  12.   
  13. 第1000页 [http-8080-2] DEBUG org.logicalcobwebs.proxool.db_pms  - SELECT p.id,p.name,p.price,p.productDate,p.image,p.type_id,t.name,p.description FROM tbl_product p,tbl_type t WHERE p.type_id=t.id;  (152 milliseconds)  
  14.   
  15. 第10000页 [http-8080-2] DEBUG org.logicalcobwebs.proxool.db_pms  - SELECT p.id,p.name,p.price,p.productDate,p.image,p.type_id,t.name,p.description FROM tbl_product p,tbl_type t WHERE p.type_id=t.id;  (2030 milliseconds)  



很显然纯JDBC分页的性能与页码有关（需要查询的数据范围，也就是说st.setMaxRow是有效的），越往后翻，性能越差。 
不过话说回来没有哪个用户去无聊的自己一页一页翻到第10000页去，一般都是翻两页找不到那就会进一步缩小查询条件的范围了。 
所以折衷的办法就是一方面页面记录数搞大一点，另外就是限制用户翻页的范围，超过10页就直接提示用户缩小查询范围，如果这样的话纯JDBC分页也就可以凑和着用了。

本文转自

posted @ 2016-09-30 17:05  跨境电商杂货铺  阅读(622)  评论(0)  编辑  收藏  举报