MyBatis一對(duì)多關(guān)系映射方式小結(jié)

更新時(shí)間：2025年10月11日 09:52:44 作者：Iris761

在實(shí)際開(kāi)發(fā)中經(jīng)常會(huì)有一對(duì)多的表關(guān)系,因此掌握使用MyBatis處理一對(duì)多關(guān)系映射方式時(shí)很有必要的,本文就來(lái)介紹一下MyBatis一對(duì)多關(guān)系映射方式,感興趣的可以了解一下

1. MyBatis N+1 問(wèn)題詳解

1.1 什么是N+1問(wèn)題

N+1問(wèn)題是指執(zhí)行1次主查詢(xún)獲取N條主記錄，然后對(duì)每條主記錄再執(zhí)行1次關(guān)聯(lián)查詢(xún)，總共執(zhí)行 1 + N 次查詢(xún)的性能問(wèn)題。

1.2 示例場(chǎng)景

假設(shè)有：部門(mén)表(department)和員工表(employee)，一個(gè)部門(mén)有多個(gè)員工。

產(chǎn)生N+1問(wèn)題的代碼如下：

<!-- 1. 先查詢(xún)所有部門(mén) -->
<select id="selectAllDepartments" resultMap="DepartmentResultMap">
    SELECT id, name FROM department
</select>

<!-- 2. 為每個(gè)部門(mén)查詢(xún)員工 -->
<select id="selectEmployeesByDeptId" resultType="Employee">
    SELECT id, name FROM employee WHERE dept_id = #{deptId}
</select>

<!-- 3. 結(jié)果映射中使用嵌套查詢(xún) -->
<resultMap id="DepartmentResultMap" type="Department">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <collection property="employees" column="id" 
                ofType="Employee" select="selectEmployeesByDeptId"/>
</resultMap>

執(zhí)行過(guò)程：

List<Department> departments = departmentMapper.selectAllDepartments();
// 實(shí)際執(zhí)行的SQL：
// 1. SELECT id, name FROM department; (假設(shè)返回3個(gè)部門(mén))
// 2. SELECT id, name FROM employee WHERE dept_id = 1;
// 3. SELECT id, name FROM employee WHERE dept_id = 2;
// 4. SELECT id, name FROM employee WHERE dept_id = 3;
// 總共執(zhí)行了 1 + 3 = 4 次查詢(xún)

1.3 產(chǎn)生問(wèn)題

性能低下：查詢(xún)次數(shù)隨數(shù)據(jù)量線(xiàn)性增長(zhǎng)
數(shù)據(jù)庫(kù)壓力大：頻繁建立數(shù)據(jù)庫(kù)連接
響應(yīng)時(shí)間長(zhǎng)：網(wǎng)絡(luò)往返次數(shù)多

接下來(lái)講解解決1+N問(wèn)題的方式

2. 使用JOIN 查詢(xún) + Collection 映射

2.1 實(shí)體類(lèi)定義

// 部門(mén)實(shí)體
@Getter
@Setter
public class Department {
    private Long id;
    private String name;
    private List<Employee> employees; // 一對(duì)多關(guān)系
}

// 員工實(shí)體
@Getter
@Setter
public class Employee {
    private Long id;
    private String name;
    private String position;
    private Long deptId;
}

2.2 XML配置

由于 JOIN 查詢(xún)有些情況會(huì)產(chǎn)生重復(fù)的部門(mén)數(shù)據(jù)，MyBatis 會(huì)自動(dòng)處理這種重復(fù)，但需要使用<id>標(biāo)簽指定好主鍵：

<!-- 使用 JOIN 查詢(xún)一次性獲取所有數(shù)據(jù) -->
<resultMap id="DepartmentWithEmployeesMap" type="Department">
    <id property="id" column="dept_id"/><!-- 重要配置 -->
    <result property="name" column="dept_name"/>
    
    <!-- 使用 collection 映射一對(duì)多關(guān)系 -->
    <collection property="employees" ofType="Employee" javaType="java.util.ArrayList">
        <id property="id" column="emp_id"/><!-- 重要配置 -->
        <result property="name" column="emp_name"/>
        <result property="position" column="position"/>
        <result property="deptId" column="dept_id"/>
    </collection>
</resultMap>

<select id="selectDepartmentWithEmployees" resultMap="DepartmentWithEmployeesMap">
    SELECT 
        d.id as dept_id,
        d.name as dept_name,
        e.id as emp_id,
        e.name as emp_name,
        e.position,
        e.dept_id
    FROM department d
    LEFT JOIN employee e ON d.id = e.dept_id
    WHERE d.id = #{id}
</select>

<!-- 查詢(xún)多個(gè)部門(mén)及其員工 -->
<select id="selectAllDepartmentsWithEmployees" resultMap="DepartmentWithEmployeesMap">
    SELECT 
        d.id as dept_id,
        d.name as dept_name,
        e.id as emp_id,
        e.name as emp_name,
        e.position,
        e.dept_id
    FROM department d
    LEFT JOIN employee e ON d.id = e.dept_id
    ORDER BY d.id, e.id
</select>

2.3 使用實(shí)例

@Service
public class DepartmentService {
    
    @Autowired
    private DepartmentMapper departmentMapper;
    
    // 一次性獲取部門(mén)及其所有員工，避免N+1問(wèn)題
    public Department getDepartmentWithEmployees(Long deptId) {
        return departmentMapper.selectDepartmentWithEmployees(deptId);
    }
    
    // 獲取所有部門(mén)及其員工
    public List<Department> getAllDepartmentsWithEmployees() {
        return departmentMapper.selectAllDepartmentsWithEmployees();
    }
    
    // 業(yè)務(wù)方法：統(tǒng)計(jì)各部門(mén)員工數(shù)量
    public Map<String, Integer> getEmployeeCountByDepartment() {
        List<Department> departments = departmentMapper.selectAllDepartmentsWithEmployees();
        
        return departments.stream()
            .collect(Collectors.toMap(
                Department::getName,
                dept -> dept.getEmployees() != null ? dept.getEmployees().size() : 0
            ));
    }
}

2.4 復(fù)雜場(chǎng)景(多層嵌套)

假設(shè)有一個(gè)多層嵌套的復(fù)雜場(chǎng)景，表關(guān)系如下：

// 公司實(shí)體
public class Company {
    private Long id;
    private String name;
    private List<Department> departments; // 一對(duì)多：公司有多個(gè)部門(mén)
}

// 部門(mén)實(shí)體
public class Department {
    private Long id;
    private String name;
    private Long companyId;           // 所屬公司ID
    private List<Employee> employees; // 一對(duì)多：部門(mén)有多個(gè)員工
    private List<Project> projects;   // 一對(duì)多：部門(mén)有多個(gè)項(xiàng)目
}

// 員工實(shí)體
public class Employee {
    private Long id;
    private String name;
    private String position;      // 新增字段
    private Long deptId;         // 所屬部門(mén)ID
    private List<Skill> skills;  // 多對(duì)多：?jiǎn)T工有多個(gè)技能
}

// 項(xiàng)目實(shí)體
public class Project {
    private Long id;
    private String name;
    private Long deptId;         // 所屬部門(mén)ID
    private Date startDate;      // 新增字段
    private Date endDate;        // 新增字段
}

// 技能實(shí)體
public class Skill {
    private Long id;
    private String name;
    private String category;     // 新增字段：技能分類(lèi)
}

// 員工技能關(guān)聯(lián)實(shí)體（多對(duì)多中間表）
public class EmployeeSkill {
    private Long id;
    private Long employeeId;
    private Long skillId;
    private Integer proficiency; // 熟練程度
}

對(duì)應(yīng)的Mapper映射如下：

<!-- 更新后的結(jié)果映射，包含所有字段 -->
<resultMap id="CompanyResultMap" type="Company">
    <id property="id" column="company_id"/>
    <result property="name" column="company_name"/>
    <collection property="departments" ofType="Department" resultMap="DepartmentResultMap"/>
</resultMap>

<resultMap id="DepartmentResultMap" type="Department">
    <id property="id" column="dept_id"/>
    <result property="name" column="dept_name"/>
    <result property="companyId" column="company_id"/>
    <collection property="employees" ofType="Employee" resultMap="EmployeeResultMap"/>
    <collection property="projects" ofType="Project" resultMap="ProjectResultMap"/>
</resultMap>

<resultMap id="EmployeeResultMap" type="Employee">
    <id property="id" column="emp_id"/>
    <result property="name" column="emp_name"/>
    <result property="position" column="position"/>
    <result property="deptId" column="dept_id"/>
    <collection property="skills" ofType="Skill" resultMap="SkillResultMap"/>
</resultMap>

<resultMap id="ProjectResultMap" type="Project">
    <id property="id" column="project_id"/>
    <result property="name" column="project_name"/>
    <result property="deptId" column="dept_id"/>
    <result property="startDate" column="start_date"/>
    <result property="endDate" column="end_date"/>
</resultMap>

<resultMap id="SkillResultMap" type="Skill">
    <id property="id" column="skill_id"/>
    <result property="name" column="skill_name"/>
    <result property="category" column="category"/>
</resultMap>

<!-- 更新后的查詢(xún)SQL，包含所有字段 -->
<select id="selectCompanyWithDetails" resultMap="CompanyResultMap">
    SELECT 
        c.id as company_id, 
        c.name as company_name,
        d.id as dept_id, 
        d.name as dept_name,
        d.company_id,
        e.id as emp_id, 
        e.name as emp_name,
        e.position,
        e.dept_id,
        p.id as project_id, 
        p.name as project_name,
        p.dept_id,
        p.start_date,
        p.end_date,
        s.id as skill_id, 
        s.name as skill_name,
        s.category
    FROM company c
    LEFT JOIN department d ON c.id = d.company_id
    LEFT JOIN employee e ON d.id = e.dept_id
    LEFT JOIN project p ON d.id = p.dept_id
    LEFT JOIN employee_skill es ON e.id = es.employee_id
    LEFT JOIN skill s ON es.skill_id = s.id
    WHERE c.id = #{id}
</select>

3. 分次查詢(xún)+Stream處理

還有一種方式是通過(guò)分次(次數(shù)為關(guān)聯(lián)表的個(gè)數(shù))查詢(xún)關(guān)聯(lián)表后，再使用Stream流組裝數(shù)據(jù)，下面是通過(guò)分次查詢(xún)+Stream處理查詢(xún)公司詳情信息的方法：

@Service
public class CompanyService {
    
    public Company getCompanyWithDetails(Long companyId) {
        // 1. 查詢(xún)公司
        Company company = companyMapper.selectById(companyId);
        if (company == null) return null;
        
        // 2. 查詢(xún)部門(mén)
        List<Department> departments = departmentMapper.selectByCompanyId(companyId);
        
        // 3. 查詢(xún)員工（批量查詢(xún)避免N+1）
        List<Long> deptIds = departments.stream()
            .map(Department::getId)
            .collect(Collectors.toList());
        List<Employee> employees = employeeMapper.selectByDeptIds(deptIds);
        
        // 4. 使用Stream組裝數(shù)據(jù)
        Map<Long, List<Employee>> employeeMap = employees.stream()
            .collect(Collectors.groupingBy(Employee::getDeptId));
        
        departments.forEach(dept -> 
            dept.setEmployees(employeeMap.getOrDefault(dept.getId(), new ArrayList<>()))
        );
        
        company.setDepartments(departments);
        return company;
    }
}

4. 性能對(duì)比

同樣的業(yè)務(wù)下(查詢(xún)公司詳情信息)他們的性能對(duì)比表如下：

方面	JOIN+Collection	分次查詢(xún)+Stream
數(shù)據(jù)庫(kù)查詢(xún)次數(shù)	1次	3次
網(wǎng)絡(luò)開(kāi)銷(xiāo)	低	中等
數(shù)據(jù)庫(kù)壓力	單次復(fù)雜查詢(xún)	多次簡(jiǎn)單查詢(xún)
內(nèi)存占用	可能有重復(fù)數(shù)據(jù)	數(shù)據(jù)更緊湊
響應(yīng)時(shí)間	穩(wěn)定但可能較長(zhǎng)	可能更快（并行查詢(xún)）

總結(jié)：建議在管理后臺(tái)數(shù)據(jù)展示這樣的小數(shù)據(jù)量場(chǎng)景使用Join+Collection方案，在API接口大數(shù)據(jù)量這樣較大數(shù)據(jù)量使用分次查詢(xún)+Stream方案。

到此這篇關(guān)于MyBatis一對(duì)多關(guān)系映射方式小結(jié)的文章就介紹到這了,更多相關(guān)MyBatis一對(duì)多關(guān)系映射內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章: