MyBatis 動態 SQL 完全指南：if/choose/trim/where/set/foreach 實戰詳解

動態 SQL 核心標籤對照表

1. <if> — 條件拼接

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

注意：WHERE 1=1 避免所有條件失效導致語法錯誤。

2. <choose> — 互斥分支

<select id="queryByName" resultType="User">
  SELECT * FROM sys_user WHERE user_type_id = 1
  <choose>
    <when test="userName != null">AND user_name LIKE CONCAT('%', #{userName}, '%')</when>
    <when test="nickName != null">AND nick_name LIKE CONCAT('%', #{nickName}, '%')</when>
    <otherwise>AND is_valid = 1</otherwise>
  </choose>
</select>

按順序匹配首個成立分支，類似 switch。

3. <trim> — 精準控制前後綴

<select id="findMenu" resultType="Menu">
  SELECT * FROM sys_menu
  <trim prefix="WHERE" prefixOverrides="AND |OR">
    <if test="id != null">AND id = #{id}</if>
    <if test="name != null">AND menu_name LIKE #{name}</if>
  </trim>
</select>

• prefix：若內容非空，前面加 WHERE
• prefixOverrides：移除開頭的 AND/OR

4. <where> &

<!-- where 自動處理首個 AND/OR -->
<select id="findUser" resultType="User">
  SELECT * FROM user
  <where>
    <if test="id != null">id = #{id}</if>
    <if test="name != null">AND username = #{name}</if>
  </where>
</select>

<!-- set 自動處理尾部逗號 -->
<update id="updateUser">
  UPDATE user
  <set>
    <if test="name != null">username = #{name},</if>
    <if test="email != null">email = #{email},</if>
  </set>
  WHERE id = #{id}
</update>

5. <foreach> — 集合/陣列遍歷（IN 查詢、批次插入）

List<User> 傳入

<select id="selectByIds" resultType="User">
  SELECT * FROM user
  <where>
    <if test="list != null and !list.isEmpty()">
      <foreach collection="list" item="u" open="AND id IN (" separator="," close=")">
        #{u.id}
      </foreach>
    </if>
  </where>
</select>

陣列/基本類型 List 傳入

<select id="selectByArray" resultType="User">
  SELECT * FROM user
  <where>
    <foreach collection="array" item="id" open="AND id IN (" separator="," close=")">
      #{id}
    </foreach>
  </where>
</select>

MyBatis 自動將單一 List/Array 參數包裝為 Map，Key 分別為 list、array。

OGNL 表達式常用語法

• property != null — 非空判斷
• list != null and !list.isEmpty() — 集合非空
• map.containsKey('key') — Map 包含鍵
• obj.property — 存取屬性/Getter