本文是在參閱了http://ivanl.javaeye.com/blog/24739基礎上完成的
在看JPetStore的代碼時，發現它的分頁處理主要是通過返回PaginatedList對象來完成的。如：在CatalogService類中
public?PaginatedList?getProductListByCategory(String?categoryId)?
{?
????return?productDao.getProductListByCategory(categoryId);?
??}?分頁是操作數據庫型系統常遇到的問題。分頁實現方法很多，但效率的差異就很大了。iBatis是通過什么方式來實現這個分頁的了。查看它的實現部分：
?
返回的PaginatedList實際上是個接口，實現這個接口的是PaginatedDataList類的對象，查看PaginatedDataList類發現，每次翻頁的時候最后都會調用下面這段函數
private?List?getList(int?idx,?int?localPageSize)?throws?SQLException?{?
????return?sqlMapExecutor.queryForList(statementName,?parameterObject,?(idx)?*?pageSize,?localPageSize);?
??}?
由于
public?interface?SqlMapClient?extends?SqlMapExecutor,?SqlMapTransactionManager?{……}? 所以實際的調用次序如下：
SqlMapClientImpl.queryForPaginatedList->SqlMapSessionImpl.queryForPaginatedList?
->SqlMapExecutorDelegate.queryForPaginatedList->GeneralStatement.executeQueryForList?
->GeneralStatment.executeQueryWithCallback->GeneralStatment.executeQueryWithCallback?
->SqlExecutor.executeQuery->SqlExecutor.handleMultipleResults()->SqlExecutor.executeQuery->?handleResults?
分頁處理的函數如下
private?void?handleResults(RequestScope?request,?ResultSet?rs,?int?skipResults,?int?maxResults,?RowHandlerCallback?callback)?throws?SQLException?{?
????try?{?
??????request.setResultSet(rs);?
??????ResultMap?resultMap?=?request.getResultMap();?
??????if?(resultMap?!=?null)?{?
????????//?Skip?Results?
????????if?(rs.getType()?!=?ResultSet.TYPE_FORWARD_ONLY)?{?
??????????if?(skipResults?>?0)?{?
????????????rs.absolute(skipResults);?
??????????}?
????????}?else?{?
??????????for?(int?i?=?0;?i?<?skipResults;?i++)?{?
????????????if?(!rs.next())?{?
??????????????return;?
????????????}?
??????????}?
????????}?
??
????????//?Get?Results?
????????int?resultsFetched?=?0;?
????????while?((maxResults?==?SqlExecutor.NO_MAXIMUM_RESULTS?||?resultsFetched?<?maxResults)?&&?rs.next())?{?
??????????Object[]?columnValues?=?resultMap.resolveSubMap(request,?rs).getResults(request,?rs);?
??????????callback.handleResultObject(request,?columnValues,?rs);?
??????????resultsFetched++;?
????????}?
??????}?
????}?finally?{?
??????request.setResultSet(null);?
????}?
??}? 由此可見，iBatis的分頁主要依賴于jdbcdriver的如何實現以及是否支持rs.absolute(skipResults)。它并不是一個好的分頁方式。它先要取出所有的符合條件的記錄存入ResultSet對象，然后用absolute方法進行定位，來實現分頁。當記錄數較大（比如十萬條）時，整體的查詢速度將會變得很慢。
所以分頁還是要考慮采用直接操作sql語句來完成。當然小批量的可以采用iBatis的分頁模式。一般分頁的sql語句與數據庫的具體實現有關
mysql:?
select?*?from?A?limit?startRow,endRow?
oracle:?
select?b.*?from?(select?a.*,rownum?as?linenum?from?(select?*?from?A)?a?where?rownum?<=?endRow)?b?where?linenum?>=?startRow? Hibernate的Oracle分頁采用的就是是拼湊RowNum的Sql語句來完成的。參考代碼如下：?
?
????????public?String?createOraclePagingSql(String?sql,?int?pageIndex,?int?pageSize){?
????????????int?m?=?pageIndex?*?pageSize;?
????????????int?n?=?m?+?pageSize;?
????????????return?"select?*?from?(?select?row_.*,?rownum?rownum_?from?(?"?+?sql?
????????????????????+?"?)?row_?where?rownum?<=?"?+?n??
????????????????????+?")?where?rownum_?>?"?+?m;?
????????}? 綜上，小批量（<2w）可以采用ibatis自帶的分頁類，大批量的還是直接操縱sql，當然也可以將這些sql自己進行封裝，或在包中封裝都可以。包封裝的示例代碼如下：
一個封裝了分頁功能的Oracle Package
create?or?replace?package?body?FMW_FY_HELPER?is
PROCEDURE?GET_DATA(pi_sql?in?varchar,pi_whichpage?in?integer,pi_rownum?in?integer,
po_cur_data?out?cur_DATA,po_allrownum?out?integer,pio_succeed?in?out?integer)
as?
v_cur_data?cur_DATA;
v_cur_temp?cur_TEMP;
v_temp?integer;
v_sql?varchar(5000);
v_temp1?integer;
v_temp2?integer;
begin
pio_succeed?:=?1;
v_sql?:=?'select?count(''a'')?from?(?'?||?pi_sql?||?')';
execute?immediate?v_sql?into?v_temp;
po_allrownum:=ceil(v_temp/pi_rownum);
v_sql?:=?'';
v_temp?:=pi_whichpage*pi_rownum?+?1;
v_temp1:=(pi_whichpage-1)*pi_rownum?+?1;
v_temp2:=pi_whichpage*pi_rownum;
v_sql:=?'select?*?from?(select?rownum?as?rn,t.*?from?('?||?pi_sql?||')?t?where?rownum<'?||?to_char(v_temp)?||?')??where?rn?between?'?||?to_char(v_temp1)?||?'?and?'?||?to_char(v_temp2);
open?v_cur_data?for?v_sql;
if?v_cur_data?%notfound
then
pio_succeed:=-1;
return;
end?if;
po_cur_DATA?:=?v_cur_data;
end;
posted on 2007-01-18 16:27
滌生 閱讀(8459)
評論(6) 編輯 收藏