Mybatis 动态 SQL

TIP

动态 SQL 是 MyBatis 最强大的功能之一，可以根据条件动态拼接 SQL，避免了在 Java 代码中手动拼接 SQL 的繁琐和风险。

if

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

where

自动处理多余的 AND 和 OR：

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

set

用于 UPDATE 语句，自动处理多余的逗号：

<update id="updateById" parameterType="User">
    UPDATE user
    <set>
        <if test="name != null">name = #{name},</if>
        <if test="age != null">age = #{age},</if>
        <if test="email != null">email = #{email},</if>
    </set>
    WHERE id = #{id}
</update>

foreach

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

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

choose（when / otherwise）

类似 Java 的 switch-case：

<select id="findByCondition" resultType="User">
    SELECT * FROM user WHERE 1 = 1
    <choose>
        <when test="name != null">
            AND name LIKE CONCAT('%', #{name}, '%')
        </when>
        <when test="email != null">
            AND email = #{email}
        </when>
        <otherwise>
            AND age >= 18
        </otherwise>
    </choose>
</select>