MyBatis-XML-操作动态-SQL-

2025-03-10 约 2134 字预计阅读 5 分钟

https://bing.ee123.net/img/rand?artid=146132860

MyBatis - XML 操作动态 SQL

1. 前言

之前博文所讲的 MyBatis SQL 操作, 都必须按照注解或者标签中定义的 SQL 严格执行, 一个注解/标签, 对应的是不同的 SQL 操作. 比如: 一个 select 注解中, 定义了 3 个字段, 那这个注解只能插入这三个列的值, 如果想要插入更多的列, 必须重新再写一个注解.

而动态 SQL, 我们可以根据用户输入的值, 动态的进行字段的插入, 当用户输入这一字段的值时, 就插入这一字段. 当用户没有输入这一字段的值时, 就不插入这一字段. 能够完成不同条件下, 不同的 SQL 拼接.

注意: 本文主要通过 XML 完成动态 SQL!!

2. 动态插入

2.1 if 标签

通过 if 标签, 对传入的参数的值进行判断, 若未传入相关列的值, 则不拼接该列的 SQL 片段.

如下图所示, 只有当传入参数的值不为 null 时, 才会拼接该 SQL 片段.

注意: if 标签, 判断的是传入的参数的值(#{} 中的参数), 即 java 属性, 而不是数据库字段!!

因此, 我们就可以 通过这一个 insert 标签对应的方法, 进行任意字段的插入:

但是, 当对最后一个字段传入的值为 null 时, 错误就发生了:

这是因为最后一个字段 gender 的值为 null, 导致 if 标签的判断为 false, 因此 gender 字段的 SQL 没有进行拼接, 导致倒数第二个字段 phone 后多了一个逗号(,), 拼接的内容是 “phone , “因此造成语法错误.

此时, 就需要 trim 标签出马了~~

2.2 trim 标签

trim 标签中有以下属性:

prefix: 表示整个语句块, 以 prefix 的值作为前缀
suffix: 表示整个语句块, 以suffix的值作为后缀
prefixOverrides: 表示整个语句块要去除掉的前缀
suffixOverrides: 表示整个语句块要去除掉的后缀

有 trim 标签, 就可以通过 prefixOverrides/suffixOverrides 属性 解决上文中由于没有拼接最后一个字段, 导致倒数第二个字段后拼接了逗号(,)的问题, 并且还可以通过 prefix/suffix 属性 将 SQL 中的 “(” 和 “)” 自动拼接到 SQL 中:

运行观察结果:

<insert id="insertUser4">
        insert into user_info
               <trim prefix="(" suffix=")" prefixOverrides="," suffixOverrides=",">
                   <if test="username != null">
                       username,
                   </if>
                   <if test="password != null">
                       password,
                   </if>
                   <if test="age != null">
                       age,
                   </if>
                   <if test="phone != null">
                       phone,
                   </if>
                   <if test="gender != null">
                       gender
                   </if>
               </trim>
        values
            <trim prefix="(" suffix=")" prefixOverrides="," suffixOverrides=",">
                <if test="username != null">
                    #{username},
                </if>
                <if test="password != null">
                    #{password},
                </if>
                <if test="age != null">
                    #{age},
                </if>
                <if test="phone != null">
                    #{phone},
                </if>
                <if test="gender != null">
                    #{gender}
                </if>
            </trim>
    </insert>

2.2.1 注解操作动态 SQL

上文是通过 XML 进行的动态 SQL , 在注解中同样也是可以的, 并且方式相同.

若要通过注解完成动态 SQL, 只需将上文 XML 中动态 SQL 的代码, 以字符串的形式拼接到 <script> 标签中即可.

    @Insert("<script>" +
            "insert into user_info " +
            "<trim prefix=\"(\" suffix=\")\" prefixOverrides=\",\" suffixOverrides=\",\">" +
            "<if test=\"username != null\">username,</if>" +
            "<if test=\"password != null\">password,</if>" +
            "<if test=\"age != null\">age,</if>" +
            "<if test=\"phone != null\">phone,</if>" +
            "<if test=\"gender != null\">gender</if>" +
            "</trim> values " +
            "<trim prefix=\"(\" suffix=\")\" prefixOverrides=\",\" suffixOverrides=\",\">" +
            "<if test=\"username != null\">#{username},</if>" +
            "<if test=\"password != null\">#{password},</if>" +
            "<if test=\"age != null\">#{age},</if>" +
            "<if test=\"phone != null\">#{phone},</if>" +
            "<if test=\"gender != null\">#{gender}</if>" +
            "</trim>" +
            "</script>")
    int insertUser4(UserInfo userInfo);

3. 动态查询

进行 select 查询操作时, 常常会用到 where 关键字进行条件查询, 进行条件查询时, 同样也可以通过 if 标签进行动态查询:

但是, 当参数 phone 为 null 时, 就发生错误了:

出错的原因很简单: 因为 phone 属性的值为 null, 导致 where 关键字后跟的是 “and gender”, 导致 SQL 语法错误.

解决办法很简单, 使用 trim 标签, 将 and 关键字去除即可:

但是, 当 phone 和 gender 两个属性都为 null 时, 问题又出现了:

这里错误的原因是: phone 和 gender 都为 null, 导致出现了多余的 where 关键字, 导致 SQL 语法错误.

解决方法有两种:

添加 1 = 1, 保证 where 后有条件
使用 , 去除 where 关键字

3.1 添加 1 = 1

在 where 关键字后添加 1 = 1, 保证 where 后有条件. 并在每条拼接的 SQL 片段前添加 and, 保证 SQL 片段可以进行拼接:

<select id="selectByCondition" resultType="com.study.mybatis.model.UserInfo">
        select * from user_info
         where 1 = 1
             <if test="phone != null">
                 and phone = #{phone}
             </if>
             <if test="gender != null">
                 and gender = #{gender}
             </if>
    </select>

3.2 where 标签

使用 where 标签代替 where 关键字:

where 标签有两个作用:

当 where 标签语块内没有内容时, 不会生成 where 关键字
当 where 标签语块内有查询条件时, 会自动生成 where 关键字, 并且会去除最前面的 and 关键字

因此, 即使查询条件全为 null, 或者第一个查询条件前有 and 关键字, where 标签都会帮我们解决这些问题:

但是, 在实际工作中, 要避免使用 where 标签, 因为当我们遗漏传入条件查询的参数时, 程序也会正确执行, 而无任何条件限制的 select 是非常严重的!!

<select id="selectByCondition" resultType="com.study.mybatis.model.UserInfo">
        select * from user_info
        <where>
            <if test="phone != null">
                phone = #{phone}
            </if>
            <if test="gender != null">
                and gender = #{gender}
            </if>
        </where>
</select>

4. 动态更新

update 语句也可以结合 if 和 trim 标签, 完成动态更新操作:

<update id="updateUser2">
        update user_info
        set
            <trim suffixOverrides=",">
                <if test="username != null">
                    username = #{username},
                </if>
                <if test="password != null">
                    password = #{password},
                </if>
                <if test="age != null">
                    age = #{age}
                </if>
            </trim>
             where id = #{id}
    </update>

4.1 set 标签

除了上文将 if 和 trim 标签结合完成动态的 update 操作外, 也可以使用 set 标签来完成.

set 标签的作用:

生成 set 关键字
去除多余的逗号(只能去除最后一个逗号)

<update id="updateUser3">
        update user_info
        <set>
            <if test="username != null">
                username = #{username},
            </if>
            <if test="password != null">
                password = #{password},
            </if>
            <if test="age != null">
                age = #{age}
            </if>
        </set>
        where id = #{id}
    </update>

5. foreach 标签

在进行数据库操作时, 我们常会在一个条 SQL 语句中, 对数据批量的进行操作, 如下:

SELECT * FROM user_info where id = 1 or id = 2 or id = 3;

INSERT into user_info (username, `password`) VALUES ('1', '1'), ('2', '2');

UPDATE user_info set delete_flag = 1 WHERE id IN(1, 2, 3);

DELETE from user_info WHERE id IN(4, 5, 6);

如果要通过 MyBatis 对数据进行批量处理, 就需要使用 foreach 标签. \

使用 foreach 批量操作数据, 传入的参数必定是一个集合(包括 Map).

foreach 标签中有以下几个关键属性:

collection (必须): 指定要迭代的集合. ( 如果方法参数是 List ， collection 就是 List 的参数名 )
item (必须): 指定迭代过程中元素的名称.
index (可选): 表示集合中每个元素的索引
separator (可选): 指定每个元素之间的分隔符
open (可选): 循环开始前要添加的字符串. 通常用于在 IN 语句中添加左括号 (
close (可选): 循环结束后要添加的字符串. 通常用于在 IN 语句中添加右括号 )

<!--    DELETE from user_info WHERE id IN(1, 2, 3);-->
    <delete id="deleteUser2">
        delete from user_info where
               id in
               <foreach collection="ids" open="(" close=")" item="id" separator=",">
                   #{id}
               </foreach>
    </delete>

<!--    delete FROM user_info where id = 1 or id = 2 or id = 3;-->
    <delete id="deleteUser3">
        delete from user_info where
        <foreach collection="ids" item="id" separator="or">
            id = #{id}
        </foreach>
    </delete>

<!--    insert into user_info (username, `password`) VALUES ('1', '1'), ('2', '2');-->
    <insert id="insertUser1">
        insert into user_info (username, password) values
        <foreach collection="userInfos" item="user" separator=",">
              (#{user.username}, #{user.password})
        </foreach>
    </insert>

6. sql 标签 & include 标签

sql 标签和 include 标签通常搭配使用.

在 sql 标签中定义一个 sql 片段, 并指定这个 sql 标签的 id.

在其他任何标签中, 就可以通过 include 标签指定某个 sql 标签的 id, 直接引用该 sql 标签中的 sql 片段.

也就是说, 我们可以直接通过 sql 标签的 id, 实现 sql 片段的复用.

注意:
SQL 标签中可以定义任意的 SQL 片段(字符串), 不一定是可执行的 SQL !!

END