工作中Mybatis动态SQL的使用

一、基础概述#

1、什么是动态SQL？#

简单说：动态SQL就是根据条件自动拼接SQL语句。

为什么要用动态SQL？

举个例子：查询用户列表时，用户可能只输入用户名，也可能同时输入用户名和手机号，还可能什么都不输入。如果不用动态SQL，你就得写多个方法：

1
// 不用动态SQL，要写多个方法
2
List<User> listAll();                           // 查全部
3
List<User> listByName(String userName);         // 按用户名查
4
List<User> listByPhone(String phone);           // 按手机号查
5
List<User> listByNameAndPhone(String userName, String phone); // 按用户名和手机号查

用动态SQL，只需要一个方法：

1
// 用动态SQL，一个方法搞定
2
List<User> list(UserQueryDto query);

2、动态SQL能解决什么问题？#

问题	动态SQL解决方案
条件不确定	根据参数有无动态拼接条件
SQL拼接繁琐	自动处理AND、OR、逗号等
代码冗余	一个SQL搞定多种情况

二、常用动态SQL标签#

1、if标签 - 条件判断#

最常用的标签！ 用于判断参数是否满足条件。

基本语法#

1
<if test="判断条件">
2
    SQL语句
3
</if>

使用示例#

1
<select id="list" resultType="User">
2
    SELECT * FROM t_user
3
    <where>
4
        <!-- 如果userName不为空，就拼接这个条件 -->
5
        <if test="userName != null and userName != ''">
6
            AND user_name = #{userName}
7
        </if>
8
        <!-- 如果phone不为空，就拼接这个条件 -->
9
        <if test="phone != null and phone != ''">
10
            AND phone = #{phone}
11
        </if>
12
        <!-- 如果status不为空，就拼接这个条件 -->
13
        <if test="status != null">
14
            AND status = #{status}
15
        </if>
16
    </where>
17
</select>

不同类型的判断方式#

类型	判断方式	示例
Integer类型	只判断null
String类型	判断null和空串
List类型	判断null和size
Boolean类型	直接判断

新人注意：Integer类型判断时不要加空字符串判断！值为0时会被误判为空。

1
<!-- 错误：Integer类型加了空字符串判断 -->
2
<if test="status != null and status != ''">
3
    AND status = #{status}
4
</if>
5
<!-- 当status=0时，这个条件不会生效！ -->
6

7
<!-- 正确：Integer类型只判断null -->
8
<if test="status != null">
9
    AND status = #{status}
10
</if>

2、where标签 - 智能处理WHERE#

where标签的作用：

自动添加WHERE关键字
自动去除多余的AND或OR

使用示例#

1
<select id="list" resultType="User">
2
    SELECT * FROM t_user
3
    <where>
4
        <if test="userName != null and userName != ''">
5
            AND user_name = #{userName}
6
        </if>
7
        <if test="phone != null and phone != ''">
8
            AND phone = #{phone}
9
        </if>
10
    </where>
11
</select>

效果说明：

情况	生成的SQL
两个条件都有	SELECT * FROM t_user WHERE user_name = ? AND phone = ?
只有userName	SELECT * FROM t_user WHERE user_name = ?
条件都为空	SELECT * FROM t_user

注意： where标签会自动去掉第一个AND，所以if里面的AND写在前面没问题。

3、set标签 - 智能处理UPDATE#

set标签的作用：

自动添加SET关键字
自动去除多余的逗号

使用示例#

1
<update id="update">
2
    UPDATE t_user
3
    <set>
4
        <if test="userName != null and userName != ''">
5
            user_name = #{userName},
6
        </if>
7
        <if test="phone != null and phone != ''">
8
            phone = #{phone},
9
        </if>
10
        <if test="email != null and email != ''">
11
            email = #{email},
12
        </if>
13
    </set>
14
    WHERE id = #{id}
15
</update>

效果： 只更新传入的字段，没传的不更新。

4、choose/when/otherwise - 多选一#

类似Java的switch-case，只执行第一个满足条件的分支。

使用示例#

1
<select id="query" resultType="User">
2
    SELECT * FROM t_user WHERE 1=1
3
    <choose>
4
        <when test="userName != null and userName != ''">
5
            AND user_name = #{userName}
6
        </when>
7
        <when test="phone != null and phone != ''">
8
            AND phone = #{phone}
9
        </when>
10
        <otherwise>
11
            AND status = 1
12
        </otherwise>
13
    </choose>
14
</select>

效果说明：

如果userName有值，只按userName查
如果userName没值但phone有值，只按phone查
如果都没有，就查status=1的数据

5、foreach标签 - 遍历集合#

用于批量操作：批量插入、批量更新、IN查询等。

基本语法#

1
<foreach collection="集合名" item="元素名" separator="分隔符" open="开始符号" close="结束符号">
2
    #{元素名}
3
</foreach>

属性	说明
collection	集合参数名
item	遍历时每个元素的变量名
separator	元素之间的分隔符
open	以什么开始
close	以什么结束

使用场景#

（1）IN查询

1
<select id="listByIds" resultType="User">
2
    SELECT * FROM t_user WHERE id IN
3
    <foreach collection="ids" item="id" separator="," open="(" close=")">
4
        #{id}
5
    </foreach>
6
</select>

生成的SQL：SELECT * FROM t_user WHERE id IN (1, 2, 3)

（2）批量插入

1
<insert id="insertBatch">
2
    INSERT INTO t_user (user_name, phone, status)
3
    VALUES
4
    <foreach collection="list" item="user" separator=",">
5
        (#{user.userName}, #{user.phone}, #{user.status})
6
    </foreach>
7
</insert>

（3）批量更新

1
<update id="updateBatch">
2
    <foreach collection="list" item="user" separator=";">
3
        UPDATE t_user
4
        <set>
5
            <if test="user.userName != null">
6
                user_name = #{user.userName},
7
            </if>
8
            <if test="user.phone != null">
9
                phone = #{user.phone},
10
            </if>
11
        </set>
12
        WHERE id = #{user.id}
13
    </foreach>
14
</update>

6、trim标签 - 自定义处理#

更灵活地处理前缀后缀。

使用示例#

1
<!-- 等价于where标签 -->
2
<trim prefix="WHERE" prefixOverrides="AND">
3
    <if test="userName != null and userName != ''">
4
        AND user_name = #{userName}
5
    </if>
6
    <if test="phone != null and phone != ''">
7
        AND phone = #{phone}
8
    </if>
9
</trim>
10

11
<!-- 等价于set标签 -->
12
<trim prefix="SET" suffixOverrides=",">
13
    <if test="userName != null and userName != ''">
14
        user_name = #{userName},
15
    </if>
16
    <if test="phone != null and phone != ''">
17
        phone = #{phone},
18
    </if>
19
</trim>

三、实际工作中的使用场景#

1、查询列表（最常用）#

1
<select id="list" resultType="UserVo">
2
    SELECT * FROM t_user
3
    <where>
4
        <if test="userName != null and userName != ''">
5
            AND user_name LIKE CONCAT('%', #{userName}, '%')
6
        </if>
7
        <if test="phone != null and phone != ''">
8
            AND phone = #{phone}
9
        </if>
10
        <if test="status != null">
11
            AND status = #{status}
12
        </if>
13
        <if test="startTime != null">
14
            AND cr_time >= #{startTime}
15
        </if>
16
        <if test="endTime != null">
17
            AND cr_time <= #{endTime}
18
        </if>
19
    </where>
20
    ORDER BY cr_time DESC
21
</select>

2、动态更新#

1
<update id="updateSelective">
2
    UPDATE t_user
3
    <set>
4
        <if test="userName != null and userName != ''">
5
            user_name = #{userName},
6
        </if>
7
        <if test="phone != null">
8
            phone = #{phone},
9
        </if>
10
        <if test="email != null">
11
            email = #{email},
12
        </if>
13
        up_time = NOW(),
14
    </set>
15
    WHERE id = #{id}
16
</update>

3、动态插入#

1
<insert id="insertSelective">
2
    INSERT INTO t_user
3
    <trim prefix="(" suffix=")" suffixOverrides=",">
4
        <if test="userName != null">
5
            user_name,
6
        </if>
7
        <if test="phone != null">
8
            phone,
9
        </if>
10
        <if test="email != null">
11
            email,
12
        </if>
13
        cr_time,
14
    </trim>
15
    <trim prefix="VALUES (" suffix=")" suffixOverrides=",">
16
        <if test="userName != null">
17
            #{userName},
18
        </if>
19
        <if test="phone != null">
20
            #{phone},
21
        </if>
22
        <if test="email != null">
23
            #{email},
24
        </if>
25
        NOW(),
26
    </trim>
27
</insert>

四、新人常见错误#

1、Integer类型判断错误#

1
<!-- 错误：Integer类型加了空字符串判断 -->
2
<if test="status != null and status != ''">
3
    AND status = #{status}
4
</if>
5

6
<!-- 正确 -->
7
<if test="status != null">
8
    AND status = #{status}
9
</if>

2、忘记加AND或逗号#

1
<!-- 错误：忘记加AND -->
2
<where>
3
    <if test="userName != null">
4
        user_name = #{userName}  <!-- 少了AND -->
5
    </if>
6
    <if test="phone != null">
7
        AND phone = #{phone}
8
    </if>
9
</where>
10

11
<!-- 正确：第一个条件也要加AND，where标签会自动去掉 -->
12
<where>
13
    <if test="userName != null">
14
        AND user_name = #{userName}
15
    </if>
16
    <if test="phone != null">
17
        AND phone = #{phone}
18
    </if>
19
</where>

3、批量操作没处理好#

1
<!-- 错误：批量插入格式不对 -->
2
<insert id="insertBatch">
3
    INSERT INTO t_user (user_name, phone) VALUES
4
    <foreach collection="list" item="user">
5
        (#{user.userName}, #{user.phone})  <!-- 没有分隔符 -->
6
    </foreach>
7
</insert>
8

9
<!-- 正确：加上separator -->
10
<insert id="insertBatch">
11
    INSERT INTO t_user (user_name, phone) VALUES
12
    <foreach collection="list" item="user" separator=",">
13
        (#{user.userName}, #{user.phone})
14
    </foreach>
15
</insert>

4、foreach没有判空#

新人常见错误！如果list为空，<foreach> 不会生成任何内容，SQL就会变成不合法的语法，直接报错。

1
<!-- 错误：没有判空，list为空时SQL变成 INSERT INTO t_user (user_name, phone) VALUES  ← 后面啥都没有，语法错误！ -->
2
<insert id="insertBatch">
3
    INSERT INTO t_user (user_name, phone) VALUES
4
    <foreach collection="list" item="user" separator=",">
5
        (#{user.userName}, #{user.phone})
6
    </foreach>
7
</insert>
8

9
<!-- 错误：同样的问题，list为空时SQL变成 WHERE id IN  ← 后面没有值，语法错误！ -->
10
<select id="listByIds" resultType="User">
11
    SELECT * FROM t_user WHERE id IN
12
    <foreach collection="ids" item="id" separator="," open="(" close=")">
13
        #{id}
14
    </foreach>
15
</select>

正确做法：在Service层判空，list为空时不调用Mapper方法：

1
@Override
2
public void insertBatch(List<UserDto> userList) {
3
    // 在Service层判空，避免传空集合到Mapper
4
    if (CollectionUtils.isEmpty(userList)) {
5
        return;
6
    }
7
    // 分批处理，每批不超过1000条
8
    int batchSize = 1000;
9
    for (int i = 0; i < userList.size(); i += batchSize) {
10
        int end = Math.min(i + batchSize, userList.size());
11
        userMapper.insertBatch(userList.subList(i, end));
12
    }
13
}

总结：<foreach> 一定要配合判空使用。建议在Service层判断，集合为空直接return或抛异常，不要让空集合传到Mapper里。

五、新人避坑指南#

坑	正确做法
Integer判断加空字符串	Integer只判断null
String判断只判断null	还要判断空字符串
第一个if条件不加AND	要加AND，where标签会自动去掉
set里最后没逗号	要加逗号，set标签会自动去掉
foreach没写separator	批量操作要加分隔符
foreach没有判空	在Service层判空，集合为空时不调用Mapper，避免SQL语法错误
复制粘贴不改参数名	仔细检查参数名是否正确

六、调试技巧#

1、开启SQL日志#

MyBatis项目：

1
mybatis:
2
  configuration:
3
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

MyBatis-Plus项目：

1
mybatis-plus:
2
  configuration:
3
    log-impl: org.apache.ibatis.logging.stdout.StdOutImpl

注意区分： MyBatis和MyBatis-Plus只是配置前缀不同，一个是mybatis:，一个是mybatis-plus:。看看项目用的是哪个就配哪个。

开启日志后，控制台会打印实际执行的SQL，检查是否符合预期。

2、使用MyBatis Log Free插件（推荐）#

在IDEA中安装 MyBatis Log Free 插件，可以把MyBatis日志中带?占位符的SQL直接还原成完整的、可执行的SQL，复制到Navicat里直接运行排查问题。

安装方式： IDEA → Settings → Plugins → 搜索 MyBatis Log Free → Install

如果安装失败：

在 Appearance & Behavior->System Settings->HTTP Proxy 路径中勾选 Auto-detect proxy settings 后再重新 install 插件 MyBatis Log Free

使用步骤：

开启SQL日志（上面的第1步）

调用接口后，控制台会打印类似这样的日志：

1
==> Preparing: SELECT * FROM t_user WHERE user_name = ? AND status = ?
2
==> Parameters: 张三(String), 1(Integer)

打开IDEA底部的 MyBatis Log Free 面板（Tools → MyBatis Log Free）

插件会自动还原成完整SQL：

1
SELECT * FROM t_user WHERE user_name = '张三' AND status = 1

复制这条SQL，直接粘贴到Navicat执行，排查数据和条件是否正确

新人必装！ 不装这个插件的话，你需要手动把?和参数一一对应替换，参数多了很容易搞错。有了这个插件一键还原，效率提升很多。

七、终极建议#

工作中90%的动态SQL就是if + where的组合，把这个练熟就够了。

遇到不确定的情况，开启SQL日志，看实际生成的SQL是什么样的，然后调整。

音乐

一、基础概述#

1、什么是动态SQL？#

2、动态SQL能解决什么问题？#

二、常用动态SQL标签#

1、if标签 - 条件判断#

基本语法#

使用示例#

不同类型的判断方式#

2、where标签 - 智能处理WHERE#

使用示例#

3、set标签 - 智能处理UPDATE#

使用示例#

4、choose/when/otherwise - 多选一#

使用示例#

5、foreach标签 - 遍历集合#

基本语法#

使用场景#

6、trim标签 - 自定义处理#

使用示例#

三、实际工作中的使用场景#

1、查询列表（最常用）#

2、动态更新#

3、动态插入#

四、新人常见错误#

1、Integer类型判断错误#

2、忘记加AND或逗号#

3、批量操作没处理好#

4、foreach没有判空#

五、新人避坑指南#

六、调试技巧#

1、开启SQL日志#

2、使用MyBatis Log Free插件（推荐）#

七、终极建议#

支持与分享