动态sql

发表于 更新于 分类于 Waline： 阅读次数： 本文字数： 7.6k 阅读时长 ≈ 7 分钟

MyBatis 动态 SQL 详解与实战

动态 SQL 是 MyBatis 的核心特性之一，它允许我们根据不同条件动态构建 SQL 语句，有效解决了传统 SQL 拼接带来的语法错误（如多余的 AND、逗号等问题）。深入解析动态 SQL 标签的使用场景、最佳实践及高级技巧。

动态 SQL 核心标签详解

1. if 标签：条件判断

if 标签是动态 SQL 中最基础的条件判断标签，通过 test 属性（OGNL 表达式）决定是否拼接其内部的 SQL 片段。

基础用法
<select id="findUsers" resultType="User">
  SELECT * FROM users
  WHERE 1=1  <!-- 避免所有条件不满足时出现空 WHERE -->
    <if test="username != null and username != ''">
      AND username LIKE #{username}
    </if>
    <if test="age != null">
      AND age > #{age}
    </if>
    <if test="status != null">
      AND status = #{status}
    </if>
</select>
注意事项
  • test 属性中使用 OGNL 表达式，判断字符串非空需同时检查 != null 和 != ''；
  • 数值类型只需判断 != null（避免 != 0 误判合法的 0 值）；
  • 多条件判断可用 &&（需转义为 &&）或 and 连接。

2. where 标签：智能处理条件前缀

where 标签解决了 if 标签可能导致的「多余 AND/OR」问题，它会自动去除条件片段开头的 AND 或 OR，并在有条件时添加 WHERE 关键字。

替代 1=1 的优雅方案
<select id="findUsers" resultType="User">
  SELECT * FROM users
  <where>
    <if test="username != null and username != ''">
      AND username LIKE #{username}  <!-- 开头的 AND 会被自动去除 -->
    </if>
    <if test="age != null">
      AND age > #{age}
    </if>
  </where>
</select>
工作原理
  • 若内部条件均不满足，where 标签不会生成任何内容；
  • 若条件片段以 AND 或 OR 开头，会自动剔除；
  • 若条件片段正常，会在开头添加 WHERE。

3. trim 标签：自定义字符串处理

trim 标签是更灵活的条件拼接工具，通过 prefix、suffix、prefixOverrides、suffixOverrides 四个属性自定义拼接规则，可替代 where 和 set 标签。

属性说明
属性 作用
prefix 给拼接后的整个字符串添加前缀
suffix 给拼接后的整个字符串添加后缀
prefixOverrides 去除拼接字符串开头的指定字符
suffixOverrides 去除拼接字符串结尾的指定字符
替代 where 标签
<trim prefix="WHERE" prefixOverrides="AND | OR">
  <if test="username != null">AND username = #{username}</if>
  <if test="age != null">OR age = #{age}</if>
</trim>
替代 set 标签（更新场景）
<trim prefix="SET" suffixOverrides=",">
  <if test="username != null">username = #{username},</if>
  <if test="age != null">age = #{age},</if>
</trim>

4. set 标签：动态更新字段

set 标签用于 UPDATE 语句，自动添加 SET 关键字，并去除字段末尾多余的逗号。

示例：只更新非空字段
<update id="updateUser">
  UPDATE users
  <set>
    <if test="username != null and username != ''">
      username = #{username},  <!-- 末尾逗号会被自动去除 -->
    </if>
    <if test="age != null">
      age = #{age},
    </if>
    <if test="status != null">
      status = #{status}
    </if>
  </set>
  WHERE id = #{id}
</update>

5. choose-when-otherwise：分支选择

类似 Java 的 switch-case，只执行第一个满足条件的 when 标签，若所有 when 都不满足，则执行 otherwise 标签（可选）。

示例：优先按 ID 查询，其次按用户名，否则查所有
<select id="findUser" resultType="User">
  SELECT * FROM users
  <where>
    <choose>
      <when test="id != null">
        id = #{id}
      </when>
      <when test="username != null and username != ''">
        username LIKE #{username}
      </when>
      <otherwise>
        status = 1  <!-- 默认查询状态为1的用户 -->
      </otherwise>
    </choose>
  </where>
</select>

6. foreach 标签：遍历集合

foreach 标签用于遍历数组或集合，常用于 IN 条件、批量插入、批量更新等场景。

核心属性
属性 作用 常用值
collection 需遍历的集合 / 数组名称 list（List）、array（数组）、map的key
item 遍历元素的别名 自定义变量名（如 item、user）
separator 元素之间的分隔符 ,、OR 等
open 拼接结果的开头字符 (
close 拼接结果的结尾字符 )
index 遍历索引（List 为索引，Map 为 key） 自定义变量名（如 index）
场景 1：IN 条件查询
<select id="findUsersByIds" resultType="User">
  SELECT * FROM users
  WHERE id IN
  <foreach collection="list" item="id" open="(" separator="," close=")">
    #{id}
  </foreach>
</select>
场景 2：批量插入
<insert id="batchInsertUsers">
  INSERT INTO users (username, age)
  VALUES
  <foreach collection="list" item="user" separator=",">
    (#{user.username}, #{user.age})
  </foreach>
</insert>
场景 3：批量更新（MySQL）
<update id="batchUpdateStatus">
  UPDATE users
  SET status = CASE id
  <foreach collection="map" item="status" index="id">
    WHEN #{id} THEN #{status}
  </foreach>
  END
  WHERE id IN
  <foreach collection="map.keys" item="id" open="(" separator="," close=")">
    #{id}
  </foreach>
</update>

7. 其他实用标签

sql 与 include：SQL 片段复用

sql 标签用于抽取重复的 SQL 片段（如查询字段、表名），include 标签用于引用，提升代码复用性。

<!-- 定义SQL片段 -->
<sql id="userColumns">id, username, age, status, create_time</sql>

<!-- 引用SQL片段 -->
<select id="findUserById" resultType="User">
  SELECT <include refid="userColumns"/> FROM users WHERE id = #{id}
</select>
bind：变量绑定

bind 标签可将 OGNL 表达式的结果绑定到变量，避免不同数据库的字符串拼接差异（如 MySQL 的 CONCAT 与 Oracle 的 ||）。

<select id="findUserByLikeName" resultType="User">
  <bind name="likeName" value="'%' + username + '%'"/>
  SELECT * FROM users WHERE username LIKE #{likeName}
</select>

动态 SQL 高级技巧

1. 内置参数的使用

MyBatis 提供两个内置参数，可直接在动态 SQL 中使用：

  • _parameter：代表整个参数（单个参数时为参数本身，多个参数时为封装的 Map）；
  • _databaseId：若配置了 databaseIdProvider，代表当前数据库的别名（用于多数据库适配）。
示例：使用 _parameter 简化多参数判断
<select id="findUser" resultType="User">
  SELECT * FROM users
  <if test="_parameter != null">
    <where>
      <if test="id != null">AND id = #{_parameter.id}</if>
      <if test="name != null">AND name = #{_parameter.name}</if>
    </where>
  </if>
</select>
示例：使用 _databaseId 适配多数据库
<select id="findAll" resultType="User">
  <if test="_databaseId == 'mysql'">
    SELECT * FROM users LIMIT 10
  </if>
  <if test="_databaseId == 'oracle'">
    SELECT * FROM (SELECT * FROM users) WHERE ROWNUM <= 10
  </if>
</select>

2. 多参数传递与动态 SQL

当方法有多个参数时，需通过 @Param 注解指定参数名，才能在动态 SQL 中正确引用：

// Mapper接口
List<User> findUsers(
  @Param("username") String username,
  @Param("minAge") Integer minAge,
  @Param("maxAge") Integer maxAge
);
<!-- 映射文件 -->
<select id="findUsers" resultType="User">
  SELECT * FROM users
  <where>
    <if test="username != null">AND username LIKE #{username}</if>
    <if test="minAge != null">AND age >= #{minAge}</if>
    <if test="maxAge != null">AND age <= #{maxAge}</if>
  </where>
</select>

3. 动态 SQL 与枚举类型

处理枚举类型时，可在 test 表达式中直接使用枚举的属性或方法：

// 枚举类
public enum UserStatus {
  ACTIVE(1, "活跃"), INACTIVE(0, "禁用");
  private int code;
  private String desc;
  // 构造方法、getter
}

// Mapper接口
List<User> findByStatus(@Param("status") UserStatus status);
<select id="findByStatus" resultType="User">
  SELECT * FROM users
  <if test="status != null">
    WHERE status = #{status.code}  <!-- 引用枚举的code属性 -->
  </if>
</select>

常见问题与解决方案

1. foreach 标签 collection 属性值错误

  • 问题：遍历集合时提示 Parameter 'list' not found；

  • 原因：未使用 @Param 注解时，MyBatis 对 List 参数默认封装为 list，数组为 array，但参数名可能被覆盖；

  • 解决方案：

    // 明确指定参数名
List<User> findByIds(@Param("ids") List<Long> ids);
<!-- collection 与 @Param 一致 -->
<foreach collection="ids" item="id" ...>

2. test 表达式中字符串判断问题

  • 问题：test="status == 'ACTIVE'" 不生效；

  • 原因：OGNL 中单引号包裹的是字符（char），字符串需用双引号或转义单引号；

  • 解决方案：

<if test='status == "ACTIVE"'>...</if>  <!-- 推荐：外单内双 -->
<if test="status == 'ACTIVE'.toString()">...</if>  <!-- 转为字符串比较 -->

3. 批量插入性能优化

  • 问题：foreach 批量插入大量数据时性能差；

  • 解决方案：

    1. 控制批次大小（如每 1000 条一批）；

    2. 开启批量执行模式（rewriteBatchedStatements=true）：

      jdbc.url=jdbc:mysql://localhost:3306/test?rewriteBatchedStatements=true

最佳实践总结

  1. 优先使用 where 和 set 标签：替代 trim 处理常见场景，简化代码；
  2. 避免 1=1 拼接：where 标签已完美解决条件为空问题；
  3. foreach 批量操作分批处理：防止 SQL 过长导致性能问题；
  4. 抽取 SQL 片段：通过 sql 和 include 复用重复代码；
  5. 多数据库适配：结合 _databaseId 和 choose 标签兼容不同数据库语法；
  6. 参数命名规范：多参数时必用 @Param 注解，提高可读性