应用ibatis实现动态分页查询

使用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

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. ????}

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>

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