Web程序设计笔记12——第八章：动态SQL

MyBatis 动态 SQL 完整指南

概述

动态 SQL 是 MyBatis 最强大的特性之一，它允许我们根据不同的条件动态生成 SQL 语句。通过动态 SQL，我们可以避免手动拼接 SQL 字符串的痛苦，同时让 SQL 语句更加灵活和强大。

💡 核心价值：

• 灵活性：根据条件动态生成 SQL
• 可维护性：避免字符串拼接，代码更清晰
• 安全性：防止 SQL 注入攻击
• 复用性：相同的 SQL 模板适用于多种场景

动态SQL简介

什么是动态 SQL

动态 SQL 是指根据不同的条件动态地拼接 SQL 语句。MyBatis 提供了以下动态 SQL 标签：

环境准备

数据库准备

-- 创建数据库
CREATE DATABASE IF NOT EXISTS mybatis_dynamic CHARACTER SET utf8mb4;

USE mybatis_dynamic;

-- 创建客户表
CREATE TABLE t_customer (
    id INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    jobs VARCHAR(50),
    phone VARCHAR(20),
    email VARCHAR(50),
    status INT DEFAULT 1,
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    update_time DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 插入测试数据
INSERT INTO t_customer (username, jobs, phone, email) VALUES 
('张三', 'engineer', '13800138001', 'zhangsan@email.com'),
('李四', 'teacher', '13800138002', 'lisi@email.com'),
('王五', 'doctor', '13800138003', 'wangwu@email.com'),
('赵六', 'engineer', '13800138004', 'zhaoliu@email.com'),
('钱七', null, '13800138005', 'qianqi@email.com');

实体类准备

public class Customer {
    private Integer id;
    private String username;
    private String jobs;
    private String phone;
    private String email;
    private Integer status;
    private Date createTime;
    private Date updateTime;
    
    // 构造方法、getter/setter、toString 省略
}

核心元素详解

1. if 元素

<if> 元素是最常用的动态 SQL 标签，用于条件判断。

基本语法

<if test="condition">
    SQL fragment
</if>

条件表达式支持

实际示例

<!-- 多条件查询客户 -->
<select id="findCustomerByConditions" parameterType="Customer" resultType="Customer">
    SELECT * FROM t_customer WHERE 1=1
    <if test="username != null and username != ''">
        AND username LIKE CONCAT('%', #{username}, '%')
    </if>
    <if test="jobs != null and jobs != ''">
        AND jobs = #{jobs}
    </if>
    <if test="phone != null and phone != ''">
        AND phone = #{phone}
    </if>
    <if test="email != null and email != ''">
        AND email LIKE CONCAT('%', #{email}, '%')
    </if>
    <if test="status != null">
        AND status = #{status}
    </if>
</select>

<!-- 更复杂的条件判断 -->
<select id="findActiveCustomers" resultType="Customer">
    SELECT * FROM t_customer
    WHERE status = 1
    <if test="minCreateTime != null">
        AND create_time >= #{minCreateTime}
    </if>
    <if test="maxCreateTime != null">
        AND create_time <= #{maxCreateTime}
    </if>
    <if test="jobsList != null and jobsList.size() > 0">
        AND jobs IN
        <foreach collection="jobsList" item="job" open="(" separator="," close=")">
            #{job}
        </foreach>
    </if>
</select>

测试代码

@Test
public void testIfElement() {
    try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
        CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
        
        // 测试1：仅按用户名查询
        Customer param1 = new Customer();
        param1.setUsername("张");
        List<Customer> result1 = mapper.findCustomerByConditions(param1);
        System.out.println("按用户名查询结果：" + result1.size() + "条");
        
        // 测试2：多条件组合查询
        Customer param2 = new Customer();
        param2.setJobs("engineer");
        param2.setStatus(1);
        List<Customer> result2 = mapper.findCustomerByConditions(param2);
        System.out.println("多条件查询结果：" + result2.size() + "条");
        
        // 测试3：无条件查询（查询所有）
        Customer param3 = new Customer();
        List<Customer> result3 = mapper.findCustomerByConditions(param3);
        System.out.println("查询所有结果：" + result3.size() + "条");
    }
}

2. choose-when-otherwise 元素

<choose> 元素类似于 Java 的 switch 语句，它只会选择满足条件的第一个分支。

基本语法

<choose>
    <when test="condition1">
        SQL fragment 1
    </when>
    <when test="condition2">
        SQL fragment 2
    </when>
    <otherwise>
        SQL fragment 3
    </otherwise>
</choose>

实际示例

<!-- 单一条件优先级查询 -->
<select id="findCustomerByPriority" parameterType="Customer" resultType="Customer">
    SELECT * FROM t_customer WHERE 1=1
    <choose>
        <when test="id != null">
            AND id = #{id}
        </when>
        <when test="username != null and username != ''">
            AND username LIKE CONCAT('%', #{username}, '%')
        </when>
        <when test="jobs != null and jobs != ''">
            AND jobs = #{jobs}
        </when>
        <when test="phone != null and phone != ''">
            AND phone = #{phone}
        </when>
        <otherwise>
            AND status = 1
        </otherwise>
    </choose>
</select>

<!-- 排序条件选择 -->
<select id="findCustomersWithOrder" resultType="Customer">
    SELECT * FROM t_customer
    WHERE status = 1
    ORDER BY
    <choose>
        <when test="orderBy == 'name'">
            username
        </when>
        <when test="orderBy == 'time'">
            create_time
        </when>
        <when test="orderBy == 'job'">
            jobs
        </when>
        <otherwise>
            id
        </otherwise>
    </choose>
    <if test="descOrder == true">
        DESC
    </if>
</select>

<!-- 分页查询的不同实现 -->
<select id="findCustomersWithPaging" resultType="Customer">
    SELECT * FROM t_customer
    <choose>
        <when test="database == 'mysql'">
            LIMIT #{offset}, #{limit}
        </when>
        <when test="database == 'oracle'">
            WHERE ROWNUM <= #{limit}
        </when>
        <when test="database == 'sqlserver'">
            OFFSET #{offset} ROWS FETCH NEXT #{limit} ROWS ONLY
        </when>
    </choose>
</select>

测试代码

@Test
public void testChooseElement() {
    try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
        CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
        
        // 测试1：ID优先级最高
        Customer param1 = new Customer();
        param1.setId(1);
        param1.setUsername("test"); // 即使设置了username，也会优先使用id
        List<Customer> result1 = mapper.findCustomerByPriority(param1);
        System.out.println("ID查询结果：" + result1.size());
        
        // 测试2：只设置jobs
        Customer param2 = new Customer();
        param2.setJobs("engineer");
        List<Customer> result2 = mapper.findCustomerByPriority(param2);
        System.out.println("职业查询结果：" + result2.size());
        
        // 测试3：otherwise分支
        Customer param3 = new Customer();
        List<Customer> result3 = mapper.findCustomerByPriority(param3);
        System.out.println("默认查询结果：" + result3.size());
    }
}

3. where 元素

<where> 元素会智能地处理 WHERE 关键字和 AND/OR 前缀。

功能特点

• 自动添加 WHERE 关键字
• 自动去除多余的 AND 或 OR 前缀
• 如果没有条件，不会添加 WHERE

实际示例

<!-- 基本的 where 使用 -->
<select id="findCustomersDynamic" resultType="Customer">
    SELECT * FROM t_customer
    <where>
        <if test="username != null and username != ''">
            AND username LIKE CONCAT('%', #{username}, '%')
        </if>
        <if test="jobs != null and jobs != ''">
            AND jobs = #{jobs}
        </if>
        <if test="phone != null and phone != ''">
            AND phone = #{phone}
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
    </where>
</select>

<!-- 复杂的 where 条件组合 -->
<select id="findCustomersComplex" resultType="Customer">
    SELECT * FROM t_customer
    <where>
        <if test="searchType == 'basic'">
            <if test="keyword != null and keyword != ''">
                AND (username LIKE CONCAT('%', #{keyword}, '%')
                    OR jobs LIKE CONCAT('%', #{keyword}, '%')
                    OR email LIKE CONCAT('%', #{keyword}, '%'))
            </if>
        </if>
        <if test="searchType == 'advanced'">
            <if test="username != null and username != ''">
                AND username = #{username}
            </if>
            <if test="startDate != null">
                AND create_time >= #{startDate}
            </if>
            <if test="endDate != null">
                AND create_time <= #{endDate}
            </if>
        </if>
        <if test="excludeIds != null and excludeIds.size() > 0">
            AND id NOT IN
            <foreach collection="excludeIds" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
    </where>
    ORDER BY create_time DESC
</select>

<!-- where 与choose 结合使用 -->
<select id="findCustomersMixed" resultType="Customer">
    SELECT * FROM t_customer
    <where>
        <choose>
            <when test="searchMode == 'id'">
                id = #{searchValue}
            </when>
            <when test="searchMode == 'name'">
                username LIKE CONCAT('%', #{searchValue}, '%')
            </when>
            <when test="searchMode == 'phone'">
                phone = #{searchValue}
            </when>
        </choose>
        <if test="activeOnly == true">
            AND status = 1
        </if>
    </where>
</select>

where 元素的等价写法

<!-- 使用 where 元素 -->
<select id="method1" resultType="Customer">
    SELECT * FROM t_customer
    <where>
        <if test="name != null">
            AND name = #{name}
        </if>
    </where>
</select>

<!-- 等价于使用 trim -->
<select id="method2" resultType="Customer">
    SELECT * FROM t_customer
    <trim prefix="WHERE" prefixOverrides="AND |OR ">
        <if test="name != null">
            AND name = #{name}
        </if>
    </trim>
</select>

4. set 元素

<set> 元素用于动态更新语句，会自动去除多余的逗号。

功能特点

• 自动添加 SET 关键字
• 自动去除最后一个逗号
• 至少要有一个条件满足，否则会报错

实际示例

<!-- 基本的动态更新 -->
<update id="updateCustomerDynamic" parameterType="Customer">
    UPDATE t_customer
    <set>
        <if test="username != null and username != ''">
            username = #{username},
        </if>
        <if test="jobs != null and jobs != ''">
            jobs = #{jobs},
        </if>
        <if test="phone != null and phone != ''">
            phone = #{phone},
        </if>
        <if test="email != null and email != ''">
            email = #{email},
        </if>
        <if test="status != null">
            status = #{status},
        </if>
        update_time = NOW()
    </set>
    WHERE id = #{id}
</update>

<!-- 条件更新 -->
<update id="updateCustomerByCondition">
    UPDATE t_customer
    <set>
        <if test="newStatus != null">
            status = #{newStatus},
        </if>
        <if test="newJobs != null and newJobs != ''">
            jobs = #{newJobs},
        </if>
        <choose>
            <when test="operationType == 'activate'">
                status = 1,
            </when>
            <when test="operationType == 'deactivate'">
                status = 0,
            </when>
        </choose>
        update_time = NOW()
    </set>
    <where>
        <if test="oldStatus != null">
            AND status = #{oldStatus}
        </if>
        <if test="jobs != null and jobs != ''">
            AND jobs = #{jobs}
        </if>
    </where>
</update>

<!-- 批量更新使用 foreach -->
<update id="batchUpdateStatus">
    <foreach collection="list" item="item" separator=";">
        UPDATE t_customer
        <set>
            status = #{item.status},
            update_time = NOW()
        </set>
        WHERE id = #{item.id}
    </foreach>
</update>

set 元素的等价写法

<!-- 使用 set 元素 -->
<update id="method1">
    UPDATE t_customer
    <set>
        <if test="name != null">
            name = #{name},
        </if>
        <if test="email != null">
            email = #{email},
        </if>
    </set>
    WHERE id = #{id}
</update>

<!-- 等价于使用 trim -->
<update id="method2">
    UPDATE t_customer
    <trim prefix="SET" suffixOverrides=",">
        <if test="name != null">
            name = #{name},
        </if>
        <if test="email != null">
            email = #{email},
        </if>
    </trim>
    WHERE id = #{id}
</update>

测试代码

@Test
public void testSetElement() {
    try (SqlSession sqlSession = sqlSessionFactory.openSession()) {
        CustomerMapper mapper = sqlSession.getMapper(CustomerMapper.class);
        
        // 测试1：部分字段更新
        Customer customer1 = new Customer();
        customer1.setId(1);
        customer1.setUsername("张三更新");
        customer1.setEmail("zhangsan_new@email.com");
        int result1 = mapper.updateCustomerDynamic(customer1);
        
        // 测试2：条件更新
        Map<String, Object> params = new HashMap<>();
        params.put("newStatus", 0);
        params.put("oldStatus", 1);
        params.put("jobs", "engineer");
        int result2 = mapper.updateCustomerByCondition(params);
        
        sqlSession.commit();
        System.out.println("更新记录数：" + result1 + ", " + result2);
    }
}

高级动态SQL

5. foreach 元素

<foreach> 元素用于遍历集合，常用于 IN 查询和批量操作。

属性说明

实际示例

<!-- IN 查询 -->
<select id="findCustomersByIds" resultType="Customer">
    SELECT * FROM t_customer
    WHERE id IN
    <foreach collection="list" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

<!-- 批量插入 -->
<insert id="batchInsertCustomers" parameterType="list">
    INSERT INTO t_customer (username, jobs, phone, email) VALUES
    <foreach collection="list" item="customer" separator=",">
        (#{customer.username}, #{customer.jobs}, #{customer.phone}, #{customer.email})
    </foreach>
</insert>

<!-- 复杂的 foreach 使用 -->
<select id="findCustomersByConditions" resultType="Customer">
    SELECT * FROM t_customer
    <where>
        <if test="jobsList != null and jobsList.size() > 0">
            AND jobs IN
            <foreach collection="jobsList" item="job" open="(" separator="," close=")">
                #{job}
            </foreach>
        </if>
        <if test="statusList != null and statusList.size() > 0">
            AND status IN
            <foreach collection="statusList" item="status" open="(" separator="," close=")">
                #{status}
            </foreach>
        </if>
    </where>
</select>

<!-- Map 遍历 -->
<select id="findByMap" resultType="Customer">
    SELECT * FROM t_customer
    <where>
        <foreach collection="conditions" index="key" item="value" separator=" AND ">
            ${key} = #{value}
        </foreach>
    </where>
</select>

<!-- 批量更新（MySQL） -->
<update id="batchUpdateCustomers">
    UPDATE t_customer
    <trim prefix="SET" suffixOverrides=",">
        <trim prefix="username = CASE" suffix="END,">
            <foreach collection="list" item="item">
                WHEN id = #{item.id} THEN #{item.username}
            </foreach>
        </trim>
        <trim prefix="jobs = CASE" suffix="END,">
            <foreach collection="list" item="item">
                WHEN id = #{item.id} THEN #{item.jobs}
            </foreach>
        </trim>
    </trim>
    WHERE id IN
    <foreach collection="list" item="item" open="(" separator="," close=")">
        #{item.id}
    </foreach>
</update>

6. trim 元素

<trim> 元素是一个更灵活的元素，可以实现 where、set 等元素的功能。

属性说明

实际示例

<!-- 使用 trim 实现 where 功能 -->
<select id="findWithTrim" resultType="Customer">
    SELECT * FROM t_customer
    <trim prefix="WHERE" prefixOverrides="AND |OR ">
        <if test="username != null">
            AND username = #{username}
        </if>
        <if test="jobs != null">
            OR jobs = #{jobs}
        </if>
    </trim>
</select>

<!-- 使用 trim 实现 set 功能 -->
<update id="updateWithTrim">
    UPDATE t_customer
    <trim prefix="SET" suffixOverrides=",">
        <if test="username != null">
            username = #{username},
        </if>
        <if test="jobs != null">
            jobs = #{jobs},
        </if>
    </trim>
    WHERE id = #{id}
</update>

<!-- 复杂的 trim 使用 -->
<insert id="insertWithTrim">
    INSERT INTO t_customer
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="username != null">
            username,
        </if>
        <if test="jobs != null">
            jobs,
        </if>
        <if test="phone != null">
            phone,
        </if>
    </trim>
    VALUES
    <trim prefix="(" suffix=")" suffixOverrides=",">
        <if test="username != null">
            #{username},
        </if>
        <if test="jobs != null">
            #{jobs},
        </if>
        <if test="phone != null">
            #{phone},
        </if>
    </trim>
</insert>

7. bind 元素

<bind> 元素可以创建一个变量并绑定到上下文中。

实际示例

<!-- 使用 bind 处理模糊查询 -->
<select id="findByNamePattern" resultType="Customer">
    <bind name="pattern" value="'%' + _parameter.username + '%'" />
    SELECT * FROM t_customer
    WHERE username LIKE #{pattern}
</select>

<!-- 多个 bind 变量 -->
<select id="findByMultipleBind" resultType="Customer">
    <bind name="userPattern" value="'%' + username + '%'" />
    <bind name="emailPattern" value="'%' + email + '%'" />
    <bind name="upperJobs" value="jobs.toUpperCase()" />
    
    SELECT * FROM t_customer
    WHERE username LIKE #{userPattern}
       OR email LIKE #{emailPattern}
       OR UPPER(jobs) = #{upperJobs}
</select>

<!-- bind 与 OGNL 表达式 -->
<select id="findWithOgnl" resultType="Customer">
    <bind name="isValidPhone" value="phone != null and phone.length() == 11" />
    <bind name="isEngineer" value="'engineer'.equals(jobs)" />
    
    SELECT * FROM t_customer
    <where>
        <if test="isValidPhone">
            AND phone = #{phone}
        </if>
        <if test="isEngineer">
            AND jobs = 'engineer'
        </if>
    </where>
</select>

实战案例

复杂查询系统

<!-- 综合查询示例 -->
<select id="complexSearch" resultType="Customer">
    <bind name="hasKeyword" value="keyword != null and keyword != ''" />
    <bind name="keywordPattern" value="'%' + keyword + '%'" />
    
    SELECT DISTINCT c.* FROM t_customer c
    <if test="joinOrders">
        LEFT JOIN t_order o ON c.id = o.customer_id
    </if>
    <where>
        <!-- 关键词搜索 -->
        <if test="hasKeyword">
            AND (
                c.username LIKE #{keywordPattern}
                OR c.email LIKE #{keywordPattern}
                OR c.phone LIKE #{keyword}
            )
        </if>
        
        <!-- 精确条件 -->
        <if test="exactMatch != null">
            <foreach collection="exactMatch" index="field" item="value">
                AND c.${field} = #{value}
            </foreach>
        </if>
        
        <!-- 范围查询 -->
        <if test="dateRange != null">
            <if test="dateRange.start != null">
                AND c.create_time >= #{dateRange.start}
            </if>
            <if test="dateRange.end != null">
                AND c.create_time <= #{dateRange.end}
            </if>
        </if>
        
        <!-- 状态筛选 -->
        <choose>
            <when test="statusFilter == 'active'">
                AND c.status = 1
            </when>
            <when test="statusFilter == 'inactive'">
                AND c.status = 0
            </when>
            <when test="statusFilter == 'all'">
                <!-- 显示所有状态 -->
            </when>
            <otherwise>
                AND c.status >= 0
            </otherwise>
        </choose>
        
        <!-- 订单相关条件 -->
        <if test="joinOrders and orderConditions != null">
            <if test="orderConditions.minAmount != null">
                AND o.amount >= #{orderConditions.minAmount}
            </if>
            <if test="orderConditions.maxAmount != null">
                AND o.amount <= #{orderConditions.maxAmount}
            </if>
        </if>
    </where>
    
    <!-- 排序 -->
    <if test="orderBy != null and orderBy.size() > 0">
        ORDER BY
        <foreach collection="orderBy" item="order" separator=",">
            c.${order.field} ${order.direction}
        </foreach>
    </if>
    
    <!-- 分页 -->
    <if test="pagination != null">
        LIMIT #{pagination.offset}, #{pagination.limit}
    </if>
</select>

动态表名和列名

<!-- 动态表名（注意安全性） -->
<select id="selectFromDynamicTable" resultType="map">
    SELECT 
    <choose>
        <when test="columns != null and columns.size() > 0">
            <foreach collection="columns" item="col" separator=",">
                ${col}
            </foreach>
        </when>
        <otherwise>*</otherwise>
    </choose>
    FROM ${tableName}
    <where>
        <if test="conditions != null">
            <foreach collection="conditions" index="key" item="value" separator=" AND ">
                ${key} = #{value}
            </foreach>
        </if>
    </where>
</select>

<!-- 安全的动态表名处理 -->
<select id="selectFromTableSafe" resultType="map">
    SELECT * FROM
    <choose>
        <when test="tableType == 'customer'">t_customer</when>
        <when test="tableType == 'order'">t_order</when>
        <when test="tableType == 'product'">t_product</when>
        <otherwise>t_customer</otherwise>
    </choose>
    WHERE id = #{id}
</select>

性能优化

1. 缓存动态 SQL 结果

<!-- 开启二级缓存 -->
<cache eviction="LRU" flushInterval="60000" size="512" readOnly="true"/>

<!-- 可缓存的动态查询 -->
<select id="cachedDynamicQuery" resultType="Customer" useCache="true">
    SELECT * FROM t_customer
    <where>
        <if test="status != null">
            AND status = #{status}
        </if>
    </where>
</select>

2. 避免过度复杂的动态 SQL

<!-- 不好的做法：过度嵌套 -->
<select id="badExample" resultType="Customer">
    SELECT * FROM t_customer
    <where>
        <if test="condition1">
            <if test="condition2">
                <if test="condition3">
                    <!-- 过度嵌套，难以维护 -->
                </if>
            </if>
        </if>
    </where>
</select>

<!-- 好的做法：使用 SQL 片段 -->
<sql id="baseConditions">
    <if test="username != null">
        AND username = #{username}
    </if>
    <if test="status != null">
        AND status = #{status}
    </if>
</sql>

<select id="goodExample" resultType="Customer">
    SELECT * FROM t_customer
    <where>
        <include refid="baseConditions"/>
        <if test="additionalCondition">
            AND additional_field = #{additionalValue}
        </if>
    </where>
</select>

3. 使用 SQL 片段复用

<!-- 定义可复用的 SQL 片段 -->
<sql id="customerColumns">
    c.id, c.username, c.jobs, c.phone, c.email, c.status
</sql>

<sql id="customerJoins">
    <if test="includeOrders">
        LEFT JOIN t_order o ON c.id = o.customer_id
    </if>
    <if test="includeAddress">
        LEFT JOIN t_address a ON c.id = a.customer_id
    </if>
</sql>

<sql id="customerConditions">
    <if test="activeOnly">
        AND c.status = 1
    </if>
    <if test="keyword != null">
        AND (c.username LIKE CONCAT('%', #{keyword}, '%')
             OR c.email LIKE CONCAT('%', #{keyword}, '%'))
    </if>
</sql>

<!-- 使用 SQL 片段 -->
<select id="findCustomersWithDetails" resultType="Customer">
    SELECT 
        <include refid="customerColumns"/>
        <if test="includeOrders">
            , COUNT(o.id) as orderCount
        </if>
    FROM t_customer c
    <include refid="customerJoins"/>
    <where>
        <include refid="customerConditions"/>
    </where>
    <if test="includeOrders">
        GROUP BY <include refid="customerColumns"/>
    </if>
</select>

最佳实践

1. 编写可读性高的动态 SQL

<!-- 使用清晰的缩进和注释 -->
<select id="findCustomersReadable" resultType="Customer">
    SELECT * FROM t_customer c
    <where>
        <!-- 基本信息筛选 -->
        <if test="basicInfo != null">
            <if test="basicInfo.username != null">
                AND c.username LIKE CONCAT('%', #{basicInfo.username}, '%')
            </if>
            <if test="basicInfo.email != null">
                AND c.email = #{basicInfo.email}
            </if>
        </if>
        
        <!-- 状态筛选 -->
        <if test="statusList != null and statusList.size() > 0">
            AND c.status IN
            <foreach collection="statusList" item="status" 
                     open="(" separator="," close=")">
                #{status}
            </foreach>
        </if>
        
        <!-- 时间范围 -->
        <if test="timeRange != null">
            <if test="timeRange.startTime != null">
                AND c.create_time >= #{timeRange.startTime}
            </if>
            <if test="timeRange.endTime != null">
                AND c.create_time <= #{timeRange.endTime}
            </if>
        </if>
    </where>
</select>

2. 安全性注意事项

<!-- 错误：容易 SQL 注入 -->
<select id="unsafeQuery" resultType="Customer">
    SELECT * FROM t_customer WHERE username = '${username}'
</select>

<!-- 正确：使用参数化查询 -->
<select id="safeQuery" resultType="Customer">
    SELECT * FROM t_customer WHERE username = #{username}
</select>

<!-- 动态表名/列名的安全处理 -->
<select id="safeDynamicTable" resultType="map">
    SELECT * FROM
    <choose>
        <when test="tableName == 'customer'">t_customer</when>
        <when test="tableName == 'order'">t_order</when>
        <otherwise>t_customer</otherwise>
    </choose>
    WHERE id = #{id}
</select>

3. 性能最佳实践

<!-- 使用 include 避免重复 -->
<sql id="selectCustomerBase">
    SELECT id, username, jobs, phone, email, status, create_time
    FROM t_customer
</sql>

<!-- 限制返回结果集 -->
<select id="findCustomersWithLimit" resultType="Customer">
    <include refid="selectCustomerBase"/>
    <where>
        <if test="condition != null">
            AND status = #{condition}
        </if>
    </where>
    LIMIT #{limit}
</select>

<!-- 使用索引字段查询 -->
<select id="findByIndexedFields" resultType="Customer">
    SELECT * FROM t_customer
    <where>
        <!-- 优先使用索引字段 -->
        <if test="id != null">
            AND id = #{id}
        </if>
        <if test="phone != null">
            AND phone = #{phone}
        </if>
        <!-- 非索引字段放在后面 -->
        <if test="username != null">
            AND username LIKE CONCAT('%', #{username}, '%')
        </if>
    </where>
</select>

4. 维护性建议

<!-- 使用命名规范 -->
<sql id="whereCustomerActive">
    status = 1
</sql>

<sql id="whereCustomerByJobs">
    <if test="jobs != null and jobs.size() > 0">
        AND jobs IN
        <foreach collection="jobs" item="job" open="(" separator="," close=")">
            #{job}
        </foreach>
    </if>
</sql>

<!-- 模块化组织 -->
<select id="findActiveCustomersByJobs" resultType="Customer">
    SELECT * FROM t_customer
    <where>
        <include refid="whereCustomerActive"/>
        <include refid="whereCustomerByJobs"/>
    </where>
</select>

常见问题

问题 1：动态 SQL 中的空格问题

问题描述：SQL 语句拼接时缺少空格

<!-- 错误示例 -->
<select id="wrongSpacing" resultType="Customer">
    SELECT * FROM t_customer
    <if test="name != null">
        WHERE username = #{name}  <!-- 缺少前缀空格 -->
    </if>
    <if test="orderBy != null">
        ORDER BY create_time      <!-- 缺少前缀空格 -->
    </if>
</select>

<!-- 正确做法 -->
<select id="correctSpacing" resultType="Customer">
    SELECT * FROM t_customer
    <where>
        <if test="name != null">
            AND username = #{name}
        </if>
    </where>
    <if test="orderBy != null">
        ORDER BY create_time
    </if>
</select>

问题 2：集合判断错误

问题描述：集合为空时导致 SQL 错误

<!-- 错误示例 -->
<select id="wrongCollection" resultType="Customer">
    SELECT * FROM t_customer
    WHERE id IN
    <foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
    <!-- 如果 ids 为空，会生成 WHERE id IN () -->
</select>

<!-- 正确做法 -->
<select id="correctCollection" resultType="Customer">
    SELECT * FROM t_customer
    <where>
        <if test="ids != null and ids.size() > 0">
            AND id IN
            <foreach collection="ids" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
    </where>
</select>

问题 3：OGNL 表达式错误

问题描述：test 条件中的表达式错误

<!-- 常见错误 -->
<if test="type == 'student'">  <!-- 字符串应使用单引号 -->
<if test="list.size > 0">      <!-- 应该是 list.size() -->
<if test="name == null || name == ''">  <!-- 字符串判断 -->

<!-- 正确的 OGNL 表达式 -->
<if test='type == "student"'>  <!-- 或者交换引号 -->
<if test="list.size() > 0">
<if test="name == null or name == ''">

问题 4：性能问题

问题描述：复杂动态 SQL 导致性能下降

<!-- 优化前 -->
<select id="beforeOptimization" resultType="Customer">
    SELECT * FROM t_customer c
    <if test="includeOrders">
        LEFT JOIN t_order o ON c.id = o.customer_id
        LEFT JOIN t_order_item oi ON o.id = oi.order_id
        LEFT JOIN t_product p ON oi.product_id = p.id
    </if>
    <!-- 多个JOIN 可能导致性能问题 -->
</select>

<!-- 优化后：分步查询 -->
<select id="afterOptimization" resultMap="customerWithOrders">
    SELECT * FROM t_customer
    <where>
        <if test="customerId != null">
            AND id = #{customerId}
        </if>
    </where>
</select>

<select id="selectOrdersByCustomerId" resultType="Order">
    SELECT * FROM t_order WHERE customer_id = #{customerId}
</select>

总结

动态 SQL 是 MyBatis 的核心特性之一，它让我们能够：

🎯 核心价值

1. 灵活性：根据不同条件动态生成 SQL
2. 可维护性：代码结构清晰，易于理解和修改
3. 安全性：通过参数化查询防止 SQL 注入
4. 复用性：通过 SQL 片段复用，减少重复代码

📊 使用建议

• 合理选择：根据场景选择合适的动态 SQL 元素
• 性能考虑：避免过度复杂的动态 SQL
• 安全优先：始终使用参数化查询
• 代码规范：保持代码清晰和良好的可读性

相关文章

前后章节导航

• 上一章：Web程序设计笔记11——第七章：MyBatis核心配置
• 下一章：Web程序设计笔记13——第九章：MyBatis的关系映射
• 相关指南：MyBatis 动态SQL完整指南

Web程序设计系列教程

• Web程序设计笔记10——第六章：初识MyBatis - MyBatis基础入门
• Web程序设计笔记11——第七章：MyBatis核心配置 - 核心配置详解
• Web程序设计笔记08——第四章：Spring的数据库开发 - Spring数据库基础
• Web程序设计笔记13——第九章：MyBatis 的关系映射 - 关系映射进阶
• Web程序设计笔记14——第十章：Spring 和 MyBatis 的整合 - 框架整合应用

动态SQL深入学习

• Web程序设计笔记13——第九章：MyBatis 的关系映射 - 结合动态SQL的复杂映射
• Web程序设计笔记17——第十三章：数据绑定 - 动态参数绑定

MyBatis高级特性

• Web程序设计笔记13——第九章：MyBatis 的关系映射 - 一对一、一对多、多对多映射
• Web程序设计笔记11——第七章：MyBatis核心配置 - ResultMap与TypeAlias配置

Spring MVC集成

• Web程序设计笔记15——第十一章：Spring MVC - MVC框架基础
• Web程序设计笔记16——第十二章：Spring MVC 的核心类和注解 - 核心注解详解
• Web程序设计笔记18——第十四章：JSON 数据和 RESTful 风格的 url - RESTful API开发

SpringBoot 集成

• SpringBoot 整合 MyBatis-Plus 和 Druid - 现代化MyBatis配置

数据库操作优化

• Web程序设计笔记08——第四章：Spring的数据库开发 - Spring事务管理
• Spring 事务管理完整指南 - 事务控制详解

常见问题解决

• Mybaits连接MySQL80版本的配置 - 数据库连接配置
• 关于jdbc连接mysql URL上的常见问题 - 连接参数问题

动态SQL应用场景

• 条件查询：if元素实现多条件组合查询
• 分支选择：choose-when-otherwise实现多分支逻辑
• 动态更新：set元素实现选择性字段更新
• 动态WHERE：where元素自动管理AND/OR条件
• 批量操作：foreach元素实现批量增删改查
• 灵活截取：trim元素自定义SQL片段处理