MyBatis 多表聯查：resultMap 關聯映射或嵌套查詢

場景

查詢表 A、B 關聯數據，映射到包含 B 屬性的 A 實體（或 DTO）。

方案一：resultMap 關聯映射（單次查詢，推薦）

<resultMap id="orderDetailMap" type="OrderDTO">
    <id property="id" column="order_id"/>
    <result property="amount" column="order_amount"/>
    <association property="user" javaType="User">
        <id property="id" column="user_id"/>
        <result property="name" column="user_name"/>
    </association>
</resultMap>

<select id="selectOrderWithUser" resultMap="orderDetailMap">
    SELECT o.id AS order_id, o.amount AS order_amount,
           u.id AS user_id, u.name AS user_name
    FROM orders o
    JOIN users u ON o.user_id = u.id
    WHERE o.id = #{id}
</select>

方案二：嵌套查詢（N+1 問題，慎用）

<resultMap id="orderMap" type="Order">
    <id property="id" column="id"/>
    <association property="user" select="selectUserById" column="user_id"/>
</resultMap>

<select id="selectOrder" resultMap="orderMap">SELECT * FROM orders WHERE id = #{id}</select>
<select id="selectUserById" resultType="User">SELECT * FROM users WHERE id = #{id}</select>

**缺點**：查 1 個 Order 會額外發 1 條 SQL 查 User，列表頁極易產生 N+1 問題。

方案三：手寫 DTO + 平鋪映射（簡單場景）

// DTO 包含 Order 所有字段 + User 需要字段
public class OrderUserDTO {
    private Long orderId; private BigDecimal amount;
    private Long userId; private String userName;
    // getter/setter
}

<select id="selectOrderUser" resultType="OrderUserDTO">
    SELECT o.id AS orderId, o.amount, u.id AS userId, u.name AS userName
    FROM orders o JOIN users u ON o.user_id = u.id
    WHERE o.id = #{id}
</select>

參考教程

MyBatis 關聯查詢詳解

**首選方案一**：單 SQL、resultMap 精確映射、無 N+1、性能最優。