526互联

【SpringBootWeb入门-17】Mybatis-基础操作-动态SQL

发布时间 2023-12-21 23:21:59作者: hiker0412

1、章节回顾

上一篇文章我们讲解完了Mybatis基础操作，本篇继续学习Mybatis中非常重要的功能：动态SQL。

什么是动态SQL：随着用户的输入或外部条件的变化而变化的SQL语句，我们称为动态SQL。简单说SQL语句不是固定的，是动态变化的。

就拿我们上一篇所提到的根据条件来查询员工的SQL语句来说：根据条件姓名、性别、入职时间来查询员工表emp数据，我们的案例是三个条件都指定了，SQL语句已经固定的了，如果三个条件都为空，这样子是查不到数据的；或者说只想根据姓名来查询，后面两个条件为空，这样子也无法查到数据。

实际上，我们的SQL语句应该灵活可变的，如果想只根据姓名查询，那么后面的性别、入职时间条件就不需要；如果想根据查询性别，那么姓名、入职时间条件也不需要。简单来说，就是根据实际情况来灵活变化查询SQL（动态SQL）。

那么动态SQL是怎么实现的呢？Mybatis给我们提供了基于这种场景的动态SQL标签：if标签。if标签是用来做条件判断的，如果条件成立，才会去拼接SQL语句，如果不成立就不拼接。

下面我们来通过动态SQL标签来改造上一篇的根据条件来查询员工的SQL语句。

2、动态SQL-if标签

上一篇的根据条件来查询员工的SQL语句如下：

<select id="list" resultType="com.hiker.pojo.Emp">
        select *
        from emp
        where name like concat('%', #{name}, '%')
          and gender = #{gender}
          and entrydate between #{begin} and #{end}
        order by update_time desc
    </select>
View Code

我们通过if标签来改造成动态SQL，改造如下：

<select id="list" resultType="com.hiker.pojo.Emp">
        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>
View Code

我们把测试程序的代码参数修改一下，入职时间设置为空：List<Emp> empList = empMapper.list("张", (short) 1, null,null)

重新运行测试代码，输出的结果可以看到，SQL是动态生成：

2、动态SQL-where标签

上面使用if标签改造的动态SQL语句已经成功执行输出，但是这里还有个问题，就是如果第一个字段name我们不查询，只根据查询性别来查询，那么就会报错如下：

此时生成的动态SQL就会变成：select * from emp where and gender = ?  order by update_time desc

很明显出现了SQL错误，where关键字后面紧跟着了and关键字。这是因为我们上面的动态SQL使用的if标签，where后面的第一个条件为空跳过了，接着是第二个条件“and gender = ?”，所以拼接出来的SQL语句出现语法错误。

那么如何来解决这个问题呢？我们可以通过使用where标签来解决这个问题。

<where>标签：where元素只会在子元素有内容的情况下才插入where字句，而且会自动去除字句的开头的AND或者OR关键字。

例如上面的动态SQL语句，使用了where标签后，那么where后面的第一个条件为空跳过了，接着是第二个条件“gender = ?”，会自动去除掉and关键字，生成的动态SQL就不会再有语法报错。

下面我们来重新改造动态SQL语句：

<select id="list" resultType="com.hiker.pojo.Emp">
        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>
View Code

改造后我们重新运行测试代码，输出SQL语句：select * from emp WHERE gender = ? order by update_time desc，结果如下：

同理，如果遇上update的SQL语句，也会有set标签，动态地在行首插入 SET 关键字，并会删掉额外的逗号。

3、动态SQL-foreach标签

接下来我们来继续学习动态SQL的foreach标签，这个标签用来做循环遍历，foreach标签里面有几个属性，分别如下：

  • collection：要遍历的集合
  • item：遍历出来的元素
  • separator：每一次遍历使用的分隔符
  • open：遍历开始前要拼接的SQL片段
  • close：遍历结束后要拼接的SQL片段

我们以一个批量删除员工表数据（批量删除id为14、15、16的数据）为例，来看看如何使用foreach标签。

批量删除的SQL代码：delete from emp where id in (14,15,16)

对应的接口方法：

package com.hiker.mapper;

import com.hiker.pojo.Emp;
import org.apache.ibatis.annotations.*;

import java.time.LocalDate;
import java.util.List;

@Mapper
public interface EmpMapper {

    //批量删除员工
    public void deleteByIds(List<Integer> ids);
}
View Code

对应的XML映射文件：

<delete id="deleteByIds">
        delete from emp where id in
        <foreach collection="Ids" item="id" separator="," open="(" close=")">
            #{id}
        </foreach>
    </delete>
View Code

对应的测试代码：

package com.hiker;

import com.hiker.mapper.EmpMapper;
import com.hiker.pojo.Emp;
import org.apache.ibatis.annotations.Param;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.Arrays;
import java.util.List;

@SpringBootTest
class SpringbootMybatisCrudApplicationTests {

    @Autowired
    private EmpMapper empMapper;

    //批量删除员工
    @Test
    public void testDeleteByIds() {
        List<Integer> ids = Arrays.asList(14,15,16);
        empMapper.deleteByIds(ids);
    }

}
View Code

运行单元测试的方法，看到动态SQL语句已正确生成并且成功执行批量删除三条记录。

这里有个新增接口小技巧：在EmpMapper里面新增deleteByIds接口，在写完后可以光标放在接口名上，按住Alt+回车+回车，会自动生成XML里面的SQL代码标签，如下图：

4、动态SQL-sql&include标签

接下来我们来继续学习动态SQL的sql、include两个标签，这两个标签是配套使用的。在学习这两个标签之前，我们先来分析一下上面写的SQL代码所存在的问题。

我们可以看到，上面截图的sql代码有两个查询模块，一个是根据多个条件来查询数据，一个是根据id来查询数据，但是我们可以看到，两段查询模块都有相同的部分：

select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time from emp

如果在一个XML映射文件中存在大量的相同性sql代码，如果在项目后期需要修改这块相同的sql代码，那么需要修改的地方就会很多，容易出错，代码复用性较差，这个时候我们就可以使用sql、include两个标签来解决这个问题。

  • <sql>：定义可重用的 SQL 片段。
  • <include>：通过属性refid，指定包含的sql片段。

对应的XML映射文件代码改造：

    <!--  抽取共用的SQL语句 -->
    <sql id="commonSelect" >
        select id, username, password, name, gender, image, job, entrydate, dept_id, create_time, update_time
        from emp
    </sql>

    <select id="list" resultType="com.hiker.pojo.Emp">
        <!--  引用进来共用的SQL语句 -->
        <include refid="commonSelect"></include>
        <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>
View Code

重新运行查询的测试方法，可以看到查询记录也可以正常加载。

关于Mybatis的相关知识点我们已经全部学习完毕了，下一篇我们就开始结合前端来做SpringBootWeb案例小项目学习。