第2章: 动态SQL

if 标签

<select id="findByCondition" resultType="User">
    SELECT * FROM users
    WHERE 1=1
    <if test="username != null">
        AND username LIKE CONCAT('%', #{username}, '%')
    </if>
    <if test="age != null">
        AND age = #{age}
    </if>
</select>

where 标签

<select id="findByCondition" resultType="User">
    SELECT * FROM users
    <where>
        <if test="username != null">
            AND username LIKE CONCAT('%', #{username}, '%')
        </if>
        <if test="age != null">
            AND age = #{age}
        </if>
    </where>
</select>

choose/when/otherwise

<select id="findByCondition" resultType="User">
    SELECT * FROM users
    <where>
        <choose>
            <when test="username != null">
                username = #{username}
            </when>
            <when test="email != null">
                email = #{email}
            </when>
            <otherwise>
                age > 18
            </otherwise>
        </choose>
    </where>
</select>

foreach 标签

<!-- 批量删除 -->
<delete id="deleteByIds">
    DELETE FROM users WHERE id IN
    <foreach collection="ids" item="id" open="(" close=")" separator=",">
        #{id}
    </foreach>
</delete>

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

set 标签

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

本章小结

if 标签实现条件判断
where 标签自动处理 AND/OR
choose 实现多条件选择
foreach 实现批量操作
set 标签动态更新字段