[url]http://lyfei022.blog.163.com/blog/static/82558312009313104138320/[/url]
这两天项目用到ibatis,碰到and or的联合查询,语句像这样的
select * from table where xxx = "xxx" and (xxx="xxx" or xx1="xx1")
google,baidu了都找不到解决的方法,就是不知道括号往哪加
后面看到ibatis的动态迭代iterate里有个open="(",close=")"
我就在<dynamic prepend="and" 后面加上 open="(",close=")">一试,果然SQL拼凑正确
下面贴出动态查询代码备忘:
然后使用:<include refid="searchValidDate"></include>
包含到sql里面去
这两天项目用到ibatis,碰到and or的联合查询,语句像这样的
select * from table where xxx = "xxx" and (xxx="xxx" or xx1="xx1")
google,baidu了都找不到解决的方法,就是不知道括号往哪加
后面看到ibatis的动态迭代iterate里有个open="(",close=")"
我就在<dynamic prepend="and" 后面加上 open="(",close=")">一试,果然SQL拼凑正确
下面贴出动态查询代码备忘:
<sql id="condition">
<dynamic prepend="and">
<isNotEmpty prepend="and" property="starttime">
<![CDATA[ starttime >= #starttime# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="endtime">
<![CDATA[ starttime <= #endtime# ]]>
</isNotEmpty>
<isNotEmpty prepend="and" property="rentcompany">
<![CDATA[ rentcompany like '%$rentcompany$%' ]]>
</isNotEmpty>
</dynamic>
<dynamic prepend="and" open="(" close=")">
<isNotNull prepend="or" property="bilkcar">
<![CDATA[ bilkcar = 1 ]]>
</isNotNull>
<isNotNull prepend="or" property="guarantycar">
<![CDATA[ guarantycar = 1 ]]>
</isNotNull>
<isNotNull prepend="or" property="filchpart">
<![CDATA[ filchpart = 1 ]]>
</isNotNull>
<isNotNull prepend="or" property="causetrouble">
<![CDATA[ causetrouble = 1 ]]>
</isNotNull>
<isNotNull prepend="or" property="nohandle">
<![CDATA[ nohandle = 1 ]]>
</isNotNull>
<isNotNull prepend="or" property="inarrears">
<![CDATA[ inarrears = 1 ]]>
</isNotNull>
</dynamic>
</sql>
然后使用:<include refid="searchValidDate"></include>
包含到sql里面去