ibatis sql mapping 中原始的sql是这样的。
<select id="searchUsers" parameterClass="User" resultClass="User">
select * from user_profile
<dynamic prepend="where">
<isGreaterThan prepend="AND" property="roleId" compareValue="0">
roleId=#roleId#
</isGreaterThan>
<isNotEmpty prepend="AND" property="loginName">
loginName like '%$loginName$%'
</isNotEmpty>
</dynamic>
</select>
现在我需要增加一个静态的过滤条件 loginName != 'anonymous',如何能够混合动态和静态sql在where子句中呢。
第一种方案:
<select id="searchUsers" parameterClass="User" resultClass="User">
select * from user_profile
<dynamic prepend="where">
loginName != 'anonymous'
<isGreaterThan prepend="AND" property="roleId" compareValue="0">
roleId=#roleId#
</isGreaterThan>
<isNotEmpty prepend="AND" property="loginName">
loginName like '%$loginName$%'
</isNotEmpty>
</dynamic>
</select>
这样配置产生的结果是 roleId条件前没有AND
select * from user_profile where loginName != 'anonymous' roleId=1 AND loginName like '%user%';
第二种方案:
<select id="searchUsers" parameterClass="User" resultClass="User">
select * from user_profile where loginName != 'anonymous'
<dynamic>
<isGreaterThan prepend="AND" property="roleId" compareValue="0">
roleId=#roleId#
</isGreaterThan>
<isNotEmpty prepend="AND" property="loginName">
loginName like '%$loginName$%'
</isNotEmpty>
</dynamic>
</select>
这种方案解决了动态和静态sql的混合问题,产生的sql无论有没有roleid和loginName参数都是正确的。
select * from user_profile where loginName != 'anonymous';
or
select * from user_profile where loginName != 'anonymous' AND roleId=1 AND loginName like '%user%';
<select id="searchUsers" parameterClass="User" resultClass="User">
select * from user_profile
<dynamic prepend="where">
<isGreaterThan prepend="AND" property="roleId" compareValue="0">
roleId=#roleId#
</isGreaterThan>
<isNotEmpty prepend="AND" property="loginName">
loginName like '%$loginName$%'
</isNotEmpty>
</dynamic>
</select>
现在我需要增加一个静态的过滤条件 loginName != 'anonymous',如何能够混合动态和静态sql在where子句中呢。
第一种方案:
<select id="searchUsers" parameterClass="User" resultClass="User">
select * from user_profile
<dynamic prepend="where">
loginName != 'anonymous'
<isGreaterThan prepend="AND" property="roleId" compareValue="0">
roleId=#roleId#
</isGreaterThan>
<isNotEmpty prepend="AND" property="loginName">
loginName like '%$loginName$%'
</isNotEmpty>
</dynamic>
</select>
这样配置产生的结果是 roleId条件前没有AND
select * from user_profile where loginName != 'anonymous' roleId=1 AND loginName like '%user%';
第二种方案:
<select id="searchUsers" parameterClass="User" resultClass="User">
select * from user_profile where loginName != 'anonymous'
<dynamic>
<isGreaterThan prepend="AND" property="roleId" compareValue="0">
roleId=#roleId#
</isGreaterThan>
<isNotEmpty prepend="AND" property="loginName">
loginName like '%$loginName$%'
</isNotEmpty>
</dynamic>
</select>
这种方案解决了动态和静态sql的混合问题,产生的sql无论有没有roleid和loginName参数都是正确的。
select * from user_profile where loginName != 'anonymous';
or
select * from user_profile where loginName != 'anonymous' AND roleId=1 AND loginName like '%user%';