跳至主要內容

9. 动态SQL语句

PangHu大约 4 分钟

动态SQL语句

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

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

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

1. if语句

​ 使用动态 SQL 最常见情景是根据条件包含 where 子句的一部分

<select id="query" parameterType="user" resultType="user">
    select
    *
    from t_user
    where
    1 = 1
    <if test="username != null">
        and username = #{username}
    </if>
    <if test="address != null">
        and address like #{address}
    </if>
    <if test="gender != null">
        and gender = #{gender}
    </if>
</select>

Dao的接口文件中

public interface UserMapper {
    List<User> query(User user);
}

测试代码

image-20231122145034059
image-20231122145034059
image-20231122145039055
image-20231122145039055

2. where 语句

​ 我们在写SQL语句的where部分的时候为了保证SQL语句的语法正确，我们会加上1=1表达式，如果我们不想这么写的话可以使用<where>标签来替代

<select id="query" parameterType="user" resultType="user">
    select
    *
    from t_user
    where
    1 = 1
    <if test="username != null">
        and username = #{username}
    </if>
    <if test="address != null">
        and address like #{address}
    </if>
    <if test="gender != null">
        and gender = #{gender}
    </if>
</select>
image-20231122145046483
image-20231122145046483
image-20231122145051247
image-20231122145051247

3. choose、when、otherwise

​ 有时候，我们不想使用所有的条件，而只是想从多个条件中选择一个使用。针对这种情况，MyBatis 提供了 choose 元素，它有点像 Java 中的 switch 语句。

<!--
        查询的条件是 如果传递的有username那就按照username来查询，
        否则判断是否传递的有address
        如果有就根据address来查询否则根据id倒序排序输出
    -->
<select id="queryChoose" parameterType="user" resultType="user" >
    select *
    from t_user
    where 1 = 1
    <choose>
        <when test="username != null">
            and username = #{username}
        </when>
        <when test="address != null">
            and address like #{address}
        </when>
        <otherwise>
            order by id desc
        </otherwise>
    </choose>
</select>

测试

image-20231122145058636
image-20231122145058636
image-20231122145106784
image-20231122145106784
image-20231122145113761
image-20231122145113761

4. set语句

​ 当我们要实现动态更新的时候会因为, 问题而造成非常麻烦的后果，这时我们可以通过<set>标签来动态管理

<update id="updateUser" parameterType="user"  >
    update
    t_user
    <set>
        <if test="username != null">
            username = #{username},
        </if>
        <if test="address != null">
            address = #{address},
        </if>
        <if test="gender != null">
            gender = #{gender},
        </if>
    </set>
    where id = #{id}
</update>
image-20231122145126213
image-20231122145126213
image-20231122145131688
image-20231122145131688

5. trim语句

​ trim标记是一个格式化的标记。可以完成set或者where标记的功能

属性说明
prefix前缀
prefixOverrides去掉第一个指定内容
suffix后缀
suffixoverrides去掉最后一个指定内容

替代where的实现

    <select id="queryWhere" parameterType="user" resultType="user">
        select
        *
        from t_user
        <trim prefix="where" prefixOverrides="AND |OR ">
            <if test="username != null">
                and username = #{username}
            </if>
            <if test="address != null">
                and address like #{address}
            </if>
            <if test="gender != null">
                and gender = #{gender}
            </if>
        </trim>
    </select>

效果

image-20231122145138719
image-20231122145138719
image-20231122145143773
image-20231122145143773

替换<set>标签

    <update id="updateUser" parameterType="user"  >
        update
        t_user
        <trim prefix="set" suffixOverrides=",">
            <if test="username != null">
                username = #{username},
            </if>
            <if test="address != null">
                address = #{address},
            </if>
            <if test="gender != null">
                gender = #{gender},
            </if>
        </trim>
        where id = #{id}
    </update>
    <update id="updateUser" parameterType="user"  >
        update
        t_user
        <trim prefix="set" prefixOverrides=",">
            <if test="username != null">
                ,username = #{username}
            </if>
            <if test="address != null">
                ,address = #{address}
            </if>
            <if test="gender != null">
                ,gender = #{gender}
            </if>
        </trim>
        where id = #{id}
    </update>

效果

image-20231122145152330
image-20231122145152330

6. foreach

动态 SQL 的另一个常见使用场景是对集合进行遍历（尤其是在构建 IN 条件语句的时候）

属性说明
collectioncollection属性的值有三个分别是list、array、map三种
open前缀
close后缀
separator分隔符，表示迭代时每个元素之间以什么分隔
item表示在迭代过程中每一个元素的别名
index用一个变量名表示当前循环的索引位置

演示案例

<select id="queryByIds" resultType="user">
        select *
        from t_user
        <where>
            <if test="ids != null">
                id in
                <foreach collection="ids" open=" ( " close=" ) " item="id" separator=",">
                    #{id}
                </foreach>
            </if>
        </where>
    </select>

Mapper接口声明

    List<User> queryByIds(@Param("ids") List<Integer> ids);

效果

image-20231122145200414
image-20231122145200414

foreach的第二种使用方式就是在批量插入数据的时候使用

    <insert id="insertUser" >
        insert into t_user (username,address) values
        <foreach collection="users" separator="," item="user" >
            (#{user.username},#{user.address})
        </foreach>
    </insert>

接口中声明

Integer insertUser(@Param("users") List<User> users);

测试

image-20231122145206027
image-20231122145206027

7. bind语句

bind 元素允许你在 OGNL 表达式以外创建一个变量，并将其绑定到当前的上下文

<select id="selectBlogsLike" resultType="Blog">
  <bind name="pattern" value="'%' + _parameter.getTitle() + '%'" />
  SELECT * FROM BLOG
  WHERE title LIKE #{pattern}
</select>

8. SQL代码片段

​ 可重复使用的SQL语句代码片段

image-20231122145212987
image-20231122145212987
image-20231122145217599
image-20231122145217599