IBatis的分頁研究-----sql分頁

在看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;