MyBatis 關聯查詢深度解析：一對一、一對多雙模式（嵌套結果 vs 嵌套查詢）

場景準備

-- 教師表
CREATE TABLE teacher (t_id INT PRIMARY KEY AUTO_INCREMENT, t_name VARCHAR(20));
-- 班級表
CREATE TABLE class (c_id INT PRIMARY KEY AUTO_INCREMENT, c_name VARCHAR(20), teacher_id INT);
-- 學生表
CREATE TABLE student (s_id INT PRIMARY KEY AUTO_INCREMENT, s_name VARCHAR(20), class_id INT);

關係：Class 一對一 Teacher，Class 一對多 Student

實體類設計

public class Classes {
    private int id;
    private String name;
    private Teacher teacher;           // 一對一
    private List<Student> students;   // 一對多
    // getter/setter/toString
}
public class Teacher { int id; String name; }
public class Student { int id; String name; }

一對一關聯：association 標籤

模式一：嵌套結果

<select id="getClass" resultMap="classMap">
    SELECT c.c_id, c.c_name, t.t_id, t.t_name
    FROM class c LEFT JOIN teacher t ON c.teacher_id = t.t_id
    WHERE c.c_id = #{id}
</select>

<resultMap id="classMap" type="Classes">
    <id property="id" column="c_id"/>
    <result property="name" column="c_name"/>
    <association property="teacher" javaType="Teacher">
        <id property="id" column="t_id"/>
        <result property="name" column="t_name"/>
    </association>
</resultMap>

優點：單次查詢，性能最佳。缺點：結果集列名需手動別名區分。

模式二：嵌套查詢

<select id="getClass" resultMap="classMap">
    SELECT * FROM class WHERE c_id = #{id}
</select>

<resultMap id="classMap" type="Classes">
    <id property="id" column="c_id"/>
    <result property="name" column="c_name"/>
    <association property="teacher" column="teacher_id" 
                 select="getTeacher"/>  <!-- 觸發二次查詢 -->
</resultMap>

<select id="getTeacher" resultType="Teacher">
    SELECT t_id id, t_name name FROM teacher WHERE t_id = #{id}
</select>

優點：SQL 簡單、可復用。缺點：N+1 問題，需配合緩存使用。

一對多關聯：collection 標籤

模式一：嵌套結果（推薦）

<select id="getClass" resultMap="classDetailMap">
    SELECT c.c_id, c.c_name, t.t_id, t.t_name, s.s_id, s.s_name
    FROM class c
    LEFT JOIN teacher t ON c.teacher_id = t.t_id
    LEFT JOIN student s ON c.c_id = s.class_id
    WHERE c.c_id = #{id}
</select>

<resultMap id="classDetailMap" type="Classes">
    <id property="id" column="c_id"/>
    <result property="name" column="c_name"/>
    <association property="teacher" javaType="Teacher">
        <id property="id" column="t_id"/>
        <result property="name" column="t_name"/>
    </association>
    <collection property="students" ofType="Student">
        <id property="id" column="s_id"/>
        <result property="name" column="s_name"/>
    </collection>
</resultMap>

關鍵：ofType 指定集合泛型類型，id 元素用於去重（防止主表行重複導致對象重複）。

模式二：嵌套查詢

<collection property="students" ofType="Student" column="c_id" select="getStudents"/>

<select id="getStudents" resultType="Student">
    SELECT s_id id, s_name name FROM student WHERE class_id = #{id}
</select>

性能選型建議

避坑指南

• resultMap 中主表必須有 <id> 元素，否則一對多會產生重複對象
• 嵌套結果模式下，列名衝突需用 SQL 別名區分（如 t_id AS teacher_id）
• 嵌套查詢開啟 lazyLoadingEnabled=true 可按需加載