一. 动态sql
概念:sql语句的内容并不是固定的,会通过一些条件判断拼接成最终符合要求的sql语句。
本篇所讲的动态SQL,是mybatis通过标签元素的形式, 如if, choose, when, otherwise, trim, where, set, foreach等标签完成对sql的拼接功能,使用起来也非常灵活方便,大大提高了开发人员的工作效率!
二. 使用场景
下面我们将以User表来举例说明:
1. where + if 元素
先来个简单的需求,根据 username 和 sex 来查询user用户,首先看下普通的sql:
<select id="selectUserByNameAndSex" resultType="com.kzy.mybatis.entity.User"
parameterType="com.kzy.entity.User">
select * from user where username=#{username} and sex=#{sex}
</select>
这种方式如果其中一个参数为空,可能就会导致最终查询不到数据。
我们现在想实现如果username为空,将只根据sex来查询;如果sex为空,我们将只根据username来查询。那么可以使用 where + if 标签进行判断,sql如下:
<select id="selectUserByNameOrSex" resultType="com.kzy.mybatis.entity.User"
parameterType="com.kzy.entity.User">
select * from user
<where>
<if test="username != null">
username=#{username}
</if>
<if test="sex != null">
and sex=#{sex}
</if>
</where>
</select>
if 元素:即根据条件判断是否显示其里面的内容。 where 元素:自行判断若下面的子元素有内容,则此处会添加一个'where',如果下面的子元素无内容,即条件判断都为空,则此处不添加'where'。此外,如果where标签内容以'and' 或 'or'开头的话,会将and/or自动剔除(否则 'where and/or' 连在一起会报语法错误)。
2. set + if 元素
不仅查询操作可能会用到动态sql,有时一些更新操作也会需要根据前端传来的参数进行判断,拼接符合条件的sql语句,如下:
<update id="updateUserById" parameterType="com.kzy.mybatis.entity.User">
update user u
<set>
<if test="username != null and username != ''">
u.username = #{username},
</if>
<if test="sex != null and sex != ''">
u.sex = #{sex}
</if>
</set>
where id=#{id}
</update>
若第一个条件 username 为空,那么 sql 语句为:update user u set u.sex=? where id=?
若第一个条件不为空,那么 sql 语句为:update user u set u.username = ? ,u.sex = ? where id=?
多个条件以此类推,最后一个if标签里的内容结尾不用加逗号,是为了防止出现 '... , where ...' 语法错误。
3. choose + when + otherwise 元素
有时候,我们不想使用所有的条件,而只是想从多个条件中选择一个使用。针对这种情况,MyBatis 提供了 choose 元素,它有点像 Java 中的 switch 语句。
<select id="selectUserByChoose" resultType="com.kzy.mybatis.entity.User" parameterType="com.kzy.mybatis.entity.User">
select * from user
<where>
<choose>
<when "id != null and test=id !='' ">
id=#{id}
</when>
<when test="username != null and username !='' ">
and username=#{username}
</when>
<otherwise>
and sex=#{sex}
</otherwise>
</choose>
</where>
</select>
业务还是查询user,三个条件分别为 id,username,sex,但是策略变为只选择一个作为查询条件:传入 “id” ,对应的sql语句就是 select * from user where id=?;
传入 “username” ,对应的sql语句就是 select * from user where username=?;
如果前两者都没有传入,那么默认选择<otherwise>标签里的内容,对应的查询语句为 select * from user where sex=?;
4. 自定义 trim 元素
如果 where 或者 set 元素与你期望的不太一样,你也可以通过自定义 trim 元素来定制 where 或 set 的功能,非常实用!
<1>. 自定义 trim 元素改写上面的 where + if 语句
<select id="selectUserByNameOrSex" resultType="com.kzy.mybatis.entity.User" parameterType="com.kzy.mybatis.entity.User">
select * from user
<!-- <where>
<if test="username != null">
username=#{username}
</if>
<if test="sex != null">
and sex=#{sex}
</if>
</where> -->
<!-- 改写后的效果 -->
<trim prefix="where" prefixOverrides="and | or">
<if test="username != null">
username=#{username}
</if>
<if test="sex != null">
and sex=#{sex}
</if>
</trim>
</select>
prefix:插入 prefix 属性中指定的内容,即前缀。
prefixoverride:前缀覆盖,去掉第一个and 或者or,即 prefixoverride 属性中的内容,此处为了防止拼接sql时出现" where and "这种情况。
<2>. 自定义 trim 元素改写上面的 set + if 语句
<update id="updateUserById" parameterType="com.kzy.mybatis.entity.User">
update user u
<!-- <set>
<if test="username != null and username != ''">
u.username = #{username},
</if>
<if test="sex != null and sex != ''">
u.sex = #{sex}
</if>
</set> -->
<!-- 修改后的内容 -->
<trim prefix="set" suffixOverrides=",">
<if test="username != null and username != ''">
u.username = #{username},
</if>
<if test="sex != null and sex != ''">
u.sex = #{sex},
</if>
</trim>
where id=#{id}
</update>
suffixoverride:后缀覆盖,去掉最后一个逗号,即 suffixoverride 属性中的内容,此处为了防止拼接sql时出现尾部多个逗号这种情况。
5. foreach 元素
foreach 元素的非常强大,它允许你指定一个集合,声明可以在元素体内使用的集合项(item)和索引(index)变量,它也允许你指定开头与结尾的字符串以及集合项迭代之间的分隔符,不会有不必要的语法错误。
你可以将任何可迭代对象(如 List、Set 等)、Map 对象或者数组对象作为集合参数传递给 foreach 。当使用可迭代对象或者数组时,index 是当前迭代的序号,item 的值是本次迭代获取到的元素。当使用 Map 对象(或者 Map.Entry 对象的集合)时,index 是键,item 是值。
例如:现在我们有个简单的sql语句:select * from user where id in (1,2,3);
现在我们来对它进行改写 :
<select id="selectUserByListId" parameterType="java.util.List" resultType="com.kzy.mybatis.entity.User">
select * from user
where id in
<!--
collection:指定输入对象中的集合属性,可以是array数组,也可是list集合
item:每次遍历生成的对象
open:开始遍历时的拼接字符串
close:结束时拼接的字符串
separator:遍历对象之间需要拼接的字符串
select * from user where id in (1,2,3)
-->
<foreach collection="list" item="id" open="(" close=") " separator=",">
#{id}
</foreach>
</select>
foreach 元素内各个属性对应的含义:
collection:指定输入对象中的集合属性,可以是array数组,也可是list集合
item:每次遍历生成的对象
open:开始遍历时的拼接字符串
close:结束时拼接的字符串
separator:遍历对象之间需要拼接的字符串
批量增加
批量删除
总结:
动态sql其实就是一个字符串拼接的过程,只不过mybatis帮我们封装好了方法,不需要我们自行拼接,按照格式来写就可以实现拼接过程,使用起来也比较方便,不容易出错。平时我们在写动态sql的时候,最好根据业务需求先写出原生sql,然后使用mybatis框架选择合适的元素,对照格式进行修改即可!