1 背景概述
由于在項目中需要在頁面上顯示數量非常多的資料， 在進行資料庫查詢時首先會把所有的資料都查詢出來，然後在進行顯示，這時候分頁查詢的操作就必不可少了，本文介紹Mysql、Oracle、sql Server 三種資料庫進行分頁查詢的用法。
2 預期讀者
- 數通暢聯内部員工
- 廣大計算機愛好者
3 名詞解釋
- 分頁查詢
就是将将過多的結果在有限的界面上分多頁來顯示，一般将分頁查詢分為兩類：邏輯分頁、實體分頁。
邏輯分頁是在使用者第一次通路時，将資料庫的所有記錄全部查詢出來，添加到一個大集合中，然後存放在session對象，再通過頁碼計算出目前頁需要顯示的資料内容，存儲到一個小的list的集合中，并将其存儲到request對象中，跳轉到JSP頁面，進行周遊顯示。 當使用者第二次通路時，隻要不關閉浏覽器，還會從session中擷取資料，來進行顯示。因為此種方法是在記憶體的session對象中進行計算分頁顯示的，而不是真正的将我們資料庫進行分頁的,是以叫做邏輯分頁。
缺點：如果需要查詢的資料量過大，session将耗費大量的記憶體；因為是在session中擷取資料，如果第二次或者更多此的不關閉浏覽器通路，會直接通路session，進而不能保證資料是最新的。
優點：統一代碼處理方式，較容易跨資料庫做遷移。
實體分頁，使用資料庫自身所帶的分頁機制，例如，Oracle資料庫的rownum，或者Mysql資料庫中的limit等機制來完成分頁操作。因為是對資料庫的資料進行分頁條件查詢，是以叫實體分頁。每一次實體分頁都會去連接配接資料庫。
優點：資料能夠保證最新，由于根據分頁條件會查詢出少量的資料，是以不會占用太多的記憶體。
- CTE（Common Table Expression，公用表表達式）
該表達式源自簡單查詢，可以認為是在單個 SELECT、INSERT、UPDATE、DELETE 或 CREATE VIEW 語句的執行範圍内定義的臨時結果集。CTE 與派生表類似，具體表現在不存儲為對象，并且隻在查詢期間有效。與派生表的不同之處在于，CTE 可自引用，還可在同一查詢中引用多次。
4 實作思路
通過實體分頁的方法進行資料庫查詢。
5 實作步驟
首先通過開發平台建立一個工程，使用新工程中的系統日志子產品作為樣例，進行mysql和oracle的分頁查詢功能
5.1 Mysql
- 在資料庫中進行操作：
mysql的分頁查詢是最簡單的，借助關鍵字limit即可實作查詢，查詢語句通用形式：
| select o.* from (sql) o limit firstIndex,pageSize |
其中的sql可以是單表查詢的結果也可以是多表查詢的結果
firstIndex為顯示結果的起始位置（mysql是從0作為起始位置的）
pageSize為顯示記錄數
直接對表進行查詢如下，我們可以看到查詢時間是0.005s
采用分頁查詢，一頁顯示15條資料，查詢時間是0.001s
- 在工程中展現：
首先在工程中找到SystemLogQueryImpl.java這個類
其中有一點需要注意，方法的傳回值需要是PageList而不是List
在開發平台中這個類是日志功能的服務實作類，
其中，ec_p為頁數，ec_rd為顯示記錄數
staratNum為起始索引，endNum為結束索引
由于MySQL的起始索引是從0開始的，需要對得到的起始索引減一，顯示記錄數的計算方法為： 結束索引-起始索引+1
在sqlMap中的展現如圖：
在頁面上的顯示：
可以在頁面選擇顯示的頁碼和記錄數
5.2 Oracle
Oracle的查詢方法有兩種：ROWNUM、row_number()
5.2.1 ROWNUM
- 在資料庫中進行操作：
查詢語句通用形式：
| select * from(select o.*,ROWNUM num from(sql) o where ROWNUM<=(endIndex)) where num>=firstIndex |
直接對表進行查詢，耗時16msecs
采用分頁算法進行查詢是，耗時7msecs
- 在工程中展現：
找到對應的服務實作類：
服務實作類的内容：
startNum為起始索引，endNum為結束索引
因為ROWNUM方法中使用的兩個參數一個是起始索引，一個是結束索引，是以可以直接使用。
在sql中的展現：
在頁面上的效果：
5.2.2 row_number()
查詢語句通用形式：
| select * from(select * from(select t.*,row_number() over(order by orderColumn) as rownumber from(sql) t) p where p.rownumber>firstIndex) where rownum<=pageSize |
直接對表進行查詢，耗時22msecs
使用分頁查詢語句進行查詢，耗時12msece
- 在工程中展現：
服務實作類的内容和ROWNUM一樣，差別在sql中
由于在sql中添加了order by排序函數，查詢速率會變慢，是以在開發平台中不采用這種方法。
對于oracle的分頁查詢，他們的主要差別是:使用rownum進行排序的時候是先對結果集加入僞列rownum然後再進行排序，而函數row_number()在包含排序從句後是先排序再計算行号碼。
5.3 Sqlserver
由于sqlserver版本比較多，分頁查詢的方式也有不同之處
5.3.1 Sqlserver 2005/2008中使用row_number()
查詢通用形式：
| SELECT * FROM (SELECT *,ROW_NUMBER() OVER (ORDER BY orderColumn) AS RowNumber FROM tableName) EmployeePage WHERE RowNumber > =startIndex AND RowNumber <= endIndex ORDER BY orderColumn GO |
直接查詢表，如圖
使用分頁查詢，如圖
5.3.2 SQL 2005/2008用CTE的方式實作
查詢通用形式：
| WITH EmployeePage AS (SELECT *,ROW_NUMBER() OVER (ORDER BY orderColumn) AS RowNumber FROM tableName) SELECT *FROM EmployeePage WHERE RowNumber > =firstIndex AND RowNumber <= endIndex ORDER BY orderColumn GO |
使用分頁查詢，如圖：
5.3.3 SQL SERVER 2012支援了OFFSET
查詢通用形式，如下：
| SELECT * FROM tableName ORDER BY orderColumn OFFSET （page-1） ROWS FETCH NEXT size ROWS ONLY |
Page指需要顯示的頁數
Size指需要顯示的記錄數
由于本人現在沒有sqlserver 2012的資料庫，暫時無法做性能的測試
6 附件及說明
附件中有兩個樣例工程pagingquery4mysql和pagingquery4oracle，使用最新版的aeai dp開發平台導入後檢視使用。
主流資料庫分頁查詢介紹文檔及附件 下載下傳