ibatis提取共用的语句块SQL

某些情况下，sql有些共用语句块。比如分页和查询where条件。

 

1、where条件如下：

 

<sql id="MS-QUERY-MEMBER-CASES-BY-PARAM-WHERE-CONDITION">
<isNotEmpty property="searchCaseResult">
			<isNotEqual property="searchCaseResult" compareValue="0">
				<![CDATA[and
					bitand(amc.CASE_RESULT,#searchCaseResult#) = #searchCaseResult#
				]]>
			</isNotEqual>
			<isEqual property="searchCaseResult" compareValue="0">
				<![CDATA[and
					amc.CASE_RESULT = 0
				]]>
			</isEqual>
	</isNotEmpty>
</sql>

 

2、查询和分页如何共用呢：加入include

<include refid="MS-QUERY-MEMBER-CASES-BY-PARAM-WHERE-CONDITION" ></include>

 例如：

查询如下：

SELECT amc.* FROM avatar_member_case where 1=1 
<![CDATA[
			 and amc.GMT_CREATE >= #gmtCreateStart:TIMESTAMP# and amc.GMT_CREATE <= #gmtCreateEnd:TIMESTAMP# 
		]]>
		<include refid="MS-QUERY-MEMBER-CASES-BY-PARAM-WHERE-CONDITION" ></include>

 

分页如下：

SELECT count(amc.id) FROM avatar_member_case where 1=1 
<![CDATA[
			 and amc.GMT_CREATE >= #gmtCreateStart:TIMESTAMP# and amc.GMT_CREATE <= #gmtCreateEnd:TIMESTAMP# 
		]]>
		<include refid="MS-QUERY-MEMBER-CASES-BY-PARAM-WHERE-CONDITION" ></include>

 

3、经过测试，需要注意的是：共用部分需要在查询语句之前。否则会报找不到共用部分对应的Id；

 

备注：给自己的提醒，技术人员需要的是热衷于解决技术问题的那股劲。