使用ibatis实现动态分页查询

一.实现分页之前可以做的：优化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

 

其中最内层的查询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