Spring Framework Learning

Appearance

动态SQL ​

什么是动态SQL？ ​

动态SQL是MyBatis的强大特性之一，它允许我们根据条件动态生成SQL语句，避免了大量的字符串拼接和条件判断。

常用标签 ​

1. if - 条件判断 ​

根据条件决定是否包含某段SQL。

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

2. where - 智能WHERE子句 ​

自动处理WHERE关键字和第一个AND/OR。

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

3. set - 智能SET子句 ​

用于UPDATE语句，自动处理逗号。

xml
<update id="updateSelective">
    UPDATE user
    <set>
        <if test="username != null">username = #{username},</if>
        <if test="email != null">email = #{email},</if>
        <if test="status != null">status = #{status},</if>
        update_time = NOW()
    </set>
    WHERE id = #{id}
</update>

4. choose, when, otherwise - 多条件选择 ​

类似Java的switch语句。

xml
<select id="selectByCondition" resultType="User">
    SELECT * FROM user
    <where>
        <choose>
            <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>

5. trim - 自定义字符串截取 ​

更灵活的字符串处理。

xml
<!-- 等同于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="status != null">status = #{status},</if>
    </trim>
    WHERE id = #{id}
</update>

6. foreach - 遍历集合 ​

用于IN查询、批量插入等场景。

IN查询 ​

java
List<User> selectByIds(@Param("ids") List<Long> ids);
xml
<select id="selectByIds" resultType="User">
    SELECT * FROM user
    WHERE id IN
    <foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

批量插入 ​

java
int insertBatch(@Param("users") List<User> users);
xml
<insert id="insertBatch">
    INSERT INTO user (username, email, create_time)
    VALUES
    <foreach collection="users" item="user" separator=",">
        (#{user.username}, #{user.email}, NOW())
    </foreach>
</insert>

批量更新 ​

xml
<update id="updateBatch">
    <foreach collection="users" item="user" separator=";">
        UPDATE user
        SET username = #{user.username},
            email = #{user.email}
        WHERE id = #{user.id}
    </foreach>
</update>

7. bind - 创建变量 ​

在SQL中创建变量。

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

8. sql和include - SQL片段复用 ​

定义可复用的SQL片段。

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

<sql id="baseWhere">
    <where>
        <if test="username != null and username != ''">
            AND username = #{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="userColumns"/>
    FROM user
    <include refid="baseWhere"/>
</select>

<select id="selectCount" resultType="int">
    SELECT COUNT(*)
    FROM user
    <include refid="baseWhere"/>
</select>

实战示例 ​

1. 高级搜索功能 ​

java
@Data
public class UserQuery {
    private String keyword;        // 关键词搜索
    private String username;       // 精确用户名
    private String email;          // 精确邮箱
    private Integer status;        // 状态
    private LocalDate startDate;   // 创建开始日期
    private LocalDate endDate;     // 创建结束日期
    private List<Long> ids;        // ID列表
    private String sortField;      // 排序字段
    private String sortOrder;      // 排序方向
}
xml
<select id="search" resultType="User">
    SELECT * FROM user
    <where>
        <!-- 关键词搜索 -->
        <if test="keyword != null and keyword != ''">
            <bind name="pattern" value="'%' + keyword + '%'"/>
            AND (username LIKE #{pattern} OR email LIKE #{pattern})
        </if>
        
        <!-- 精确匹配 -->
        <if test="username != null and username != ''">
            AND username = #{username}
        </if>
        <if test="email != null and email != ''">
            AND email = #{email}
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
        
        <!-- 日期范围 -->
        <if test="startDate != null">
            AND DATE(create_time) &gt;= #{startDate}
        </if>
        <if test="endDate != null">
            AND DATE(create_time) &lt;= #{endDate}
        </if>
        
        <!-- ID列表 -->
        <if test="ids != null and ids.size() > 0">
            AND id IN
            <foreach collection="ids" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
    </where>
    
    <!-- 动态排序 -->
    <if test="sortField != null and sortField != ''">
        ORDER BY
        <choose>
            <when test="sortField == 'username'">username</when>
            <when test="sortField == 'email'">email</when>
            <when test="sortField == 'createTime'">create_time</when>
            <otherwise>id</otherwise>
        </choose>
        <choose>
            <when test="sortOrder == 'desc'">DESC</when>
            <otherwise>ASC</otherwise>
        </choose>
    </if>
</select>

2. 灵活的更新操作 ​

xml
<update id="updateSelective">
    UPDATE user
    <set>
        <if test="username != null and username != ''">
            username = #{username},
        </if>
        <if test="email != null and email != ''">
            email = #{email},
        </if>
        <if test="password != null and password != ''">
            password = #{password},
        </if>
        <if test="status != null">
            status = #{status},
        </if>
        <if test="avatar != null">
            avatar = #{avatar},
        </if>
        update_time = NOW()
    </set>
    WHERE id = #{id}
</update>

3. 多条件删除 ​

xml
<delete id="deleteByCondition">
    DELETE FROM user
    <where>
        <if test="ids != null and ids.size() > 0">
            id IN
            <foreach collection="ids" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
        <if test="status != null">
            AND status = #{status}
        </if>
        <if test="beforeDate != null">
            AND create_time &lt; #{beforeDate}
        </if>
    </where>
</delete>

4. 复杂统计查询 ​

xml
<select id="getStatistics" resultType="map">
    SELECT
        COUNT(*) as total,
        <if test="groupBy != null">
            <choose>
                <when test="groupBy == 'status'">
                    status,
                    COUNT(*) as count
                </when>
                <when test="groupBy == 'date'">
                    DATE(create_time) as date,
                    COUNT(*) as count
                </when>
            </choose>
        </if>
    FROM user
    <where>
        <if test="status != null">
            AND status = #{status}
        </if>
        <if test="startDate != null">
            AND DATE(create_time) &gt;= #{startDate}
        </if>
        <if test="endDate != null">
            AND DATE(create_time) &lt;= #{endDate}
        </if>
    </where>
    <if test="groupBy != null">
        GROUP BY
        <choose>
            <when test="groupBy == 'status'">status</when>
            <when test="groupBy == 'date'">DATE(create_time)</when>
        </choose>
    </if>
</select>

5. 批量操作优化 ​

xml
<!-- 批量插入或更新 -->
<insert id="batchInsertOrUpdate">
    INSERT INTO user (id, username, email, update_time)
    VALUES
    <foreach collection="users" item="user" separator=",">
        (#{user.id}, #{user.username}, #{user.email}, NOW())
    </foreach>
    ON DUPLICATE KEY UPDATE
    username = VALUES(username),
    email = VALUES(email),
    update_time = VALUES(update_time)
</insert>

<!-- 批量条件更新 -->
<update id="batchUpdateStatus">
    UPDATE user
    SET status = #{status},
        update_time = NOW()
    WHERE id IN
    <foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</update>

test表达式 ​

常用判断 ​

xml
<!-- 判空 -->
<if test="str != null and str != ''">

<!-- 判断数字 -->
<if test="num != null and num > 0">

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

<!-- 判断布尔 -->
<if test="flag == true">
或
<if test="flag">

<!-- 字符串比较 -->
<if test="type == 'admin'">
或
<if test='type == "admin"'>

<!-- 多条件 -->
<if test="(status == 1 or status == 2) and username != null">

OGNL表达式 ​

MyBatis使用OGNL表达式语言：

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

<!-- 访问静态方法 -->
<if test="@com.example.util.StringUtil@isEmpty(username)">

<!-- 三元运算符 -->
<bind name="orderBy" value="sortOrder == 'desc' ? 'DESC' : 'ASC'"/>

最佳实践 ​

1. 合理使用where标签 ​

xml
<!-- 推荐 -->
<select id="select" resultType="User">
    SELECT * FROM user
    <where>
        <if test="username != null">AND username = #{username}</if>
        <if test="email != null">AND email = #{email}</if>
    </where>
</select>

<!-- 不推荐 -->
<select id="select" resultType="User">
    SELECT * FROM user
    WHERE 1=1
    <if test="username != null">AND username = #{username}</if>
    <if test="email != null">AND email = #{email}</if>
</select>

2. 提取公共SQL片段 ​

xml
<sql id="baseColumns">
    id, username, email, status, create_time, update_time
</sql>

<sql id="baseWhere">
    <where>
        <if test="status != null">AND status = #{status}</if>
        <if test="keyword != null">
            <bind name="pattern" value="'%' + keyword + '%'"/>
            AND (username LIKE #{pattern} OR email LIKE #{pattern})
        </if>
    </where>
</sql>

3. 避免SQL注入 ​

xml
<!-- 安全：使用#{}进行参数绑定 -->
<select id="select" resultType="User">
    SELECT * FROM user WHERE username = #{username}
</select>

<!-- 危险：直接字符串拼接 -->
<select id="select" resultType="User">
    SELECT * FROM user WHERE username = '${username}'
</select>

<!-- ${}的合理使用场景：动态表名、列名 -->
<select id="select" resultType="User">
    SELECT * FROM ${tableName}
    ORDER BY ${sortColumn} ${sortOrder}
</select>

4. 优化foreach性能 ​

xml
<!-- 大批量数据时，分批处理 -->
<insert id="batchInsert">
    INSERT INTO user (username, email)
    VALUES
    <foreach collection="users" item="user" separator=",">
        (#{user.username}, #{user.email})
    </foreach>
</insert>

<!-- 建议：每批不超过1000条 -->

5. 合理使用choose ​

xml
<!-- 互斥条件，使用choose -->
<select id="select" resultType="User">
    SELECT * FROM user
    <where>
        <choose>
            <when test="id != null">
                id = #{id}
            </when>
            <when test="username != null">
                username = #{username}
            </when>
            <otherwise>
                status = 1
            </otherwise>
        </choose>
    </where>
</select>

常见问题 ​

1. 特殊字符转义 ​

xml
<!-- 使用CDATA -->
<select id="select" resultType="User">
    SELECT * FROM user
    WHERE <![CDATA[ age >= 18 AND age <= 65 ]]>
</select>

<!-- 使用实体 -->
<select id="select" resultType="User">
    SELECT * FROM user
    WHERE age &gt;= 18 AND age &lt;= 65
</select>

2. foreach的坑 ​

xml
<!-- 错误：集合为空时会生成错误SQL -->
<select id="select" resultType="User">
    SELECT * FROM user WHERE id IN
    <foreach collection="ids" item="id" open="(" separator="," close=")">
        #{id}
    </foreach>
</select>

<!-- 正确：判断集合非空 -->
<select id="select" resultType="User">
    SELECT * FROM user
    <where>
        <if test="ids != null and ids.size() > 0">
            id IN
            <foreach collection="ids" item="id" open="(" separator="," close=")">
                #{id}
            </foreach>
        </if>
    </where>
</select>

总结 ​

动态SQL是MyBatis的核心特性，掌握它可以：

  • 避免大量的字符串拼接
  • 编写更灵活的SQL语句
  • 提高代码的可维护性
  • 减少重复代码

合理使用动态SQL标签可以让SQL更加清晰和优雅。