使用ibatis实现动态分页查询

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

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

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

1．使用优化hints

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

    2. Denormalizing the schema

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

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

 

    3. 使用rownum

SELECT *
FROM
   (SELECT ROWNUM AS RN, XX.*
    FROM
   (SELECT O.ORDER_ID, R.NAME, …
      FROM PLACED_ORDER O, RESTAURANT R
      WHERE O.RESTAURANT_ID = R.RESTAURANT_ID
      ORDER BY O.ORDER_EXT_ID
   ) XX
   WHERE ROWNUM < 21)
WHERE RN > 10

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

 

1. 使用一般的queryForList

 

Java 代码：

 

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

public class OrderDAOIBatisImpl extends SqlMapClientDaoSupport implements OrderDAO {
    public PagedQueryResult findOrders(int startingIndex,
            int pageSize, OrderSearchCriteria criteria) {
        Map map = new HashMap();
        map.put("pageSize",
                new Integer(pageSize + startingIndex + 1));
        map.put("criteria", criteria);
        Implementing dynamic paged queries with iBATIS 421
        List result = getSqlMapClientTemplate().queryForList(
                "findOrders", map, startingIndex, pageSize);
        boolean more = result.size() > pageSize;
        if (more) {
            result.remove(pageSize);
        }
        return new PagedQueryResult(result, more);
    }

Ibatis的配置：

<sqlMap>
    …
    <select id="findOrders" resultMap="OrderResultMap"
        resultSetType="SCROLL_INSENSITIVE">
        SELECT /*+ FIRST_ROWS($pageSize$) */ O.ORDER_ID, R.NAME AS
        RESTAURANT_NAME FROM FTGO_ORDER O, FTGO_RESTAURANT R WHERE
        O.RESTAURANT_ID = R.RESTAURANT_ID
        <isNotEmpty property="criteria.restaurantName">
            AND r.name = #criteria.restaurantName#
        </isNotEmpty>
        <isNotEmpty property="criteria.deliveryCity">
            AND o.delivery_city = #criteria.deliveryCity#
        </isNotEmpty>
        <isNotEmpty property="criteria.state">
            AND o.status = #criteria.state#
        </isNotEmpty>
        ORDER BY o.ORDER_ID ASC
    </select>
    <resultMap id="OrderResultMap"
        class="net.chrisrichardson.foodToGo.
            bbbbbbbbbbb➥ placeOrderTransactionScripts.details.
            bbbbbbbbbb➥ OrderSummaryDTO">
        <result property="orderId" column="ORDER_ID" />
        <result property="restaurantName" column="RESTAURANT_NAME" />
        …
    </resultMap>
    …
</sqlMap>

2.使用rownum

 

java代码：

public PagedQueryResult findOrders (int startingIndex,
        int pageSize, OrderSearchCriteria criteria) {
    Map map = new HashMap();
    map.put("startingIndex", new Integer(startingIndex));
    map.put("maxRows", new Integer(pageSize + startingIndex
            + 2));
    map.put("criteria", criteria);
    List result = getSqlMapClientTemplate().queryForList(
            Implementing dynamic paged queries with iBATIS 423
            "findOrders", map);
    boolean more = result.size() > pageSize;
    if (more) {
        result.remove(pageSize);
    }
    return new PagedQueryResult(result, more);
}

 

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

 

<sqlMap>
    <select id="findOrders" resultMap="OrderResultMap"
        resultSetType="SCROLL_INSENSITIVE">

        <isGreaterThan property=" startingIndex" compareValue="0">
            SELECT * FROM (SELECT XX.*, ROWNUM RNXX FROM (
        </isGreaterThan>

        SELECT * FROM ( SELECT O.ORDER_ID, R.NAME AS RESTAURANT_NAME
        FROM FTGO_ORDER O, FTGO_RESTAURANT R WHERE O.RESTAURANT_ID =
        R.RESTAURANT_ID

        <isNotEmpty property="criteria.restaurantName">
            AND r.name = #criteria.restaurantName#
        </isNotEmpty>

        <isNotEmpty property="criteria.deliveryCity">
            AND o.delivery_city = #criteria.deliveryCity#
        </isNotEmpty>

        <isNotEmpty property="criteria.state">
            AND o.status = #criteria.state#
        </isNotEmpty>

        ORDER BY o.ORDER_ID ASC ) WHERE ROWNUM < #maxRows#

        <isGreaterThan property="start" compareValue="0">
            ) XX ) WHERE RNXX > #startingIndex#
        </isGreaterThan>
    </select>
    …
</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

 

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