某些情况下,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;
备注:给自己的提醒,技术人员需要的是热衷于解决技术问题的那股劲。