(十二)动态SQL之if、trim
Q:mybatis动态SQL有什么用?执行原理?有哪些动态SQL?
mybatis动态SQL可以在xml映射文件内,以标签的形式编写动态SQL,执行原理是根据表达式的值完成逻辑判断并动态拼接SQL的功能
mybatis提供了9种动态SQL标签:trim、where、set、foreach、if、choose、when、otherwise
一、if
<!--
test:判断表达式(OGNL)
从参数中取值进行判断,遇到特殊符号应该写转义字符
组合查询
-->
<select id="selectTeacher" parameterType="teacher" resultType="teacher">
select *
from tb_Teacher
where
<if test="id!=null">
id=#{id}
</if>
<if test="name!=null && name.trim()!=''">
and name=#{name}
</if>
</select>
二、select语句拼接遇到的关键字冗余问题 用trim标签解决
(trim标签用途用于拼接,不限于select语句)
就要用trim标签
prefix="" 整体加个前缀
prefixOverrides="" 去掉整个字符串前面多余的字符
suffix=""
suffixOverrides=""
①若不用trim拼接,则会遇到的问题:select * from tb_Teacher WHERE id=? and
(只输入id,并不输入其他内容)
<select id="selectTeacher2" parameterType="teacher" resultType="teacher">
select *
from tb_Teacher
<!-- select * from tb_Teacher WHERE id=? and -->
<trim prefix="where" suffixOverrides="and">
<if test="id!=null">
id=#{id} and
</if>
<if test="name!=null && name.trim()!=''">
name=#{name} and
</if>
<if test="email!=null && email.trim()!=''">
email=#{email}
</if>
</trim>
</select>
②若不用trim处理,遇到的情况: select * from tb_Teacher WHERE and name=?
(只输入name,并不输入id)
<select id="selectTeacher3" parameterType="teacher" resultType="teacher">
select *
from tb_Teacher
<trim prefix="where" prefixOverrides="and" >
<if test="id!=null">
id=#{id}
</if>
<if test="name!=null && name.trim()!=''">
and name=#{name}
</if>
<if test="email!=null && email.trim()!=''">
and email=#{email}
</if>
</trim>
</select>
三、update更新部分属性
<update id="updateUser" parameterType="teacher" >
update tb_Teacher set
<trim suffixOverrides=","> <!-- 因为有set可能多个属性会有多余的,所以要用trim处理下 -->
<if test="name!=null && name.trim()!=''">
name=#{name},
</if>
<if test="email!=null && email.trim()!=''">
email=#{email}
</if>
</trim>
where id=#{id}
</update>
从这我们可以看出,set、where关键字都可以用trim进行拼接,但是拼接后个人感觉不太直观
<update id="updateUser1" parameterType="teacher" >
update tb_Teacher
<trim prefix="set" suffixOverrides="," suffix="where">
<if test="name!=null && name.trim()!=''">
name=#{name},
</if>
<if test="email!=null && email.trim()!=''">
email=#{email} ,
</if>
</trim>
id=#{id}
</update>