首先我们定义公共查询语句<sql></sql> id为唯一标识符:
<sql id="sql_row_start">
<!-- isPropertyAvailable表示有从dao传来的这个字段值rowStart,既有这个值,property的值为传入字段的值-->
<isPropertyAvailable property="rowStart">
<!--isNotEmpty表示从dao传来的这个字段值rowStart不为null,property的值为传入字段的值-->
<isNotEmpty property="rowStart">
<!--isGreaterThan表示如果参数大于值则查询条件有效,property的值为传入字段的值,compareValue为比较的值-->
<isGreaterThan property="rowStart" compareValue="0">
<!--<![CDATA[ ]]> 括号中为拼接的值-->
<![CDATA[SELECT * FROM ( ]]>
</isGreaterThan>
</isNotEmpty>
</isPropertyAvailable>
<isPropertyAvailable property="rowEnd">
<isNotEmpty property="rowEnd">
<isGreaterThan property="rowEnd" compareValue="0">
<![CDATA[SELECT ROWNUM RN, QLIST.* FROM (]]>
</isGreaterThan>
</isNotEmpty>
</isPropertyAvailable>
</sql>
<sql id="sql_row_end">
<isPropertyAvailable property="rowEnd">
<isNotEmpty property="rowEnd">
<isGreaterThan property="rowEnd" compareValue="0">
<![CDATA[) QLIST WHERE ROWNUM <= #rowEnd:Integer# ]]>
</isGreaterThan>
</isNotEmpty>
</isPropertyAvailable>
<isPropertyAvailable property="rowStart">
<isNotEmpty property="rowStart">
<isGreaterThan property="rowStart" compareValue="0">
<![CDATA[ ) WHERE RN >= #rowStart:Integer# ]]>
</isGreaterThan>
</isNotEmpty>
</isPropertyAvailable>
</sql>
其次,当我们要拼接时,只需引用<sql></sql>即可,通过id引用,include关键字引用。
//通过include关键字引用,refid中为引用公共查询语句<sql></sql>中的id值 <include refid="sql_row_start" />
既当我们在xml中引用公共查询语句时:
<select id="getSelectCloseInfoShow" resultMap="SelectCloseInfoResult" parameterClass="java.util.Map">
<include refid="sql_row_start" />
select * from T_B_auto_close_delete
<!-- property中为字段值 prepend中为连接词-->
<isNotEmpty prepend="where" property="type">
"TYPE"=#type:INTEGER#<!-- 双##中为dao层传递过来的值,冒号后为值得类型-->
</isNotEmpty>
order by ID
<include refid="sql_row_end" />
</select>
就相当于所调用sql语句为:
SELECT * FROM (
SELECT ROWNUM RN, QLIST.* FROM ( --此处拼接
select * from T_B_auto_close_delete where "TYPE"=1 --1为#type:INTEGER#传入的值
order by ID
--此处拼接
)QLIST WHERE ROWNUM <= 10 --10为#rowEnd:Integer#传入的值
) WHERE RN >= 1 --1为#rowStart:Integer#传入的值