MyBatis 動態 SQL 與關聯查詢實戰指南：if/choose/set/foreach、resultMap 一對一/一對多/多對多、延遲載入完整範例

MyBatis 作為半自動 ORM，其核心優勢在於動態 SQL (XML 中靈活組裝條件) 與進階結果映射 (resultMap 處理複雜關聯)。本文系統整理動態 SQL 標籤、關聯查詢映射模式 (一對一/一對多/多對多) 與延遲載入配置，附完整 Mapper/DAO/Client 程式碼範例。

一、動態 SQL 核心標籤

1. <if> - 條件式拼接

<select id="selectUser" resultType="User" parameterType="User">
  SELECT * FROM user
  <where>
    <if test="id != null"> AND id = #{id} </if>
    <if test="name != null"> AND name = #{name} </if>
    <if test="password != null"> AND password = #{password} </if>
  </where>
</select>

<where> 智慧處理首個 `AND`/`OR`，避免語法錯誤。

2. <choose> / <when> / <otherwise> - 互斥選擇 (類 switch)

<choose>
  <when test="id != null"> AND id = #{id} </when>
  <when test="name != null"> AND name = #{name} </when>
  <otherwise> AND 1=1 </otherwise>
</choose>

3. <set> - 動態 UPDATE 欄位

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

<set> 自動移除尾端逗號。

4. <foreach> - 集合/陣列迭代 (IN 條件、批次 INSERT)

屬性對照：

IN 查詢範例：

<select id="selectByIds" resultType="User" parameterType="List">
  SELECT * FROM user
  WHERE id IN
  <foreach collection="list" item="id" open="(" close=")" separator=",">
    #{id}
  </foreach>
</select>

批次 INSERT 範例：

<insert id="batchInsert" parameterType="List">
  INSERT INTO user (name, password) VALUES
  <foreach collection="list" item="user" separator=",">
    (#{user.name}, #{user.password})
  </foreach>
</insert>

5. <sql> - 可重用 SQL 片段

<sql id="userWhere">
  <if test="id != null"> AND id = #{id} </if>
  <if test="name != null"> AND name = #{name} </if>
</sql>

<select id="selectUser" resultType="User">
  SELECT * FROM user
  <where>
    <include refid="userWhere"/>
  </where>
</select>

建議：片段以單表為基礎，不含 ``/`` 等結構標籤，提高復用率。

二、關聯查詢 resultMap 映射模式

領域模型：User (1) —< (N) Order (1) —< (N) OrderDetail > (1) Item。

1. 一對一：<association> (Order → User)

<resultMap id="orderUserMap" type="Order">
  <id column="id" property="id"/>
  <result column="user_id" property="userId"/>
  <result column="number" property="number"/>
  <association property="user" javaType="User">
    <id column="user_id" property="id"/>
    <result column="username" property="username"/>
    <result column="birthday" property="birthday"/>
  </association>
</resultMap>

<select id="selectOrderWithUser" resultMap="orderUserMap">
  SELECT o.*, u.username, u.birthday, u.sex, u.address
  FROM `order` o JOIN user u ON o.user_id = u.id
  WHERE o.id = #{0}
</select>

2. 一對多：<collection> (Order → OrderDetail 列表)

<resultMap id="orderDetailMap" type="Order" extends="orderUserMap">
  <collection property="orderDetails" ofType="OrderDetail">
    <id column="order_detail_id" property="id"/>
    <result column="item_id" property="itemId"/>
    <result column="item_num" property="itemNumber"/>
  </collection>
</resultMap>

<select id="selectOrderWithDetail" resultMap="orderDetailMap">
  SELECT o.*, u.username, ..., od.id order_detail_id, od.item_id, od.item_num
  FROM `order` o JOIN user u ON o.user_id = u.id
  JOIN orderdetail od ON o.id = od.order_id
  WHERE o.id = #{0}
</select>

3. 多對多：巢狀 collection + association (User → Orders → OrderDetails → Item)

<resultMap id="userItemMap" type="User">
  <id column="id" property="id"/>
  <result column="username" property="username"/>
  <collection property="orders" ofType="Order">
    <id column="order_id" property="id"/>
    <result column="order_number" property="number"/>
    <collection property="orderDetails" ofType="OrderDetail">
      <id column="order_detail_id" property="id"/>
      <association property="item" javaType="Item">
        <id column="item_id" property="id"/>
        <result column="item_name" property="name"/>
        <result column="item_price" property="price"/>
      </association>
    </collection>
  </collection>
</resultMap>

<select id="selectUserItem" resultMap="userItemMap">
  SELECT u.*, o.id order_id, o.number order_number, 
         od.id order_detail_id, od.item_num,
         i.id item_id, i.name item_name, i.price item_price
  FROM user u
  JOIN `order` o ON u.id = o.user_id
  JOIN orderdetail od ON o.id = od.order_id
  JOIN item i ON od.item_id = i.id
</select>

三、延遲載入 (Lazy Loading) - 按需查詢關聯資料

全域開關 (mybatis-config.xml)

<settings>
  <setting name="lazyLoadingEnabled" value="true"/>      <!-- 總開關 -->
  <setting name="aggressiveLazyLoading" value="false"/> <!-- false=按需載入 -->
</settings>

Mapper 定義延遲關聯

<resultMap id="orderUserMap" type="Order">
  <id column="id" property="id"/>
  <association property="user" 
               javaType="User" 
               select="UserMapper.selectById"  <!-- 關聯查詢 Statement -->
               column="user_id"/>              <-- 傳遞參數 -->
</resultMap>

<select id="selectOrders" resultMap="orderUserMap">
  SELECT * FROM `order`
</select>

<!-- 獨立 Statement -->
<select id="selectById" parameterType="int" resultType="User">
  SELECT * FROM user WHERE id = #{id}
</select>

驗證效果

List orders = orderMapper.selectOrders();
for (Order o : orders) {
    System.out.println(o.getUser()); // 此行才觸發 selectById 查詢
}

觀察日誌：只有真正存取 `order.getUser()` 時才發出關聯查詢 SQL，有效減輕資料庫壓力。

四、實務建議速查

1. 動態 SQL：優先用 `` + ``/``；複雜互斥邏輯用 ``；集合迭代用 ``；重用片段用 ``/``。
2. resultMap：`` 對應單一物件 (一對一)；`` 對應集合 (一對多)；巢狀組合處理多層關聯。 3. `extends` 繼承 resultMap 避免重複定義 (如 `orderDetailMap extends orderUserMap`)。 4. 延遲載入：全域開啟 + `aggressiveLazyLoading=false`，Mapper 用 `select` + `column` 定義關聯 Statement，按需觸發。 5. 大量資料關聯查詢慎用即時載入 (N+1 問題)，改用分頁/批次預載或報表專用視圖。

掌握 MyBatis 動態 SQL 與 resultMap 進階映射，即可在保持 SQL 完全掌控權的前提下，優雅解決複雜條件組裝與多層關聯物件圖構建需求。