MyBatis 关联查询详解

一对一、一对多、多对多、嵌套查询、嵌套结果

---

概述

MyBatis 通过 <association> 和 <collection> 标签支持复杂关系的映射查询。

查询方式对比

方式	说明	优点	缺点
嵌套查询（N+1）	先查主表，再查关联表	SQL 简单	执行多次查询
嵌套结果	一次 JOIN 查询	只查一次数据库	SQL 复杂
自动映射	配置 autoMapping	简单	需列名匹配

---

一对一关联

场景：订单 → 用户（一个订单属于一个用户）

表结构

-- 用户表
CREATE TABLE user (
    id BIGINT PRIMARY KEY,
    user_name VARCHAR(50),
    email VARCHAR(100)
);

-- 订单表
CREATE TABLE orders (
    id BIGINT PRIMARY KEY,
    order_no VARCHAR(50),
    user_id BIGINT,
    total_amount DECIMAL(10,2),
    FOREIGN KEY (user_id) REFERENCES user(id)
);

实体类

public class User {
    private Long id;
    private String name;
    private String email;
}

public class Order {
    private Long id;
    private String orderNo;
    private BigDecimal totalAmount;
    private User user;  // 一对一关联
}

---

方式一：嵌套结果（推荐）

<resultMap id="OrderWithUserResult" type="Order">
    <id column="order_id" property="id"/>
    <result column="order_no" property="orderNo"/>
    <result column="total_amount" property="totalAmount"/>
    
    <!-- 一对一关联：嵌套结果 -->
    <association property="user" javaType="User">
        <id column="user_id" property="id"/>
        <result column="user_name" property="name"/>
        <result column="email" property="email"/>
    </association>
</resultMap>

<select id="selectOrderWithUser" resultMap="OrderWithUserResult">
    SELECT o.id as order_id,
           o.order_no,
           o.total_amount,
           u.id as user_id,
           u.user_name,
           u.email
    FROM orders o
    LEFT JOIN user u ON o.user_id = u.id
    WHERE o.id = #{id}
</select>

Java 调用：

Order order = session.selectOne("mapper.OrderMapper.selectOrderWithUser", 1L);
System.out.println(order.getUser().getName());

---

方式二：嵌套查询（N+1 问题）

<!-- 查询订单 -->
<resultMap id="OrderWithUserResult2" type="Order">
    <id column="id" property="id"/>
    <result column="order_no" property="orderNo"/>
    <result column="total_amount" property="totalAmount"/>
    
    <!-- 嵌套查询：先查订单，再查用户 -->
    <association property="user" column="user_id" 
                 select="mapper.UserMapper.selectById"/>
</resultMap>

<select id="selectOrderWithUser2" resultMap="OrderWithUserResult2">
    SELECT id, order_no, total_amount, user_id
    FROM orders
    WHERE id = #{id}
</select>

<!-- 用户查询（被嵌套调用） -->
<select id="selectById" resultType="User">
    SELECT id, user_name as name, email
    FROM user
    WHERE id = #{id}
</select>

特点：

• 先执行主查询（订单）
• 再根据 column="user_id" 的值执行关联查询（用户）
• 总共执行 2 次 SQL

---

方式三：注解方式

public interface OrderMapper {
    
    @Results({
        @Result(column = "id", property = "id"),
        @Result(column = "order_no", property = "orderNo"),
        @Result(column = "user_id", property = "userId"),
        @Result(column = "user_id", property = "user",
                one = @One(select = "com.example.mapper.UserMapper.selectById"))
    })
    @Select("SELECT * FROM orders WHERE id = #{id}")
    Order selectOrderWithUser(Long id);
}

---

一对多关联

场景：用户 → 订单（一个用户有多个订单）

实体类

public class User {
    private Long id;
    private String name;
    private List<Order> orders;  // 一对多关联
}

---

方式一：嵌套结果

<resultMap id="UserWithOrdersResult" type="User">
    <id column="user_id" property="id"/>
    <result column="user_name" property="name"/>
    <result column="email" property="email"/>
    
    <!-- 一对多关联：嵌套结果 -->
    <collection property="orders" ofType="Order">
        <id column="order_id" property="id"/>
        <result column="order_no" property="orderNo"/>
        <result column="total_amount" property="totalAmount"/>
    </collection>
</resultMap>

<select id="selectUserWithOrders" resultMap="UserWithOrdersResult">
    SELECT u.id as user_id,
           u.user_name,
           u.email,
           o.id as order_id,
           o.order_no,
           o.total_amount
    FROM user u
    LEFT JOIN orders o ON u.id = o.user_id
    WHERE u.id = #{id}
</select>

---

方式二：嵌套查询

<resultMap id="UserWithOrdersResult2" type="User">
    <id column="id" property="id"/>
    <result column="user_name" property="name"/>
    <result column="email" property="email"/>
    
    <!-- 嵌套查询 -->
    <collection property="orders" column="id"
               select="mapper.OrderMapper.selectByUserId"/>
</resultMap>

<select id="selectUserWithOrders2" resultMap="UserWithOrdersResult2">
    SELECT * FROM user WHERE id = #{id}
</select>

<!-- 订单查询（嵌套） -->
<select id="selectByUserId" resultType="Order">
    SELECT id, order_no, total_amount
    FROM orders
    WHERE user_id = #{userId}
    ORDER BY id
</select>

---

方式三：注解方式

public interface UserMapper {
    
    @Results({
        @Result(column = "id", property = "id"),
        @Result(column = "user_name", property = "name"),
        @Result(column = "orders", property = "orders",
                many = @Many(select = "com.example.mapper.OrderMapper.selectByUserId"))
    })
    @Select("SELECT * FROM user WHERE id = #{id}")
    User selectUserWithOrders(Long id);
}

---

多对多关联

场景：学生 ↔ 课程（学生可以选多门课，课程可以被多名学生选）

表结构

-- 学生表
CREATE TABLE student (
    id BIGINT PRIMARY KEY,
    student_name VARCHAR(50)
);

-- 课程表
CREATE TABLE course (
    id BIGINT PRIMARY KEY,
    course_name VARCHAR(100)
);

-- 中间表（选课）
CREATE TABLE student_course (
    student_id BIGINT,
    course_id BIGINT,
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES student(id),
    FOREIGN KEY (course_id) REFERENCES course(id)
);

实体类

public class Student {
    private Long id;
    private String name;
    private List<Course> courses;
}

public class Course {
    private Long id;
    private String name;
    private List<Student> students;
}

---

查询学生及其课程

<resultMap id="StudentWithCoursesResult" type="Student">
    <id column="student_id" property="id"/>
    <result column="student_name" property="name"/>
    
    <collection property="courses" ofType="Course">
        <id column="course_id" property="id"/>
        <result column="course_name" property="name"/>
    </collection>
</resultMap>

<select id="selectStudentWithCourses" resultMap="StudentWithCoursesResult">
    SELECT s.id as student_id,
           s.student_name,
           c.id as course_id,
           c.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>

---

查询课程及其学生

<resultMap id="CourseWithStudentsResult" type="Course">
    <id column="course_id" property="id"/>
    <result column="course_name" property="name"/>
    
    <collection property="students" ofType="Student">
        <id column="student_id" property="id"/>
        <result column="student_name" property="name"/>
    </collection>
</resultMap>

<select id="selectCourseWithStudents" resultMap="CourseWithStudentsResult">
    SELECT c.id as course_id,
           c.course_name,
           s.id as student_id,
           s.student_name
    FROM course c
    LEFT JOIN student_course sc ON c.id = sc.course_id
    LEFT JOIN student s ON sc.student_id = s.id
    WHERE c.id = #{id}
</select>

---

延迟加载

配置开启

<settings>
    <!-- 开启延迟加载 -->
    <setting name="lazyLoadingEnabled" value="true"/>
    <!-- 积极加载改为按需加载 -->
    <setting name="aggressiveLazyLoading" value="false"/>
</settings>

使用延迟加载

// 只有访问关联对象时才触发查询
Order order = session.selectOne("selectOrderWithUser2", 1L);
System.out.println(order.getOrderNo());  // 只查订单

User user = order.getUser();  // 此时才触发用户查询
System.out.println(user.getName());

局部延迟加载（按需）

<!-- 全局禁用，但此查询启用 -->
<resultMap id="OrderWithUserEagerResult" type="Order">
    <id column="id" property="id"/>
    <association property="user" column="user_id" 
                 select="mapper.UserMapper.selectById"
                 fetchType="eager"/>
</resultMap>

<!-- 此查询禁用（默认延迟） -->
<resultMap id="OrderWithUserLazyResult" type="Order">
    <id column="id" property="id"/>
    <association property="user" column="user_id" 
                 select="mapper.UserMapper.selectById"
                 fetchType="lazy"/>
</resultMap>

---

常见问题

1. 列名重复导致映射错误

<!-- 给列起别名，避免重复 -->
<select id="selectUserWithOrders" resultMap="UserWithOrdersResult">
    SELECT u.id as user_id,
           u.user_name,
           o.id as order_id,
           o.order_no
    FROM user u
    LEFT JOIN orders o ON u.id = o.user_id
</select>

2. N+1 查询问题

问题：嵌套查询会执行 1 + N 次 SQL（N=关联数据量）

解决方案：

• 数据量小：使用嵌套结果（一次 JOIN）
• 数据量大：使用嵌套查询 + 延迟加载
• 分页：先查主表分页，再查关联数据

3. 嵌套结果分页问题

<!-- 嵌套结果不能直接 LIMIT -->
<!-- 需要先分页查主表，再 JOIN 关联表 -->
<select id="selectUserWithOrdersPage" resultMap="UserWithOrdersResult">
    SELECT * FROM (
        SELECT u.id as user_id, u.user_name, u.email
        FROM user u
        ORDER BY u.id
        LIMIT #{offset}, #{limit}
    ) t
    LEFT JOIN orders o ON t.user_id = o.user_id
</select>

---

相关页面

• **Java\MyBatis\MyBatis-完全指南** - 返回总览
• **Java\MyBatis\MyBatis-Mapper映射** - 基础映射
• **Java\MyBatis\MyBatis-缓存机制** - 一对多缓存问题

---

#标签 #mybatis #association #collection #关联查询 #延迟加载 #嵌套查询