MyBatis一对多关系映射方式小结_Java

1. mybatis n+1 问题详解

1.1 什么是n+1问题

n+1问题是指执行1次主查询获取n条主记录，然后对每条主记录再执行1次关联查询，总共执行 1 + n 次查询的性能问题。

1.2 示例场景

假设有：部门表(department)和员工表(employee)，一个部门有多个员工。

产生n+1问题的代码如下：

<!-- 1. 先查询所有部门 -->
<select id="selectalldepartments" resultmap="departmentresultmap">
    select id, name from department
</select>

<!-- 2. 为每个部门查询员工 -->
<select id="selectemployeesbydeptid" resulttype="employee">
    select id, name from employee where dept_id = #{deptid}
</select>

<!-- 3. 结果映射中使用嵌套查询 -->
<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>

执行过程：

list<department> departments = departmentmapper.selectalldepartments();
// 实际执行的sql：
// 1. select id, name from department; (假设返回3个部门)
// 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;
// 总共执行了 1 + 3 = 4 次查询

1.3 产生问题

性能低下：查询次数随数据量线性增长
数据库压力大：频繁建立数据库连接
响应时间长：网络往返次数多

接下来讲解解决1+n问题的方式

2. 使用join 查询 + collection 映射

2.1 实体类定义

// 部门实体
@getter
@setter
public class department {
    private long id;
    private string name;
    private list<employee> employees; // 一对多关系
}

// 员工实体
@getter
@setter
public class employee {
    private long id;
    private string name;
    private string position;
    private long deptid;
}

2.2 xml配置

由于 join 查询有些情况会产生重复的部门数据，mybatis 会自动处理这种重复，但需要使用<id>标签指定好主键：

<!-- 使用 join 查询一次性获取所有数据 -->
<resultmap id="departmentwithemployeesmap" type="department">
    <id property="id" column="dept_id"/><!-- 重要配置 -->
    <result property="name" column="dept_name"/>
    
    <!-- 使用 collection 映射一对多关系 -->
    <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>

<!-- 查询多个部门及其员工 -->
<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 使用实例

@service
public class departmentservice {
    
    @autowired
    private departmentmapper departmentmapper;
    
    // 一次性获取部门及其所有员工，避免n+1问题
    public department getdepartmentwithemployees(long deptid) {
        return departmentmapper.selectdepartmentwithemployees(deptid);
    }
    
    // 获取所有部门及其员工
    public list<department> getalldepartmentswithemployees() {
        return departmentmapper.selectalldepartmentswithemployees();
    }
    
    // 业务方法：统计各部门员工数量
    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 复杂场景(多层嵌套)

假设有一个多层嵌套的复杂场景，表关系如下：

// 公司实体
public class company {
    private long id;
    private string name;
    private list<department> departments; // 一对多：公司有多个部门
}

// 部门实体
public class department {
    private long id;
    private string name;
    private long companyid;           // 所属公司id
    private list<employee> employees; // 一对多：部门有多个员工
    private list<project> projects;   // 一对多：部门有多个项目
}

// 员工实体
public class employee {
    private long id;
    private string name;
    private string position;      // 新增字段
    private long deptid;         // 所属部门id
    private list<skill> skills;  // 多对多：员工有多个技能
}

// 项目实体
public class project {
    private long id;
    private string name;
    private long deptid;         // 所属部门id
    private date startdate;      // 新增字段
    private date enddate;        // 新增字段
}

// 技能实体
public class skill {
    private long id;
    private string name;
    private string category;     // 新增字段：技能分类
}

// 员工技能关联实体（多对多中间表）
public class employeeskill {
    private long id;
    private long employeeid;
    private long skillid;
    private integer proficiency; // 熟练程度
}

对应的mapper映射如下：

<!-- 更新后的结果映射，包含所有字段 -->
<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>

<!-- 更新后的查询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. 分次查询+stream处理

还有一种方式是通过分次(次数为关联表的个数)查询关联表后，再使用stream流组装数据，下面是通过分次查询+stream处理查询公司详情信息的方法：

@service
public class companyservice {
    
    public company getcompanywithdetails(long companyid) {
        // 1. 查询公司
        company company = companymapper.selectbyid(companyid);
        if (company == null) return null;
        
        // 2. 查询部门
        list<department> departments = departmentmapper.selectbycompanyid(companyid);
        
        // 3. 查询员工（批量查询避免n+1）
        list<long> deptids = departments.stream()
            .map(department::getid)
            .collect(collectors.tolist());
        list<employee> employees = employeemapper.selectbydeptids(deptids);
        
        // 4. 使用stream组装数据
        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. 性能对比

同样的业务下(查询公司详情信息)他们的性能对比表如下：

方面	join+collection	分次查询+stream
数据库查询次数	1次	3次
网络开销	低	中等
数据库压力	单次复杂查询	多次简单查询
内存占用	可能有重复数据	数据更紧凑
响应时间	稳定但可能较长	可能更快（并行查询）