MyBatis 操作数据库（构造动态 SQL）

前言

        动态 SQL 是 Mybatis 的强⼤特性之⼀，能够完成不同条件下不同的 sql 拼接。

标签

        我们在填写用户信息的时候经常会看到如下的界面，用户信息中包含必填信息和非必填信息，非必填信息是填和不填都可以的，那这样的话插入到数据库中的数据有多种情况，SQL 语句也有多种情况，比如地址管理，我可以选择填，也可以选择不填，那么 SQL 语句的插入语句就要对应插入或者不插入

        要想完成这个效果，我们就需要构造动态 SQL

接口定义：

//通过用户提供的数据，动态构造 SQL 语句进行插入
Integer insertUserByCondition(UserInfo userInfo);

Mapper.xml实现:

"insertUserByCondition">
        insert into userinfo
            "(" suffix=")" suffixOverrides=",">
                <if test="username!=null">
                    username,
                if>
                <if test="password!=null">
                    password,
                if>
                <if test="age!=null">
                    age,
                if>
                <if test="gender!=null">
                    gender,
                if>
                <if test="phone!=null">
                    phone
                if>
            
        values
        "(" suffix=")" suffixOverrides=",">
            <if test="username!=null">
                #{username},
            if>
            <if test="password!=null">
                #{password},
            if>
            <if test="age!=null">
                #{age},
            if>
            <if test="gender!=null">
                #{gender},
            if>
            <if test="phone!=null">
                #{phone}
            if>
        
    

          标签的 test 属性表示判断的内容，如下的代码，判断参数 username 的值是否为 null，相当于判断用户是否输入 username，如果不为 null，说明用户输入了 username ，就需要插入数据到 username 字段，所以拼接 username, 到 SQL 语句中

<if test="username!=null">
    username,
if>

        如果所有的参数都赋了值，构造的 SQL 语句就是：

insert into userinfo( username, password, age, gender, phone )
values(#{username},#{password},#{age},#{gender},#{phone});

        如果参数 gender 和 phone 都没赋值，就是：

insert into userinfo( username, password, age)
values(#{username},#{password},#{age});

        构造的 SQL 语句是根据用户的输入动态改变的

标签

        在上面 Mapper.xml 实现的代码中，我们用到了   标签

标签中有如下属性：

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

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

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

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

       在上面 Mapper.xml 实现的代码中，我们使用了 标签进行了以下处理

• 基于 prefix 配置，开始部分加上 ( 

• 基于suffix 配置，结束部分加上 )

 • 多个组织的语句都以 ,  结尾，在最后拼接好的字符串还会以 ,  结尾，会基于 suffixOverrides 配置去掉最后⼀个 ,  

标签

         标签只会在⼦元素有内容的情况下才插⼊where⼦句，⽽且会⾃动去除⼦句的开头的 AND 或 OR

        看下⾯这个场景, 系统会根据我们的筛选条件, 动态组装 select 语句的 where 条件，来查询到我们指定的内容

        假设所有的参数都输入，我们得到的 SQL 语句如下：

SELECT id,username,password,age,gender,phone,delete_flag,create_time,update_time 
FROM userinfo 
WHERE id = #{id}
AND username = #{username} 
AND gender = #{gender};

        接口定义:

 //通过用户提供的数据，动态构造 SQL 语句进行查询
List selectByCondition(UserInfo userInfo);

        Mapper.xml实现:

    "selectByCondition" resultType="com.example.springbootdemo.dao.UserInfo">
        select id,username,password,age,gender,phone,delete_flag,create_time,update_time
        from userinfo
        
            <if test="id!=null">
                and id=#{id}
            if>
            <if test="username!=null">
                and username=#{username}
            if>
            <if test="gender!=null">
                and gender=#{gender}
            if>
        
    

        假设用户一个参数都没有输入，得到的 SQL 语句就是：

select id,username,password,age,gender,phone,delete_flag,create_time,update_time
from userinfo;

        假设用户没有输入 id 参数，得到的 SQL 语句就是：

SELECT id,username,password,age,gender,phone,delete_flag,create_time,update_time 
FROM userinfo 
WHERE username = #{username} 
AND gender = #{gender};

标签

         标签动态的在SQL语句中插⼊set关键字，并会删掉额外的逗号.(⽤于update语句中)

        根据用户传入的参数，动态构造 SQL 语句，插入相关内容

        接⼝定义:根据传⼊的用户 id 属性，修改其他传入参数的属性

//通过用户提供的数据，动态构造 SQL 语句进行修改
Integer updateByCondition(UserInfo userInfo);

        Mapper.xml实现:

    "updateByCondition">
        update userinfo
        
            <if test="username!=null">
                username=#{username},
            if>
            <if test="age!=null">
                age=#{age},
            if>
            <if test="deleteFlag!=null">
                delete_flag=#{deleteFlag}
            if>
        
        where id=#{id}
    

标签

对集合进⾏遍历时可以使⽤该标签。

标签有如下属性：

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

• item：遍历时的每⼀个对象 

• open：语句块开头的字符串 

• close：语句块结束的字符串 

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

        有以下场景我们将要删除的用户 id 都集中放到一个 List 列表当中，在删除的时候，将列表中的用户 id 所指向的信息都进行删除

        假设列表当中的数据为{1，2，3，4，5}，我们要构造的 SQL 语句就应该为:

delete from userinfo where id in(1,2,3,4,5);

        接口定义：

//给定一个 id 集合，将集合中的所有 id 对应的用户数据都删除
Integer deleteByIds(List ids);

        Mapper.xml实现:

    "deleteByIds">
        delete from userinfo
               where id in
                     "ids" item="id" separator="," open="(" close=")">
                         #{id}
                     
    

       根据一开始提到的例子，我们通过 标签要构造出来的 SQL 结构应该为 (1,2,3,4,5)，1,2,3,4,5 要通过遍历集合中的内容获得、

collection="ids" 表示绑定了参数中的集合 ids 

item="id" 表示遍历集合得到的数据放到 id 中，通过 id 来使用

separator=","  遍历集合得到的数据之间通过  , 进行分割

open="("  标签包含的语句块以 "(" 作为开头

close=")"  标签包含的语句块以 ")" 作为结尾

和 标签

        在xml映射⽂件中配置的SQL，有时可能会存在很多重复的⽚段，此时就会存在很多冗余的代码

        我们可以通过  标签提取重复的 SQL 片段进行封装，通过  标签引用封装好的 SQL 片段

        通过  标签封装:

        通过 id 属性为封装的 SQL 片段命名，方便后续进行调用

"allColumn">
    id,username,password,age,gender,phone,delete_flag,create_time,update_time

        通过 标签引用：

        通过 refid 属性设置要引入的 SQL 片段

    "selectByCondition2" resultType="com.example.springbootdemo.dao.UserInfo">
        select 
            "allColumn">
            from userinfo
    

         最终得到的 SQL 语句为：

select id,username,password,age,gender,phone,delete_flag,create_time,update_time
from userinfo;