MyBatis之动态SQL

不应该热爱

已于 2023-09-10 20:38:09 修改

阅读量528

点赞数 1

文章标签： mybatis sql java

于 2023-09-10 20:36:16 首次发布

本文链接：https://blog.csdn.net/qq_63218110/article/details/132789892

版权

一、前言

动态SQL介绍，以下是来自官网的定义：

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

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

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

其实通俗的来说，动态SQL的存在就是允许我们在XML中可以使用逻辑判断。

二、if 标签

2.1 为什么需要if标签？

当我们在某公司官网提交简历信息时候，时常会看见以下界面：

我们知道，这些带*是必填项，而不带*是选填项，当应聘者提交信息到服务器后台时，后台又是如何保存的呢？

前提引入（以上图片和下面代码字段不同，图片主要提供必填和选填的场景）

我们知道，其实数据库的字段是有默认值的，可能是Null，亦或者是其他。

例如当前有10个数据，需要插入数据库中其实只有6个（必填项），其他4个用户是没有填写的（这些是非必填项），当后台在编写SQL语句的时候，应该如何设计呢？

能否不运用if标签呢?来看看下面：

我们可以通过SQL语句查询默认字段设置的是什么（也就是当初建表所使用的SQL语句是什么）：show create table userinfo;

由上面可知，state的默认值其实是1。

如果用户在进行填写的时候，没有给选填的信息填值，那么其实就相当于执行以下SQL语句。

insert into userinfo(username,password,state)values("foox","123456",NULL);

那么在数据库的表中，这个插入记录显示如下，state显示为NULL，这其实是不利于后期数据管理和查询的：

我们其实最希望刚刚没有填写state字段时候数据库的显示是如下场景(state在没传入值的时候显示的是1)：

但是上图显示的第六条记录是通过如下SQL执行出来的效果：

 insert into userinfo(username,password)values("foox","123456");

如果是利用以上SQL，那么其实是不能将其运用到上述我们谈到的，有必填和选填信息的表中的。

2.2 if标签的运用

这时候就需要我们动态SQL中的if标签登场了

UserMapper和UserInfo如下所示：

UserMapper.xml如下所示：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.mapper.UserMapper">
    <insert id="addUser">
        insert into userinfo(username,password
        <if test="photo!=null">
            ,photo
        </if>
        ) values( #{username}, #{pwd}
        <if test="photo!=null">
            ,#{photo}
        </if>
        )
    </insert>
</mapper>

PS：需要注意的是，if标签里面的都是属性名，而不是字段。（下面会讲到）

测试方法如下：

运行结果如下：

以上是没有对photo进行传参的情况，下面我们来看同样是利用if标签，对photo传参的情况：

我们发现：正是由于if标签的存在，程序可以动态的生成SQL语句，完美的解决了我们之前所看到的有必传参数和非必传参数的情景。

当然，也可以对上面xml文件中的动态SQL进行一些优化：

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.example.demo.mapper.UserMapper">
    <insert id="addUser">
        insert into userinfo(
        <if test="photo!=null and photo!=''">
            photo,
        </if>
        username,password
        ) values(
        <if test="photo!=null and photo!=''">
            #{photo},
        </if>
        #{username}, #{pwd})
    </insert>
</mapper>

需要注意的是，if标签里面的都是属性名，而不是字段。

运行结果如下：

总结：

if标签里面的test 的key值一定是类里面的属性，而不是字段，而外部就是普通的SQL语句，所以是字段。

三、trim标签

3.1 为什么需要使用trim标签

场景引入：

上面我们介绍了用户界面有必填项和非必填项时需要使用if标签来解决问题，但是如果所有信息都为非必填项时，极端情况下，用户什么都没填，就上传信息到服务器中，那只使用if标签可以解决问题吗？

查看以下代码，试想一下，如果按照以下方式填写，那么if标签中的SQL语句的逗号应该如何解决？

如果只传递img属性，那么SQL中photo后面会有个逗号，其他同理，这里的逗号是无法解决的，会造成SQL语句出错，可能有人会说，那把逗号放入到第二个if标签中，写出 ",username, "是否可以解决问题呢，其实也是不行的，如果这时候photo没传呢？这时候username前面就多了一个 ","。

所以如果只是用if标签的话，那么无论如何是无法解决全部信息或者是多个信息可能都不传的情况的。

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

3.2 trim标签的使用

这时候就需要我们trim标签的登场了：

prefix是添加前缀
suffix是添加后缀
suffixOvrides是去除后缀
prefixOverrides是去除前缀

需要注意的是，在trim标签中，如果没有生成任何代码，那么这里的前缀和后缀是不会添加的。

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

由于我们数据库的表username和pwd不能为空，所以这里我们测试时候就没有全部都不传，

但是还是可以从是否删除逗号和是否允许成功观察trim标签的作用的：

四、 Where标签

4.1 为什么需要使用where标签？

其实是解决MyBatis中多个参数都是非必传参数的问题。

我们先来看一组代码，需要实现的是根据文章id或者文章标题获取文章详情信息：

ArticleMapper.java如下：

ArticleMapper.xml如下所示：

解决方案1：使用1=1的形式（这种方式其实并不是很优雅，所以以下不对其进行测试，了解即可）

<select id="getListByIdOrTitle" resultType="com.example.demo.entity.vo.ArticleInfoVO">
        select * from articleinfo
        where 1=1
        <trim prefixOverrides="and">
            <if test="id!=null and id>0">
                and id=#{id}
            </if>
            <if test="title!=null and title!=''">
                and title like concat('%',#{title},'%')
            </if>
        </trim>
</select>

解决方案2：使用trim标签形式：

<select id="getListByIdOrTitle" resultType="com.example.demo.entity.vo.ArticleInfoVO">
        select * from articleinfo
        <trim prefix="where" suffixOverrides="and">
            <if test="id!=null and id>0">
                id=#{id} and
            </if>
            <if test="title!=null and title!=''">
                title like concat('%',#{title},'%')
            </if>
        </trim>
</select>

测试方法和运行结果如下，这是两个都不传的情况，可以看到，trim标签由于里面是空，所以前缀的where是不存在的。

如果只传一个id的话，那么也是可以正常运行的：

4.2 trim标签的使用

trim标签虽然是解决了这个问题，但是我们在书写程序的时候还要考虑前缀后缀等参数，因此引入where来解决：

   <select id="getListByIdOrTitle" resultType="com.example.demo.entity.vo.ArticleInfoVO">
        select * from articleinfo
        <where>
            <if test="id!=null and id>0">
                id=#{id}
            </if>
            <if test="title!=null and title!=''">
                and title like concat('%',#{title},'%')
            </if>
        </where>
   </select>

而且使用where标签，可以帮助我们去掉where前缀。但是并不能帮我们去掉and后缀。

下面我们对其进行测试，第一种情况，什么都不传：

可以观察到，程序运行无误，当where标签中没有东西的时候，是不会有where语句的。

只传一个参数,发现where标签可以智能的删去and前缀，并且拿到了对应的数据：

当然了，以上<where>标签也可以使用 <trim prefix="where" prefixOverrides="and"> 进行替换。

五、set标签

在update标签中的parameterType：指定了传递给更新操作的参数类型，通常是一个Java对象类型，可以使用对象的属性来传递参数。

由于这里的set标签用法和where标签类似，就不作演示。

<update id="updateById" parameterType="org.example.model.User">
        update user
        <set>
            <if test="username != null">
                username=#{username},
            </if>
            <if test="password != null">
                password=#{password},
            </if>
        </set>
        where id=#{id}
</update>

但是需要注意的是：这里set标签中，虽然每个参数都可以选择不传，但是必须保证set标签中必须有一个参数传递进来，否则会造成SQL语句报错。（这层判断可以放在Controller层，在那里判断调用该方法的时候保证至少传递一个参数进来，如果一个参数都没传递，就不允许调用该方法）。

这就像公司中一样，假设研发部有A，B，C三人，虽然他们周末都可以选择不加班，但是其实还是要保证至少有一个人周末在公司，负责预防突发状况。

以上<set>标签也可以使用 <trim prefix="set" suffixOverrides=",”> 进行替换

六、<foreach>标签

当我们想要进行批量操作的时候，就可以使用foreach标签，<foreach>标签的常用属性如下：

collection：绑定⽅法参数中的集合，如 List，Set，Map或数组对象
item：遍历时的每⼀个对象
open：语句块开头的字符串
close：语句块结束的字符串
separator：每次遍历之间间隔的字符串

这里我们以最常见的批量删除为示例：

正常情况我们其实是这样书写批量删除的,但是实际场景其实对方传来的可能是一个集合：

delete from articleinfo where id in (1,2,3);

因此其实我们就是要将上面代码中的（1，2，3）变成动态的：

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

可能会很快发现以上代码如果当参数没有一个进行传递的时候，是会进行报错的，这里其实也是应该在Controller进行判断的，跟上面的set一样，如果没有传递任何参数，那么就不能调用该方法。

下面我们对其进行测试：

    @Transactional //这里为了不让其去真正的污染数据库，所以加上@Transactional
    @Test
    void delByIdList() {
        List<Integer> idList = new ArrayList<>();
        idList.add(1);
        idList.add(2);
        idList.add(3);
        int result = articleMapper.delByIdList(idList);
        System.out.println("删除条数: " + result);
    }

需要注意的是，代码中的@Transactional与声明式事务时使用的@Transactional虽然是相同的，但是含义并不一样，这里表示的只是会让其进行回滚而已。