MyBatis⑤动态SQL

动态 SQL

在业务逻辑复杂的情况下，要求 SQL 根据需求而动态变化。

  • 传统 SQL：需要在 service 层对不同逻辑进行判断，代码复杂。
  • 动态 SQL：可根据不同条件自动拼接 SQL 语句。
    • 动态 SQL = SQL 语句 + 条件拼接子句
    • 注意 SQL 语句之间的空格、逗号等。

主要功能

  • if：匹配条件，追加满足条件的所有子句。
  • choose：匹配条件，追加满足条件的一个子句（类似 Java 的 switch-case）
  • trim
  • foreach
  • SQL 片段

if（❗）

1.1、说明

匹配条件，追加满足条件的所有子句。

根据 if 标签的 test 条件表达式，匹配条件。

  • 若没有满足条件的 if 子句，不追加任何子句。
  • 若匹配到任意满足条件的 if 子句，按声明的先后顺序追加子句。

1.2、示例

示例：查询所有用户

  • 若有传入 userId，则拼接 userId 查询条件。
  • 若有传入 name，则拼接 name 模糊查询条件。

1.2.1、Mapper

  • Mapper 接口

    List<User> listUsersByUserIdAndName(@Param("userId") String userId,
                                    @Param("name") String name);

  • Mapper.xml

    • 使用 WHERE 1=1，则 if 子句都以 AND 开头（无需考虑是否添加 AND）

    • 使用 <if> 子句，判断参数是否为空

      <select id="listUsersByUserIdAndName" resultType="user">
    SELECT user_id, name, password
    FROM study_mysql.t_user
    WHERE 1 = 1
    <if test="userId != null">
        AND user_id = #{userId}
    </if>
    <if test="name != null">
        AND name LIKE CONCAT('%',#{name},'%')
    </if>
</select>

1.2.2、测试

修改 userId 和 name 的值，查看 SQL 语句（null 相当于没有传参）

String userId;
String name;

SqlSession sqlSession = MyBatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);

List<User> userList = mapper.listUsersByUserIdAndName(userId, name);
System.out.println(userList);

sqlSession.close();

  • 没传参：不拼接任何子句

    image-20220407184820836

  • 传参：追加相应的子句

    • 传入 userId

      image-20220407185036824

    • 传入 name

      image-20220407185133677

    • 传入 userId 和 name

      image-20220407185259492

choose

2.1、说明

匹配条件，追加满足条件的一个子句（类似 Java 的 switch-case）

根据 choose 标签 中 when 标签的 test 条件表达式，匹配条件。

  • 若匹配到满足条件的 when 子句，追加首个满足条件的 when 子句。
  • 若没有满足条件的 when 子句，有 otherwise 子句则追加，否则不追加任何子句。

2.2、示例

示例：查询所有用户

  • 若匹配到满足条件的 when 子句，追加首个满足条件的 when 子句。
    • 只要有 userId，追加 userId 子句
    • 只有 name，追加 userId 子句
  • 若没有满足条件的 when 子句，追加 otherwise 子句。

2.2.1、Mapper

Mapper 接口不变，修改 mapper.xml 中的 SQL 语句。

Mapper.xml

  • 使用 WHERE 1=1，则 when 子句都以 AND 开头（无需考虑是否添加 AND）

  • <when> 标签使用 test 属性，判断参数是否为空。

    <select id="listUsersByUserIdAndName" resultType="user">
    SELECT user_id, name, password
    FROM study_mysql.t_user
    WHERE 1 = 1
    <choose>
        <when test="userId != null">
            AND user_id = #{userId}
        </when>
        <when test="name != null">
            AND name LIKE CONCAT('%',#{name},'%')
        </when>
        <otherwise>
            AND user_id = 'admin'
        </otherwise>
    </choose>
</select>

2.2.2、测试

  • 没传参：拼接 otherwise 子句

    image-20220407191626622

  • 传参：追加相应的子句

    • 只传入 userId、传入 userId 和 name

      image-20220407191702809

    • 只传入 name

      image-20220407185133677

trim（❗）

3.1、where

动态 SQL 拼接前需要添加 WHERE 1=1 条件，以便追加 AND 子句。

使用 where 标签改进。

  • 匹配到至少一个条件时，才会添加 WHERE 关键字。
  • 自动将子句开头多余的 AND 或 OR 去除。

示例：查询用户

<select id="listUsersByUserIdAndName" resultType="user">
    SELECT user_id, name, password
    FROM study_mysql.t_user
    <where>
        <if test="userId != null">
            AND user_id = #{userId}
        </if>
        <if test="name != null">
            AND name LIKE CONCAT('%',#{name},'%')
        </if>
    </where>
</select>

3.2、set

适用于 UPDATE 语句

  • 匹配到至少一个条件时，才会添加 SET 关键字。
  • 自动将多余的逗号去除。

示例：更新用户

  • Mapper 接口

    int updateUser(User user);

  • Mapper.xml

    <update id="updateUser" parameterType="user">
    UPDATE study_mysql.t_user
    <set>
        <if test="name != null">
            name = #{name},
        </if>
        <if test="password != null">
            password = #{password},
        </if>
    </set>
    WHERE user_id = #{userId}
</update>

3.3、自定义 trim（*）

trim 标签的属性（了解即可）

  • prefix：行首自动添加 prefix 值。
  • prefixOverrides：若行首为 prefixOverrides 值，自动去除。
  • suffix：行尾自动添加 suffix 值。
  • suffixOverrides：若行尾为 suffixOverrides 值，自动去除。

示例：自定义 trim 元素，实现与 set 标签等价的功能。

  • set 标签

    <set>
	...
</set>

  • 自定义 trim 标签

    <trim prefix="SET" suffixOverrides=",">
    ...
</trim>

foreach

4.1、说明

用于遍历集合，通常用于 IN 条件语句。

属性

  • collection：待遍历的集合对象。
  • index：索引，可省略。
    • 可迭代对象或数组：当前下标。
    • Map：Key 值。
  • item：集合项，封装集合遍历的每个元素。
    • 可迭代对象或数组：下标为 index 的元素。
    • Map：Key 对应的 Value 值。
  • open：起始字符串。
  • close：结束字符串。
  • separator：集合项之间的分隔符。

4.2、示例

查询包含指定 name 的用户

  • Mapper 接口

    List<User> listUsersNameIn(@Param("nameList") List<String> nameList);

  • Mapper.xml

    • collection 属性匹配 @Param 注解值。

    • 占位符 #{} 参数为 item 值。

      <select id="listUsersNameIn" resultType="user">
    SELECT user_id, name, password
    FROM study_mysql.t_user
    <where>
        <foreach collection="nameList" item="nameItem"
                 open="name IN(" separator="," close=")">
            #{nameItem}
        </foreach>
    </where>
</select>

测试

向 nameList 中添加属性，查看不同情况的 SQL 语句。

ArrayList<String> nameList = new ArrayList<>();

SqlSession sqlSession = MyBatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(UserMapper.class);

mapper.listUsersNameIn(nameList);

sqlSession.close();

  • 没有属性：不追加任何条件

    image-20220408005710599

  • 传属性：将所有属性拼接到 IN 语句中

    image-20220408005832414

SQL 片段

抽取公共的 SQL 语句，减少重复编码。

  • 使用 sql 标签抽取，使用 include 标签引用。
  • 最好抽取单表查询的基本 SQL 语句。
    • 多表查询具有动态性和不确定性（不同业务的查询列、连接条件不同）
    • 不同业务的数据过滤条件也不同。

示例：根据用户名查询用户

  • 抽取前

    <select id="getUserByName" resultType="user">
    SELECT user_id, name, password
    FROM study_mysql.t_user
    WHERE name = #{name}
</select>

  • 抽取后

    <sql id="selectUser">
    SELECT user_id, name, password
    FROM study_mysql.t_user
</sql>

<select id="getUserByName" resultType="user">
    <include refid="selectUser"/>
    WHERE name = #{name}
</select>
posted @ 2021-07-27 21:30  Jaywee  阅读(75)  评论(0)  编辑  收藏  举报

☝

👇