1.0          其他語言
 
 

6 SQL 查詢結果分頁

 
 

當一個 SQL 查詢,得到的結果資料太多的時候,這時候就需要將結果資料進行分頁,每次只讓使用者看到一小部份資料。如果一次顯示的資料太多,人眼是無法看過來的。

但是,ANSI SQL 標準中,並沒有考慮好這一點。在幾個流行的資料中,Mysql/PostgreSQL 對這個問題進行自定義延伸,解決了這個問題。這也許是 Mysql 在 Web 應用中流行的原因之一。因為 Web 應用需要關心單個頁面資料量大小。

SQL standard 2003 新帶來的 SQL 窗口函數(Window function) ROW_NUMBER() OVER () , 資料庫 SQL server 2005/DB2/Oracle 都支援,Oracle 與標準略為有點差別。 Mysql/PostgreSQL 則不支援 ROW_NUMBER()。

這已經夠了。VelocityWeb 在此基礎上,提供了一個統一的介面,可以寫出適合各種資料庫的分頁程式,也支援 select distinct/ group by 之類的特殊操作。網路上通常提供的分頁程式,都不支援這些特殊 SQL。

分頁操作,一般涉及兩個部份︰取現行頁資料,取總資料量。另外,需要注意的是,取現行頁資料,涉及資料排序,也就是 SQL 中的 order by。必須有排序,分頁才有意義。

這里面涉及幾個類,都製作了 PageDialect 介面︰ StandardPageDialect(支援 SQL Server 2005,DB2), SQLServer2000PageDialect, OraclePageDialect, MySQLPageDialect, PostgreSQLPageDialect。

下面是一個例子︰

sql = new StringBuffer();
sql.append("SELECT i.itemid, qty");
sql.append(" FROM inventory v,item i");
sql.append(" where i.itemid = v.itemid");
sql.append(" and i.itemid > ?");
sql.append(" and v.itemid > ?");

orderBy = "i.itemid,v.qty desc";

paraList = new LinkedList();
paraList.add("aa");
paraList.add("bb");

countSql = dialect.getCountSql(sql.toString());
count = (Number)this.queryScalar(countSql, paraList.toArray(), 1);

pageSql = dialect.getPageSqlWithoutPageInfo(sql.toString(), orderBy);
values = dialect.getPageParemeterValuesWithPageInfo(pageIndex, pageSize);
paraList.add(new Long(values[0]));
paraList.add(new Long(values[1]));

list = this.queryBeanList(pageSql, paraList.toArray(), Inventory.class);

從目前的使用效果來看,還是不錯的。

VelocityWeb 在此基礎上,提供了更進一步的封裝。如果用 JDBC 作為 DAO 則可以這樣寫︰

List resultDataList = new LinkedList();
int recordCount = JdbcPageUtils.getPageData(sql.toString(), paraList, orderBy,
pageIndex, pageSize, con, dialect, resultDataList);
現行頁資料 resultDataList 中每個元素是 Object[].

如果用 VelocityWeb 的 JdbcDao,則可以這樣寫︰

List resultDataList = new LinkedList();
int recordCount = VelocitywebPageUtils.getPageData(sql.toString(), paraList, orderBy,
pageIndex, pageSize, dao, dialect, Inventory.class, resultDataList);
現行頁資料 resultDataList 中每個元素是 Inventory 物件.