IBatis的分页研究

 

摘自：

http://cpu.iteye.com/blog/311395

yangtingkun   Oracle分页查询语句

ibaits.jar OracleDialect.java

 

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

例如：我要查询 UserInfo 表中的第 11 - 20 条的数据
select * from (select row_.*, rownum rownum_ from (select * from UserInfo order by sort desc) row_ where rownum <= 20) where rownum_ > 10

 

select t2.* from (select t1.*,rownum rownum_ from userinfo t1 where rownum <= 20) t2 where t2.rownum_ > 10;

 

   其中最内层的查询 SELECT * FROM UserInfo 表示不进行翻页的原始查询语句。 ROWNUM <= 20 和 ROWNUM_ > 10控制分页查询的每页的范围。

      上面给出的这个分页查询语句，在大多数情况拥有较高的效率。分页的目的就是控制输出结果集大小，将结果尽快的返回。 在上面的分页查询语句中，这种考虑主要体现在 WHERE ROWNUM <= 20 这句上。

选择第 10 到2 0 条记录存在两种方法，一种是上面例子中展示的在查询的第二层通过 ROWNUM <= 20 来控制最大值，在查询的最外层控制最小值。而另一种方式是去掉查询第二层的 WHERE ROWNUM <= 20 语句，在查询的最外层控制分页的最小值和最大值。这是，查询语句如下：

SELECT * FROM 
(
SELECT A.*, ROWNUM ROWNUM_ 
FROM (SELECT * FROM TABLE_NAME) A 
)
WHERE ROWNUM_ BETWEEN 11 AND 20

       对比这两种写法，绝大多数的情况下，第一个查询的效率比第二个高得多。

 

 

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

 

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

四.关于ibatis自己提供的分页API

PaginatedList paginatedList=sqlMap.queryForPaginatedList(statementName, parameterObject, pageSize);

 

这个是基于内存的分页，就是已经把所有数据load到内存了，才实现的伪分页。不会减少load的负荷。

 

 

      综上所述，小批量（<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 ;