问题代码:
<select id="list" resultType="全类名">
select * from emp
where
<if test="name != null">
name like concat('%',#{name},'%')
</if>
<if test="gender != null">
and gender = #{gender}
</if>
<if test="begin != null and end != null">
and entrydate between #{begin} and #{end}
</if>
order by update_time desc
</select>
说明问题:该SQL语句可以用来动态判断三个条件,
1、当第一个条件不为空时,可以正常执行。
2、当第一个条件为空时,会出错
即:select * from emp where and gender = 1 and entrydate between 1998-02-03 and 2020-02-06 order by update_time desc; 黄色字体出现问题,sql语法出现问题
解决问题:
1、利用if标签在开始拼接一个结果为真的字符串,且判断条件和之前第一个条件正好相反,具体如下:
<if test="name == null">
'1'=1
</if>
通过拼接来设定一定存在一个语句,从而避免出现and的错误以及若没有条件只剩下where的问题。
最终语句如下:
<select id="list" resultType="全类名">
select * from emp
where
<if test="name == null">
'1'=1
</if>
<if test="name != null">
name like concat('%',#{name},'%')
</if>
<if test="gender != null">
and gender = #{gender}
</if>
<if test="begin != null and end != null">
and entrydate between #{begin} and #{end}
</if>
order by update_time desc
</select>
2、利用where标签 (方便又快捷)——自动处理相关问题
<select id="list" resultType="全类名">
select * from emp
<where>
<if test="name != null">
name like concat('%',#{name},'%')
</if>
<if test="gender != null">
and gender = #{gender}
</if>
<if test="begin != null and end != null">
and entrydate between #{begin} and #{end}
</if>
</where>
order by update_time desc
</select>