-- 用戶表
CREATE TABLE `user` (
  `id` int NOT NULL AUTO_INCREMENT,
  `username` varchar(50) NOT NULL,
  `age` int DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 身份證表（與用戶一對一關(guān)聯(lián)）
CREATE TABLE `id_card` (
  `id` int NOT NULL AUTO_INCREMENT,
  `card_no` varchar(20) NOT NULL, -- 身份證號
  `user_id` int NOT NULL, -- 關(guān)聯(lián)用戶ID（外鍵）
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id` (`user_id`) -- 一對一：user_id唯一
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

// 用戶類（包含一個身份證對象）
@Data
public class User {
    private Integer id;
    private String username;
    private Integer age;
    // 一對一關(guān)聯(lián)：用戶包含一個身份證
    private IdCard idCard; 
}

// 身份證類
@Data
public class IdCard {
    private Integer id;
    private String cardNo;
    private Integer userId;
}

// 根據(jù)用戶ID查詢用戶及關(guān)聯(lián)的身份證
User selectUserWithIdCardById(Integer id);

<!-- 定義resultMap：映射用戶及身份證 -->
<resultMap id="UserWithIdCardMap" type="User">
    <!-- 用戶表字段映射 -->
    <id column="id" property="id"/>
    <result column="username" property="username"/>
    <result column="age" property="age"/>
    
    <!-- 一對一關(guān)聯(lián)：association映射IdCard對象 -->
    <association property="idCard" javaType="IdCard">
        <id column="card_id" property="id"/> <!-- 注意：避免與user.id字段沖突 -->
        <result column="card_no" property="cardNo"/>
        <result column="user_id" property="userId"/>
    </association>
</resultMap>

<!-- 連接查詢：一次性查詢用戶和身份證 -->
<select id="selectUserWithIdCardById" resultMap="UserWithIdCardMap">
    SELECT 
        u.id, u.username, u.age,
        ic.id AS card_id, ic.card_no, ic.user_id
    FROM user u
    LEFT JOIN id_card ic ON u.id = ic.user_id
    WHERE u.id = #{id}
</select>

// IdCardMapper接口
IdCard selectById(Integer id);

<select id="selectById" resultType="IdCard">
    SELECT id, card_no, user_id FROM id_card WHERE id = #{id}
</select>

<resultMap id="UserWithIdCardNestedMap" type="User">
    <id column="id" property="id"/>
    <result column="username" property="username"/>
    <result column="age" property="age"/>
    
    <!-- 嵌套查詢：通過select屬性指定查詢關(guān)聯(lián)對象的方法 -->
    <association 
        property="idCard" 
        javaType="IdCard"
        column="id" <!-- 將用戶id作為參數(shù)傳遞給關(guān)聯(lián)查詢 -->
        select="com.example.mapper.IdCardMapper.selectByUserId"/> <!-- 關(guān)聯(lián)查詢的Mapper方法 -->
</resultMap>

<select id="selectUserWithIdCardNestedById" resultMap="UserWithIdCardNestedMap">
    SELECT id, username, age FROM user WHERE id = #{id}
</select>

-- 訂單表（與用戶一對多關(guān)聯(lián)）
CREATE TABLE `order` (
  `id` int NOT NULL AUTO_INCREMENT,
  `order_no` varchar(20) NOT NULL, -- 訂單號
  `total_amount` decimal(10,2) NOT NULL, -- 總金額
  `user_id` int NOT NULL, -- 關(guān)聯(lián)用戶ID
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

// 用戶類（包含訂單列表）
@Data
public class User {
    private Integer id;
    private String username;
    private Integer age;
    // 一對多關(guān)聯(lián)：用戶包含多個訂單
    private List<Order> orders; 
}

// 訂單類
@Data
public class Order {
    private Integer id;
    private String orderNo;
    private BigDecimal totalAmount;
    private Integer userId;
}

// 查詢用戶及關(guān)聯(lián)的所有訂單
User selectUserWithOrdersById(Integer id);

<!-- 定義resultMap：映射用戶及訂單列表 -->
<resultMap id="UserWithOrdersMap" type="User">
    <!-- 用戶表字段 -->
    <id column="id" property="id"/>
    <result column="username" property="username"/>
    <result column="age" property="age"/>
    
    <!-- 一對多關(guān)聯(lián)：collection映射訂單列表 -->
    <collection property="orders" ofType="Order"> <!-- ofType指定集合元素類型 -->
        <id column="order_id" property="id"/> <!-- 訂單ID（別名避免沖突） -->
        <result column="order_no" property="orderNo"/>
        <result column="total_amount" property="totalAmount"/>
        <result column="user_id" property="userId"/>
    </collection>
</resultMap>

<!-- 連接查詢：用戶與訂單 -->
<select id="selectUserWithOrdersById" resultMap="UserWithOrdersMap">
    SELECT 
        u.id, u.username, u.age,
        o.id AS order_id, o.order_no, o.total_amount, o.user_id
    FROM user u
    LEFT JOIN `order` o ON u.id = o.user_id
    WHERE u.id = #{id}
</select>

-- 學(xué)生表
CREATE TABLE `student` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 課程表
CREATE TABLE `course` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 中間表（多對多關(guān)聯(lián)）
CREATE TABLE `student_course` (
  `id` int NOT NULL AUTO_INCREMENT,
  `student_id` int NOT NULL,
  `course_id` int NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_stu_course` (`student_id`,`course_id`) -- 避免重復(fù)關(guān)聯(lián)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

// 學(xué)生類（包含課程列表）
@Data
public class Student {
    private Integer id;
    private String name;
    // 多對多關(guān)聯(lián)：學(xué)生包含多個課程
    private List<Course> courses;
}

// 課程類
@Data
public class Course {
    private Integer id;
    private String name;
}

// 查詢學(xué)生及所選課程
Student selectStudentWithCoursesById(Integer id);

<resultMap id="StudentWithCoursesMap" type="Student">
    <id column="id" property="id"/>
    <result column="name" property="name"/>
    
    <!-- 多對多：collection映射課程列表 -->
    <collection property="courses" ofType="Course">
        <id column="course_id" property="id"/>
        <result column="course_name" property="name"/>
    </collection>
</resultMap>

<select id="selectStudentWithCoursesById" resultMap="StudentWithCoursesMap">
    SELECT 
        s.id, s.name,
        c.id AS course_id, c.name AS course_name
    FROM student s
    LEFT JOIN student_course sc ON s.id = sc.student_id
    LEFT JOIN course c ON sc.course_id = c.id
    WHERE s.id = #{id}
</select>

<!-- 1次主表查詢：查詢所有用戶 -->
<select id="selectAllUser" resultMap="UserWithOrdersNestedMap">
    SELECT id, username, age FROM user
</select>

<!-- 每個用戶觸發(fā)1次訂單查詢（若有100個用戶，觸發(fā)100次） -->
<collection property="orders" select="selectOrdersByUserId" column="id"/>

-- 錯誤：未用別名，o.id會覆蓋u.id
SELECT u.id, u.name, o.id, o.name 
FROM user u JOIN order o ON u.id = o.user_id

-- 正確：用別名區(qū)分
SELECT 
    u.id AS user_id, u.name AS user_name,
    o.id AS order_id, o.name AS order_name

<settings>
    <setting name="lazyLoadingEnabled" value="true"/> <!-- 全局開啟延遲加載 -->
    <setting name="aggressiveLazyLoading" value="false"/> <!-- 按需加載（訪問時才加載） -->
</settings>

<collection property="orders" ofType="Order">
    <id column="order_id" property="id"/> <!-- 關(guān)鍵：指定訂單唯一標(biāo)識 -->
    <!-- 其他字段 -->
</collection>

<!-- 主查詢返回字段為user_id -->
<select id="selectUser" resultMap="UserMap">
    SELECT id AS user_id, username FROM user
</select>

<!-- 嵌套查詢需用主查詢的字段名作為參數(shù) -->
<association select="selectOrder" column="user_id"/> <!-- 正確：column="user_id" -->

<association select="selectByParams" column="{id=user_id, name=user_name}"/>