假设有如下的mapper:
|
|
<select id=”testForeach” parameterType=”map” resultType=”Student”> Select * from student <where> <if test=”ID != null and ID != ‘’ ”> ID = #{ID} </if> <if test=” IDArr != null and IDArr.size()>0”> And ID IN <foreach collection="IDArr" open="(" separator="," close=")" item="ID"> ${ID} </foreach> </if> </where> </select> |
这个动态SQL很简单，如果参数中有ID字段，那么将SQL组装为：
|
|
Select * from student where ID = #{ID} |
如果参数中有IDArr，那么将SQL组装成
|
|
Select * from student where ID IN ( ‘123’, ’234’,…..) |
计划的很完美，但实际却不是这样的，当有参数IDArr时，组装成的SQL却为：
|
|
Select * from student where ID = ‘998’ AND ID IN ( ‘123’, ’234’,…..,’998’) |
解决办法有
1）? 将红色的ID 换成别的名称，比如“item”。
2）? 这两个if 是对同一个字段判断，改为choose… when 结构
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
<select id=”testForeach” parameterType=”map” resultType=”Student”> Select * from student <where> <choose> <when test=”ID != null and ID != ‘’ ”> ID = #{ID} </when> <when test=” IDArr != null and IDArr.size()>0”> And ID IN <foreach collection="IDArr" open="(" separator="," close=")" item="ID"> ${ID} </foreach> </when> </choose> </where> </select> |
Posted in: Mybatis practise | Tags: mybatis
Comments are closed.