使用ibatis实现动态分页查询

迷南。 2022-09-18 08:55 278阅读 0赞

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

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.     \}
17.

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">
4.  
5.          <isGreaterThan property=" startingIndex" compareValue="0">
6.              SELECT \* FROM (SELECT XX.\*, ROWNUM RNXX FROM (
7.          </isGreaterThan>
8.  
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
12. 
13.         <isNotEmpty property="criteria.restaurantName">
14.             AND r.name = \#criteria.restaurantName\#
15.         </isNotEmpty>
16. 
17.         <isNotEmpty property="criteria.deliveryCity">
18.             AND o.delivery\_city = \#criteria.deliveryCity\#
19.         </isNotEmpty>
20. 
21.         <isNotEmpty property="criteria.state">
22.             AND o.status = \#criteria.state\#
23.         </isNotEmpty>
24. 
25.         ORDER BY o.ORDER\_ID ASC ) WHERE ROWNUM < \#maxRows\#
26. 
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);   
2.

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

五。补充

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