分页查询设计

   分页查询就是根据需要每次只返回所需要的数据，而不用每次都从数据库中全部把数据提取出来，这样可以降低程序与数据库之间的数据传送量，并且还可以提高程序的性能。 一般来说我们在数据量大的情况下总是会分页显示（谁也不会一下子将几万条数据全部一次性显示给用户），这样决定我们返回的查询结果集的参数有两个：当前显示的页数pageNo和每页要显示的记录条数pageSize。
一般作为查询数据库返回的结果有两个：正要查询当前页的所有记录集（List）和某条件下数据库中的记录总数（totalRecords）; 
       为了体现面向对象编程，我们会把分页的数据封装在一个单独的类里

Java代码  

1. import java.util.List;  
2.   
3. public class PageModel {  
4.      
5.     private List list;  
6.      
7.     private int totalRecords;  
8.      
9.     private int pageSize;  
10.      
11.     private int pageNo;  
12.      
13.     /** 
14.      * 总页数 
15.      * @return 
16.      */  
17.     public int getTotalPages() {  
18.         return (this.totalRecords + this.pageSize - 1)/this.pageSize;  
19.     }  
20.      
21.     /** 
22.      * 取得首页 
23.      * @return 
24.      */  
25.     public int getTopPageNo() {  
26.         return 1;  
27.     }  
28.      
29.     /** 
30.      * 上一页 
31.      * @return 
32.      */  
33.     public int getPreviousPageNo() {  
34.         if (this.pageNo <= 1){  
35.             return 1;  
36.         }  
37.         return this.pageNo - 1;  
38.     }  
39.      
40.     /** 
41.      * 下一页 
42.      * @return 
43.      */  
44.     public int getNextPageNo() {  
45.         if (this.pageNo >= this.getBottomPageNo()) {  
46.             return this.getBottomPageNo();  
47.         }  
48.         return this.pageNo + 1;  
49.     }  
50.      
51.     /** 
52.      * 尾页 
53.      * @return 
54.      */  
55.     public int getBottomPageNo() {  
56.         return this.getTotalPages();  
57.     }  
58.      
59.     public List getList() {  
60.         return list;  
61.     }  
62.   
63.     public void setList(List list) {  
64.         this.list = list;  
65.     }  
66.   
67.     public int getTotalRecords() {  
68.         return totalRecords;  
69.     }  
70.   
71.     public void setTotalRecords(int totalRecords) {  
72.         this.totalRecords = totalRecords;  
73.     }  
74.   
75.     public int getPageSize() {  
76.         return pageSize;  
77.     }  
78.   
79.     public void setPageSize(int pageSize) {  
80.         this.pageSize = pageSize;  
81.     }  
82.   
83.     public int getPageNo() {  
84.         return pageNo;  
85.     }  
86.   
87.     public void setPageNo(int pageNo) {  
88.         this.pageNo = pageNo;  
89.     }  
90. }  

 

Oracle 的sql

第一种写法
select * from ( select a.*,rownum rn
from (select * from user_login_info t) a where rownum <=40  
) where rn >=21;
第二种写法
select * from (
select a.*,rownum rn from (select * from user_login_info t) a

) where rn between 21 and 40;
对比这两种写法，绝大多数的情况下，第一个查询的效率比第二个高得多。

这是由于CBO优化模式下，Oracle可以将外层的查询条件推到内层查询中，以提高内层查询的执行效率。对于第一个查询语句，第二层的查询条件WHERE ROWNUM <= 40就可以被Oracle推入到内层查询中，这样Oracle查询的结果一旦超过了ROWNUM限制条件，就终止查询将结果返回了。

而第二个查询语句，由于查询条件BETWEEN 21 AND 40是存在于查询的第三层，而Oracle无法将第三层的查询条件推到最内层（即使推到最内层也没有意义，因为最内层查询不知道RN代表什么）。因此，对于第二个查询语句，Oracle最内层返回给中间层的是所有满足条件的数据，而中间层返回给最外层的也是所有数据。数据的过滤在最外层完成，显然这个效率要比第一个查询低得多。


 SQL Server 2000/2005
SELECT TOP 页大小 * FROM table1 WHERE id NOT IN ( SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id ) ORDER BY id
方法2：
适用于 SQL Server 2000/2005
SELECT TOP 页大小 * FROM table1 WHERE id > ( SELECT ISNULL(MAX(id),0) FROM ( SELECT TOP 页大小*(页数-1) id FROM table1 ORDER BY id ) A ) ORDER BY id
方法3：
适用于 SQL Server 2005
SELECT TOP 页大小 * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY id) AS RowNumber,* FROM table1 ) A WHERE RowNumber > 页大小*(页数-1)
说明，页大小：每页的行数；页数：第几页。使用时，请把“页大小”以及“页大小*(页数-1)”替换成数码。

MYSQL
SELECT * FROM TT LIMIT 1,20
SELECT * FROM TT LIMIT 21,30
/*
如果你是几千上万数据，就直接使用mysql自带的函数 limit的普通用法就ok了，如果是100万以上的数据，可能就要讲方法了，下面我们来做个百万级数据的分页查询语句.
mysql> select * from news where id>=(select id from news limit 490000,1) limit 10;    //0.18 sec  //很 明显,这 种方式胜出 .
mysql> select * from news limit 490000,10  //0.22 sec;
*/
以下的文章主要介绍的是MySQL分页的实际操作方案，其实关于实现MySQL分页的最简单的方法就是利用利用mysql数据库的LIMIT函数,LIMIT [offset,size] rows可以从MySQL数据库表中第M条记录开始检索N条记录的语句为：
 
 SELECT * FROM 表名称 LIMIT M,N 
例如从表Sys_option（主键为sys_id)中从第10条记录开始检索20条记录，语句如下：
select * from sys_option limit 10,20  
select * from table [查询条件] order by id limit ?,?