动态拼接实例,这样不用写存储过程也能实现灵活拼接,注意这里用了isEqual标签
01.<?xml version="1.0" encoding="UTF-8"?>
02.<!DOCTYPE sqlMap PUBLIC "-//ibatis.apache.org//DTD SQL Map 2.0//EN"
03. "http://ibatis.apache.org/dtd/sql-map-2.dtd">
04.<sqlMap namespace="commons">
05. <!-- 定义综合查询前缀,后缀sql -->
06. <sql id="prefixSql">
07. <dynamic>
08. <isEqual property="doCount" compareValue="true">
09. select count(*) from (
10. </isEqual>
11. <isEqual property="doCount" compareValue="false">
12. SELECT * FROM (select row_.*, rownum start_rownum_ from
13. (
14. </isEqual>
15. </dynamic>
16. </sql>
17. <sql id="suffixSql">
18. <dynamic>
19. <isEqual property="doCount" compareValue="true">)</isEqual>
20. <isEqual property="doCount" compareValue="false">
21. <![CDATA[
22. ) row_ WHERE rownum <= #startIndex# + #pageSize# ) WHERE start_rownum_ > #startIndex#
23. ]]>
24. </isEqual>
25. </dynamic>
26. </sql>
27. <sql id="countPrefix">select count(*) from (</sql>
28. <sql id="countSuffix">)</sql>
29. <sql id="pagePrefix">
30. <dynamic>
31. <isNotNull property="pageSize">
32. SELECT * FROM ( select rownum r, union_tb.* from (
33. </isNotNull>
34. </dynamic>
35. </sql>
36. <sql id="pageSuffix">
37. <dynamic>
38. <isNotNull property="pageSize">
39. <![CDATA[
40. ) union_tb
41. ) WHERE r > #startIndex# AND r <= #startIndex# + #pageSize#
42. ]]>
43. </isNotNull>
44. </dynamic>
45. </sql>
46.</sqlMap>