动态 SQL

动态 SQL 是 MyBatis 的强大特性之一，可以根据条件动态生成 SQL 语句。本章将详细介绍各种动态 SQL 标签的使用方法。

概述

动态 SQL 解决了传统 JDBC 中需要手动拼接 SQL 字符串的问题：

if 标签

if 标签用于条件判断，根据条件决定是否包含某段 SQL。

基本用法

<select id="selectByCondition" resultType="User">
    SELECT * FROM user
    WHERE 1=1
    <if test="username != null and username != ''">
        AND username LIKE CONCAT('%', #{username}, '%')
    </if>
    <if test="email != null and email != ''">
        AND email = #{email}
    </if>
    <if test="status != null">
        AND status = #{status}
    </if>
</select>

属性说明

属性	说明
`test`	OGNL 表达式，判断条件

test 表达式

<!-- 判断不为空 -->
<if test="username != null">...</if>

<!-- 判断不为空字符串 -->
<if test="username != null and username != ''">...</if>

<!-- 判断数字 -->
<if test="status == 1">...</if>
<if test="status != 0">...</if>

<!-- 判断集合 -->
<if test="ids != null and ids.size() > 0">...</if>

<!-- 判断数组 -->
<if test="ids != null and ids.length > 0">...</if>

<!-- 调用方法 -->
<if test="username != null and username.length() > 3">...</if>

<!-- 使用 OGNL 方法 -->
<if test="@org.apache.commons.lang3.StringUtils@isNotEmpty(username)">...</if>

where 标签

where 标签可以自动处理 WHERE 子句，智能添加 WHERE 关键字并去除多余的 AND/OR。

基本用法

<select id="selectByCondition" resultType="User">
    SELECT * FROM user
    <where>
        <if test="username != null and username != ''">
            AND username LIKE CONCAT('%', #{username}, '%')
        </if>
        <if test="email != null and email != ''">
            AND email = #{email}
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
    </where>
</select>

工作原理

如果标签内有内容，自动添加 WHERE 关键字
自动去除开头的 AND 或 OR

<!-- 如果所有条件都不满足 -->
SELECT * FROM user

<!-- 如果只有 username 条件满足 -->
SELECT * FROM user WHERE username LIKE CONCAT('%', ?, '%')

<!-- 如果 username 和 email 条件都满足 -->
SELECT * FROM user WHERE username LIKE CONCAT('%', ?, '%') AND email = ?

等价写法

<select id="selectByCondition" resultType="User">
    SELECT * FROM user
    WHERE 
    <if test="username != null and username != ''">
        username LIKE CONCAT('%', #{username}, '%')
    </if>
    <if test="email != null and email != ''">
        <if test="username != null and username != ''">AND</if>
        email = #{email}
    </if>
</select>

set 标签

set 标签用于 UPDATE 语句，可以智能处理 SET 子句并去除多余的逗号。

基本用法

<update id="updateSelective">
    UPDATE user
    <set>
        <if test="username != null">username = #{username},</if>
        <if test="email != null">email = #{email},</if>
        <if test="phone != null">phone = #{phone},</if>
        <if test="status != null">status = #{status},</if>
    </set>
    WHERE id = #{id}
</update>

工作原理

如果标签内有内容，自动添加 SET 关键字
自动去除末尾多余的逗号

<!-- 如果所有字段都有值 -->
UPDATE user SET username = ?, email = ?, phone = ?, status = ? WHERE id = ?

<!-- 如果只有 username 和 email 有值 -->
UPDATE user SET username = ?, email = ? WHERE id = ?

trim 标签

trim 标签是 where 和 set 的通用版本，可以自定义前缀、后缀和覆盖规则。

基本用法

<!-- 等价于 where 标签 -->
<select id="selectByCondition" resultType="User">
    SELECT * FROM user
    <trim prefix="WHERE" prefixOverrides="AND |OR ">
        <if test="username != null">
            AND username = #{username}
        </if>
        <if test="email != null">
            AND email = #{email}
        </if>
    </trim>
</select>

<!-- 等价于 set 标签 -->
<update id="updateSelective">
    UPDATE user
    <trim prefix="SET" suffixOverrides=",">
        <if test="username != null">username = #{username},</if>
        <if test="email != null">email = #{email},</if>
        <if test="phone != null">phone = #{phone},</if>
    </trim>
    WHERE id = #{id}
</update>

属性说明

属性	说明
`prefix`	给内容添加前缀
`suffix`	给内容添加后缀
`prefixOverrides`	去除内容开头指定的字符串
`suffixOverrides`	去除内容末尾指定的字符串

自定义 trim

<!-- 插入时动态处理 -->
<insert id="insertSelective">
    INSERT INTO user
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="username != null">username,</if>
        <if test="password != null">password,</if>
        <if test="email != null">email,</if>
    </trim>
    <trim prefix="VALUES (" suffix=")" suffixOverrides=",">
        <if test="username != null">#{username},</if>
        <if test="password != null">#{password},</if>
        <if test="email != null">#{email},</if>
    </trim>
</insert>

choose/when/otherwise 标签

choose 类似于 Java 中的 switch 语句，只会执行第一个匹配的条件。

基本用法

<select id="selectByCondition" resultType="User">
    SELECT * FROM user
    <where>
        <choose>
            <when test="id != null">
                AND id = #{id}
            </when>
            <when test="username != null and username != ''">
                AND username = #{username}
            </when>
            <when test="email != null and email != ''">
                AND email = #{email}
            </when>
            <otherwise>
                AND status = 1
            </otherwise>
        </choose>
    </where>
</select>

工作原理

按顺序判断 when 条件
执行第一个满足条件的 when
如果都不满足，执行 otherwise
只会执行一个分支

应用场景

<!-- 搜索优先级：ID > 用户名 > 邮箱 > 默认查询 -->
<select id="searchUser" resultType="User">
    SELECT * FROM user
    WHERE status = 1
    <choose>
        <when test="id != null">
            AND id = #{id}
        </when>
        <when test="username != null">
            AND username = #{username}
        </when>
        <when test="email != null">
            AND email = #{email}
        </when>
    </choose>
</select>

foreach 标签

foreach 标签用于遍历集合或数组，常用于 IN 条件和批量操作。

基本用法

<!-- IN 查询 -->
<select id="selectByIds" resultType="User">
    SELECT * FROM user WHERE id IN
    <foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

<!-- 批量插入 -->
<insert id="insertBatch">
    INSERT INTO user (username, password, email) VALUES
    <foreach collection="list" item="user" separator=",">
        (#{user.username}, #{user.password}, #{user.email})
    </foreach>
</insert>

<!-- 批量更新 -->
<update id="updateBatch">
    <foreach collection="list" item="user" separator=";">
        UPDATE user SET username = #{user.username} WHERE id = #{user.id}
    </foreach>
</update>

属性说明

属性	说明
`collection`	集合参数名（List 默认 list，数组默认 array，Map 默认 map）
`item`	遍历时的元素变量名
`index`	索引变量名
`open`	开始字符串
`close`	结束字符串
`separator`	元素之间的分隔符

collection 属性

<!-- List 参数 -->
<select id="selectByIds" resultType="User">
    SELECT * FROM user WHERE id IN
    <foreach collection="list" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

<!-- 使用 @Param 指定名称 -->
<select id="selectByIds" resultType="User">
    SELECT * FROM user WHERE id IN
    <foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

<!-- 数组参数 -->
<select id="selectByIdArray" resultType="User">
    SELECT * FROM user WHERE id IN
    <foreach collection="array" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

<!-- Map 参数 -->
<select id="selectByMap" resultType="User">
    SELECT * FROM user WHERE id IN
    <foreach collection="idList" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

index 属性

<!-- 使用索引 -->
<insert id="insertBatch">
    INSERT INTO user (id, username) VALUES
    <foreach collection="list" item="user" index="index" separator=",">
        (#{index}, #{user.username})
    </foreach>
</insert>

<!-- Map 的 index 是 key -->
<select id="selectByMap" resultType="User">
    SELECT * FROM user WHERE
    <foreach collection="conditions" item="value" index="key" separator=" AND ">
        ${key} = #{value}
    </foreach>
</select>

批量删除示例

<delete id="deleteByIds">
    DELETE FROM user WHERE id IN
    <foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</delete>

批量更新示例

<!-- MySQL 批量更新 -->
<update id="updateBatch">
    UPDATE user SET
    username = CASE id
    <foreach collection="list" item="user">
        WHEN #{user.id} THEN #{user.username}
    </foreach>
    END
    WHERE id IN
    <foreach collection="list" item="user" open="(" separator="," close=")">
        #{user.id}
    </foreach>
</update>

bind 标签

bind 标签用于创建变量并绑定到上下文，常用于模糊查询。

基本用法

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

多数据库兼容

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

复杂表达式

<select id="selectByCondition" resultType="User">
    <bind name="usernamePattern" value="'%' + username + '%'" />
    <bind name="emailPattern" value="'%' + email + '%'" />
    SELECT * FROM user
    <where>
        <if test="username != null">
            AND username LIKE #{usernamePattern}
        </if>
        <if test="email != null">
            AND email LIKE #{emailPattern}
        </if>
    </where>
</select>

sql 和 include 标签

sql 标签定义可重用的 SQL 片段，include 标签引用该片段。

定义和引用

<!-- 定义 SQL 片段 -->
<sql id="Base_Column_List">
    id, username, password, email, phone, status, create_time, update_time
</sql>

<sql id="User_Where_Clause">
    <where>
        <if test="username != null and username != ''">
            AND username LIKE CONCAT('%', #{username}, '%')
        </if>
        <if test="email != null and email != ''">
            AND email = #{email}
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
    </where>
</sql>

<!-- 引用 SQL 片段 -->
<select id="selectByCondition" resultType="User">
    SELECT <include refid="Base_Column_List"/>
    FROM user
    <include refid="User_Where_Clause"/>
    ORDER BY id DESC
</select>

传递参数

<sql id="Table_Name">
    ${schema}.user
</sql>

<select id="selectById" resultType="User">
    SELECT * FROM <include refid="Table_Name">
        <property name="schema" value="mybatis_demo"/>
    </include>
    WHERE id = #{id}
</select>

实战示例

多条件查询

<select id="selectByCondition" resultType="User">
    SELECT * FROM user
    <where>
        <if test="username != null and username != ''">
            AND username LIKE CONCAT('%', #{username}, '%')
        </if>
        <if test="email != null and email != ''">
            AND email = #{email}
        </if>
        <if test="phone != null and phone != ''">
            AND phone = #{phone}
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
        <if test="createTimeStart != null">
            AND create_time &gt;= #{createTimeStart}
        </if>
        <if test="createTimeEnd != null">
            AND create_time &lt;= #{createTimeEnd}
        </if>
        <if test="ids != null and ids.size() > 0">
            AND id IN
            <foreach collection="ids" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
    </where>
    ORDER BY id DESC
</select>

动态排序

<select id="selectByCondition" resultType="User">
    SELECT * FROM user
    <where>
        <if test="username != null">
            AND username = #{username}
        </if>
    </where>
    <choose>
        <when test="orderBy != null and orderBy != ''">
            ORDER BY ${orderBy}
            <if test="orderDirection != null and orderDirection != ''">
                ${orderDirection}
            </if>
        </when>
        <otherwise>
            ORDER BY id DESC
        </otherwise>
    </choose>
</select>

分页查询

<select id="selectByPage" resultType="User">
    SELECT * FROM user
    <where>
        <if test="username != null and username != ''">
            AND username LIKE CONCAT('%', #{username}, '%')
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
    </where>
    ORDER BY id DESC
    LIMIT #{offset}, #{pageSize}
</select>

批量插入

<insert id="insertBatch" useGeneratedKeys="true" keyProperty="id">
    INSERT INTO user (username, password, email, phone, status)
    VALUES
    <foreach collection="list" item="user" separator=",">
        (#{user.username}, #{user.password}, #{user.email}, #{user.phone}, #{user.status})
    </foreach>
</insert>

动态表名

<select id="selectFromTable" resultType="User">
    SELECT * FROM ${tableName}
    <where>
        <if test="status != null">
            AND status = #{status}
        </if>
    </where>
</select>

动态列名

<select id="selectColumns" resultType="map">
    SELECT
    <foreach collection="columns" item="column" separator=",">
        ${column}
    </foreach>
    FROM user
    WHERE id = #{id}
</select>

注意事项

1. XML 转义字符

在 XML 中使用特殊字符需要转义：

字符	转义	说明
`<`	`<`	小于
`>`	`>`	大于
`&`	`&`	和号
`'`	`'`	单引号
`"`	`"`	双引号

<!-- 错误写法 -->
<if test="age < 18">...</if>

<!-- 正确写法 -->
<if test="age &lt; 18">...</if>

<!-- 或者使用 CDATA -->
<if test="age < 18"><![CDATA[ AND age < 18 ]]></if>

2. # vs $

<!-- 安全：预编译参数 -->
<if test="username != null">
    AND username = #{username}
</if>

<!-- 不安全：字符串替换，用于动态表名、列名 -->
<if test="tableName != null">
    FROM ${tableName}
</if>

3. 空值判断

<!-- 字符串判断 -->
<if test="username != null and username != ''">...</if>

<!-- 数字判断 -->
<if test="status != null">...</if>

<!-- 集合判断 -->
<if test="ids != null and ids.size() > 0">...</if>

<!-- 数组判断 -->
<if test="ids != null and ids.length > 0">...</if>

小结

本章详细介绍了 MyBatis 动态 SQL 的各种标签：

标签	用途
`if`	条件判断
`where`	智能处理 WHERE 子句
`set`	智能处理 SET 子句
`trim`	自定义前后缀处理
`choose/when/otherwise`	多条件选择
`foreach`	集合遍历
`bind`	变量绑定
`sql/include`	SQL 片段复用

下一章将介绍复杂的结果映射。

概述​

if 标签​

基本用法​

属性说明​

test 表达式​

where 标签​

基本用法​

工作原理​

等价写法​

set 标签​

基本用法​

工作原理​

trim 标签​

基本用法​

属性说明​

自定义 trim​

choose/when/otherwise 标签​

基本用法​

工作原理​

应用场景​

foreach 标签​

基本用法​

属性说明​

collection 属性​

index 属性​

批量删除示例​

批量更新示例​

bind 标签​

基本用法​

多数据库兼容​

复杂表达式​

sql 和 include 标签​

定义和引用​

传递参数​

实战示例​

多条件查询​

动态排序​

分页查询​

批量插入​

动态表名​

动态列名​

注意事项​

1. XML 转义字符​

2. # vs $​

3. 空值判断​

小结​

概述

if 标签

基本用法

属性说明

test 表达式

where 标签

基本用法

工作原理

等价写法

set 标签

基本用法

工作原理

trim 标签

基本用法

属性说明

自定义 trim

choose/when/otherwise 标签

基本用法

工作原理

应用场景

foreach 标签

基本用法

属性说明

collection 属性

index 属性

批量删除示例

批量更新示例

bind 标签

基本用法

多数据库兼容

复杂表达式

sql 和 include 标签

定义和引用

传递参数

实战示例

多条件查询

动态排序

分页查询

批量插入

动态表名

动态列名

注意事项

1. XML 转义字符

2. # vs $

3. 空值判断

小结