使用ibatis实现动态分页查询

时间：2022-04-19 20:08:49

一.实现分页之前可以做的：优化

SQL

本来的查询语句为下面的，可以进行多种优化

  1. SELECT o.*, r.name
  2. FROM PLACED_ORDER o, RESTAURANT r
  3. WHERE o.RESTAURANT_ID = r.RESTAURANT_ID
  4.   AND o.DELIVERY_TIME > (SYSDATE - 30)
  5. ORDER BY o.ORDER_EXT_ID DESC

1．使用优化hints

  1. SELECT /*+ FIRST_ROWS(20) */ o.*, r.name
  2. FROM PLACED_ORDER o, RESTAURANT r
  3. WHERE o.RESTAURANT_ID = r.RESTAURANT_ID
  4. AND o.DELIVERY_TIME > (SYSDATE - 30)
  5. ORDER BY o.ORDER_EXT_ID DESC

    2. Denormalizing the schema

  可以用数据的冗余来避免join表，比如把restaurant’s name放到PLACED_ORDER表中

  1. SELECT o.order_id, o.restaurant_name, …
  2. FROM PLACED_ORDER o
  3. WHERE o.DELIVERY_TIME > (SYSDATE - 30)
  4. ORDER BY o.ORDER_EXT_ID DESC

    3. 使用rownum

  1. SELECT *
  2. FROM
  3.    (SELECT ROWNUM AS RN, XX.*
  4.     FROM
  5.    (SELECT O.ORDER_ID, R.NAME, …
  6.       FROM PLACED_ORDER O, RESTAURANT R
  7.       WHERE O.RESTAURANT_ID = R.RESTAURANT_ID
  8.       ORDER BY O.ORDER_EXT_ID
  9.    ) XX
  10.    WHERE ROWNUM < 21)
  11. WHERE RN > 10

二.使用ibatis实现动态分页查询

 

1. 使用一般的queryForList

 

Java 代码：

 

注意：findOrder() 返回了比实际需要多一行的数据，用来判断是否还有更多的行未取。在返回前，会把这个多余行remove掉的。

  1. public class OrderDAOIBatisImpl extends SqlMapClientDaoSupport implements OrderDAO {
  2.     public PagedQueryResult findOrders(int startingIndex,
  3.             int pageSize, OrderSearchCriteria criteria) {
  4.         Map map = new HashMap();
  5.         map.put("pageSize",
  6.                 new Integer(pageSize + startingIndex + 1));
  7.         map.put("criteria", criteria);
  8.         Implementing dynamic paged queries with iBATIS 421
  9.         List result = getSqlMapClientTemplate().queryForList(
  10.                 "findOrders", map, startingIndex, pageSize);
  11.         boolean more = result.size() > pageSize;
  12.         if (more) {
  13.             result.remove(pageSize);
  14.         }
  15.         return new PagedQueryResult(result, more);
  16.     }
Ibatis的配置：
  1. <sqlMap>
  2.     …
  3.     <select id="findOrders" resultMap="OrderResultMap"
  4.         resultSetType="SCROLL_INSENSITIVE">
  5.         SELECT /*+ FIRST_ROWS($pageSize$) */ O.ORDER_ID, R.NAME AS
  6.         RESTAURANT_NAME FROM FTGO_ORDER O, FTGO_RESTAURANT R WHERE
  7.         O.RESTAURANT_ID = R.RESTAURANT_ID
  8.         <isNotEmpty property="criteria.restaurantName">
  9.             AND r.name = #criteria.restaurantName#
  10.         </isNotEmpty>
  11.         <isNotEmpty property="criteria.deliveryCity">
  12.             AND o.delivery_city = #criteria.deliveryCity#
  13.         </isNotEmpty>
  14.         <isNotEmpty property="criteria.state">
  15.             AND o.status = #criteria.state#
  16.         </isNotEmpty>
  17.         ORDER BY o.ORDER_ID ASC
  18.     </select>
  19.     <resultMap id="OrderResultMap"
  20.         class="net.chrisrichardson.foodToGo.
  21.             bbbbbbbbbbb➥ placeOrderTransactionScripts.details.
  22.             bbbbbbbbbb➥ OrderSummaryDTO">
  23.         <result property="orderId" column="ORDER_ID" />
  24.         <result property="restaurantName" column="RESTAURANT_NAME" />
  25.         …
  26.     </resultMap>
  27.     …
  28. </sqlMap>

2.使用rownum

 

java代码：

  1. public PagedQueryResult findOrders (int startingIndex,
  2.         int pageSize, OrderSearchCriteria criteria) {
  3.     Map map = new HashMap();
  4.     map.put("startingIndex", new Integer(startingIndex));
  5.     map.put("maxRows", new Integer(pageSize + startingIndex
  6.             + 2));
  7.     map.put("criteria", criteria);
  8.     List result = getSqlMapClientTemplate().queryForList(
  9.             Implementing dynamic paged queries with iBATIS 423
  10.             "findOrders", map);
  11.     boolean more = result.size() > pageSize;
  12.     if (more) {
  13.         result.remove(pageSize);
  14.     }
  15.     return new PagedQueryResult(result, more);
  16. }

 

Ibatis配置： 注意：只有start大于0，才会编译某些段

 

  1. <sqlMap>
  2.     <select id="findOrders" resultMap="OrderResultMap"
  3.         resultSetType="SCROLL_INSENSITIVE">
  5.         <isGreaterThan property=" startingIndex" compareValue="0">
  6.             SELECT * FROM (SELECT XX.*, ROWNUM RNXX FROM (
  7.         </isGreaterThan>
  9.         SELECT * FROM ( SELECT O.ORDER_ID, R.NAME AS RESTAURANT_NAME
  10.         FROM FTGO_ORDER O, FTGO_RESTAURANT R WHERE O.RESTAURANT_ID =
  11.         R.RESTAURANT_ID
  13.         <isNotEmpty property="criteria.restaurantName">
  14.             AND r.name = #criteria.restaurantName#
  15.         </isNotEmpty>
  17.         <isNotEmpty property="criteria.deliveryCity">
  18.             AND o.delivery_city = #criteria.deliveryCity#
  19.         </isNotEmpty>
  21.         <isNotEmpty property="criteria.state">
  22.             AND o.status = #criteria.state#
  23.         </isNotEmpty>
  25.         ORDER BY o.ORDER_ID ASC ) WHERE ROWNUM < #maxRows#
  27.         <isGreaterThan property="start" compareValue="0">
  28.             ) XX ) WHERE RNXX > #startingIndex#
  29.         </isGreaterThan>
  30.     </select>
  31.     …
  32. </sqlMap>

三. 为什么选用上面的rownum分页查询格式

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
WHERE ROWNUM <= 40
)
WHERE RN >= 21

<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

 

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

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

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

 

SELECT * FROM
(
SELECT A.*, ROWNUM RN
FROM (SELECT * FROM TABLE_NAME) A
)
WHERE RN BETWEEN 21 AND 40

 

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

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

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

 

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

ibatis自己提供的分页API

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

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

 

 五。补充

  有人自己hack了ibatis的分页api，然后实现物理的分页，一般不提倡。

 

【参考】

1. 《POJO in action》 Implementing dynamic paged queries

2. yangtingkun ,http://yangtingkun.itpub.net/post/468/100278 ,Oracle分页查询语句

3. IBATIS document

 
标签：

相关文章