Mybatis之动态SQL

目录

前言

一、动态SQL是什么以及为什么要使用动态SQL

二、创建userinfo表

三、if标签 

if标签的使用 

if标签存在的问题

四、trim标签

五、where标签

使用trim标签来模拟where标签：

 六、set标签

使用trim标签模拟set标签：

七、froeach标签

前言

        本篇博客主要介绍Mybatis中的动态SQL的<if>、<trim>、<where>、<set>、<foreach>这几个比较常用的标签。

一、动态SQL是什么以及为什么要使用动态SQL

动态SQL的官方文档：mybatis – MyBatis 3 | 动态 SQL

动态SQL简单来说就是一种可以根据条件的不同而动态的生成不同的SQL语句的一种方法。

官方的描述：

        动态 SQL 是 MyBatis 的强大特性之一。如果你使用过 JDBC 或其它类似的框架，你应该能理解根据不同条件拼接 SQL 语句有多痛苦，例如拼接时要确保不能忘记添加必要的空格，还要注意去掉列表最后一个列名的逗号。利用动态 SQL，可以彻底摆脱这种痛苦。

        使用动态 SQL 并非一件易事，但借助可用于任何 SQL 映射语句中的强大的动态 SQL 语言，MyBatis 显著地提升了这一特性的易用性。

        如果你之前用过 JSTL 或任何基于类 XML 语言的文本处理器，你对动态 SQL 元素可能会感觉似曾相识。在 MyBatis 之前的版本中，需要花时间了解大量的元素。借助功能强大的基于 OGNL 的表达式，MyBatis 3 替换了之前的大部分元素，大大精简了元素种类，现在要学习的元素种类比原来的一半还要少。

使用动态SQL的场景：

        以下图片是一个填写个人信息的表格，其中里面有的信息是我们必须填写的，有些信息我们可填可不填，这里面其实就会有一个问题，就是当我们遇到要把这个这些信息传到后端，并且后端要将其保存到数据库中，那么当我们在写对应的SQL语句时，就会遇到问题，对于那些可填可不填的字段，那么用户到底填没填呢？没填的话到底是要我自己来给他设置默认值还是数据库中已经设置好默认值了呢？这时候其实SQL语句就不那么好写了。动态SQL其实就是来解决上面场景的问题的。我们可以自己根据传过来的数据动态的去生成我们需要的正确的SQL语句。

二、创建userinfo表

这里我们创建这个用户表主要是为了下面的代码演示，以下的代码演示都会基于这个用户表；

建表SQL：

drop table if exists  userinfo;
create table userinfo(
    id int primary key auto_increment,
    username varchar(100) ,
    password varchar(32) ,
    photo varchar(500) default '',
    createtime timestamp default current_timestamp,
    updatetime timestamp default current_timestamp,
    `state` int default 1
) default charset 'utf8mb4';

三、if标签 

        假设我们现在的业务场景是：我们要往数据库中的userinfo表添加一条数据，我们总共有三个字段，用户名、密码和头像，假设用户名和密码都是必传的，但是头像是非必传的，所以这时候我们就需要使用到动态SQL来判断头像这个字段是否有传递过来。

if标签的使用 

示例代码如下所示：

<insert id="addUser">
        insert into userinfo (username,password
        <if test="photo!=null and photo!=''">
            ,photo
        </if>)
        values(#{username},#{password}
        <if test="photo!=null and photo!=''">
            ,#{photo}
        </if>)
    </insert>

当只传两个参数时：

 当传递三个参数时：

        通过上面的代码：我们可以感受到动态SQL的强大之处，对于<if>标签，我们需要注意的有里面的test属性是必须设置的，test属性里面需要写的是if的判断条件，判断条件成立，就执行if中的内容，反之就不执行。

if标签存在的问题

        但是if标签也是有不足之处，考虑一下当这里我们要传的username、password、photo三个属性都是非必传的，那么我们就需要每个参数都要进行if判断，那么这时候如果是最后一个参数或者第一个参数没传的话对应得SQL语句就有可能会出现问题，会出现多余的逗号。

如下所示：

只传递两个参数的单元测试运行的情况：

四、trim标签

        在前面我们使用<if>标签出现了在SQL语句结尾遇到多余逗号的问题，这个问题如果单纯使用<if>标签还是非常不好解决的，所以接下来我们就来学习第二个标签，<trim>标签，这个标签的作用就是用来添加前缀或者后缀的，也可以用来删除前缀和后缀。所以这个<trim>标签就可以很好的解决<if>标签出现的多余逗号问题。

trim参数的介绍：

prefix：表示整个语句块，以prefix的值作为前缀

suffix：表示整个语句块，以suffix的值作为后缀

prefixOverrides：表示整个语句块要去除掉的前缀

suffixOverrides：表示整个语句块要去除掉的后缀

trim的使用：

<insert id="addUser3">
        insert into userinfo(
        <trim  suffixOverrides="," >
            <if test="username!=null and username!=''">
                username,
            </if>
            <if test="password!=null and password!=''">
                password,
            </if>
            <if test="photo!=null and photo!=''">
                photo
            </if>
        </trim>
        )
        values(
        <trim suffixOverrides="," >
            <if test="username!=null and username!=''">
                #{username},
            </if>
            <if test="password!=null and password!=''">
                #{password},
            </if>
            <if test="photo!=null and photo!=''">
                #{photo}
            </if>
        </trim>
        )
</insert>

对于以上代码的说明：

以上代码我们使用了trim标签去除了最后面逗号，这样子写就直接解决了上面if标签的问题；

但是为什么我不直接在trim标签里面使用prefix加上右括号'('，然后使用suffix加上左括号')'呢？

        这其实就得来说一下这个trim标签的一个特殊情况了：在trim标签中，当trim标签包裹的语句为空的时候，那么这时候我们在trim标签中设置的要添加的前缀和后缀就不会添加了，所以在我们上面的语句中当三个属性都不传的情况下，就会出现没有括号的情况，那么这就是一个错误的SQL语句，所以我们这里不使用trim标签中的参数来添加括号。

单元测试的结果：

五、where标签

        where标签主要就是用在查询的时候，根据传过来的对象中的属性是否为空来做条件查询。

where的使用：

<select id="getUser" resultType="com.example.blogmybatis.model.UserInfo">
        select * from userinfo
        <where>
            <if test="id!=null and id>0">
                id=#{id}
            </if>
            <if test="username!=null and username!=''">
                and username=#{username}
            </if>
            <if test="password!=null and password!=''">
                and password=#{password}
            </if>
        </where>
</select>

        以上的代码中，值得注意的是：如果使用where标签，那么and是只能写在属性名前面的（id=#{id} and就是and写在后面，在where这里是不允许的）,因为where在最后去and的时候，只会去最后的一个and，如果and写在前面就有可能会报错。这里如果任何条件都不传where标签就不会在sql语句中加where，查询的结果就变成全部查询了。

单元测试的结果：

使用trim标签来模拟where标签：

        使用trim来模拟where标签本质上只要在查询条件之前加上where，接着再根据写法的不同来决定要去除最后一个and还是最前面的and就可以实现了，在我们所有的条件都为空的时候，trim也是不会去加上最前面的where的。

实现代码如下：

    <select id="getUser" resultType="com.example.blogmybatis.model.UserInfo">
        select * from userinfo
        <trim prefix="where" prefixOverrides="and">
            <if test="id!=null and id>0">
                id=#{id}
            </if>
            <if test="username!=null and username!=''">
                and username=#{username}
            </if>
            <if test="password!=null and password!=''">
                and password=#{password}
            </if>
        </trim>
    </select>

单元测试结果及代码：

 六、set标签

        <set>标签就是为了我们在进行修改操作的时候可以根据前端传递过来的条件进行更新数据库，接下来直接通过代码来演示一下set标签的使用；

<update id="updateById">
        update userinfo
        <set>
            <if test="username != null">
                username=#{username},
            </if>
            <if test="password != null">
                password=#{password},
            </if>
            <if test="photo != null">
                photo=#{photo}
            </if>
        </set>
        where id=#{id}
 </update>

单元测试代码及结果:

对于set标签我们需要注意的是，当要修改的所有字段都为空时，生成的sql就是：

update userinfo set where id=? ，这时候是会直接报错的，所以在实际的开发中，我们需要对传递过来的参数进行校验，看看是否全部为空，全部为空的时候就不要去调用对应的接口了，这样子就不会报错了。

使用trim标签模拟set标签：

<update id="updateById">
        update userinfo
        <trim prefix="set" suffixOverrides=",">
            <if test="username != null">
                username=#{username},
            </if>
            <if test="password != null">
                password=#{password},
            </if>
            <if test="photo != null">
                photo=#{photo}
            </if>
        </trim>
        where id=#{id}
</update>

        对于这个使用trim标签模拟的set标签，和上面的set标签一样，如果我们传递要修改的所有字段为空时，那也是回直接报错的，所以这里依然需要在参数传递过来之前先校验一遍参数。

七、froeach标签

        foreach标签主要用于做批量的操作时使用，可以是批量删除或批量增加等等，

foreach参数介绍：

collection：绑定⽅法参数中的集合，如 List，Set，Map或数组对象

item：遍历时的每⼀个对象

open：语句块开头的字符串

close：语句块结束的字符串

separator：每次遍历之间间隔的字符串 

        接下来我们假设要批量的从数据库中删除一批用户，这时候我们就可以使用foreach来进行批量的删除，代码如下：

<delete id="delByIdList">
        delete from userinfo
        where id in
            <foreach collection="idList" item="id" separator="," open="(" close=")">
                #{id}
            </foreach>
</delete>

单元测试代码及结果：