MyBatis 级联表映射查询和N+1问题

叁歲伎倆 2023-06-09 10:21 4阅读 0赞

直接写demo，这里表都没加外键约束，开发中通过 java代码维护

**1、N+1问题？**

N+1问题来源于数据库中常见的级联技术，即 N个数据库表形成关联关系，当再增加一个关联表时，也就是 **N+1个级联关系**，

由于某些时候，我们并不需要加载数据库的所有数据，而是某一个数据库表中数据，这时 **Mybatis会自动加载所有表的数据，多执行几条无关sql语句**，会造成数据库资源的浪费以及系统性能的下降，这就是级联表的缺点。

**解决方案：**全局定义延迟加载。

延迟加载会解决上述的 N+1问题，也就是在 N+1个级联表的情况下，只加载需求的数据库表数据。这是互联网发展的需求，性能提升的途径。

下面demo中，**没有开启延时加载，所以会发送 N+1问题，**不管是否使用到关联数据，每次都会发送 SQL语句把关联数据查询出来。

### 一、多对一 ###

案例：多个员工对象同属于同一个部门对象

java pojo类设计

![20191020184536168.png][]

表设计（many方）：

CREATE TABLE `department` (
      `id` bigint(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
    
    CREATE TABLE `employee` (
      `id` bigint(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      `dept_id` bigint(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

**1、新增保存**

DepartmentMapper：

int insert(Department department);
    
        <insert id="insert" useGeneratedKeys="true" keyProperty="id">
            insert into department(name) values(#{name})
        </insert>

EmployeeMapper：

int insert(@Param("employee") Employee employee);
    
        <insert id="insert" useGeneratedKeys="true" keyProperty="id">
            insert into Employee(name,dept_id) values (#{employee.name},#{employee.department.id})
        </insert>

测试类：

@Test
        public void testSave() throws Exception {
            Department department = new Department();
            department.setName("开发部");
    
            Employee employee1 = new Employee();
            employee1.setName("lisi");
            employee1.setDepartment(department);
            Employee employee2 = new Employee();
            employee2.setName("zs");
            employee2.setDepartment(department);
    
            SqlSession session = MyBatisUtil.getSession();
            DepartmentMapper departmentMapper = session.getMapper(DepartmentMapper.class);
            EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
    
    
            departmentMapper.insert(department);
            employeeMapper.insert(employee1);
            employeeMapper.insert(employee2);
    
            // 提交事务
            session.commit();
            session.close();
        }

**2、查询**

1）额外SQL

DepartmentMapper：

Department get(@Param("id") Long id);
    
        <select id="get" resultType="cn.jq.jqmybatis.model.Department">
            select id,name from department where id = #{id}
        </select>

EmployeeMapper：

Employee get(@Param("id") Long id);
    
        <resultMap id="baseMap" type="cn.jq.jqmybatis.model.Employee">
            <id column="id" property="id"/>
            <result column="name" property="name"/>
            <result column="dept_id" property="department.id"/>
        </resultMap>
    
        <select id="get" resultMap="baseMap">
            select id,name,dept_id from Employee where id = #{id}
        </select>

测试类：

@Test
        public void testGet() throws Exception {
            SqlSession session = MyBatisUtil.getSession();
            DepartmentMapper departmentMapper = session.getMapper(DepartmentMapper.class);
            EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
    
            Employee employee = employeeMapper.get(1L);
            Department department = departmentMapper.get(employee.getDepartment().getId());
            employee.setDepartment(department);
    
            System.out.println(employee);
            session.close();
        }

![201910201903501.png][]

也可以使用分步关联查询，让 MyBatis帮我们自己查：

<resultMap id="baseMap" type="cn.jq.jqmybatis.model.Employee">
            <id column="id" property="id"/>
            <result column="name" property="name"/>
            <association property="department" column="dept_id" select="cn.jq.jqmybatis.dao.DepartmentMapper.get">
            </association>
        </resultMap>
    
        <select id="get" resultMap="baseMap">
            select id,name,dept_id from Employee where id = #{id}
        </select>
    
        @Test
        public void testGet() throws Exception {
            SqlSession session = MyBatisUtil.getSession();
            DepartmentMapper departmentMapper = session.getMapper(DepartmentMapper.class);
            EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
    
            Employee employee = employeeMapper.get(1L);
    //        Department department = departmentMapper.get(employee.getDepartment().getId());
    //        employee.setDepartment(department);
    
            System.out.println(employee);
            session.close();
        }

![20191020190544743.png][]

2）内联映射

DepartmentMapper：不需要写

EmployeeMapper：

Employee get(@Param("id") Long id);
    
       <resultMap id="baseMap" type="cn.jq.jqmybatis.model.Employee">
            <id column="id" property="id"/>
            <result column="name" property="name"/>
            <association property="department" javaType="cn.jq.jqmybatis.model.Department" >
                <id column="d_id" property="id"/>
                <result column="d_name" property="name"/>
            </association>
        </resultMap>
    
        <select id="get" resultMap="baseMap">
            select e.id,e.name,d.id AS d_id,d.name d_name from Employee e left join department d on e.dept_id = d.id where e.id = #{id}
        </select>

测试类：

@Test
        public void testGet() throws Exception {
            SqlSession session = MyBatisUtil.getSession();
            DepartmentMapper departmentMapper = session.getMapper(DepartmentMapper.class);
            EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
    
            Employee employee = employeeMapper.get(1L);
    
            System.out.println(employee);
            session.close();
        }

![20191020191241652.png][]

### 二、一对多 ###

在设计的时候，我们一般考虑使用单向的many2one，一般不使用单向的one2many，即使要使用one2many，也应该做成双向关联或者在many方存储one方的ID。

案例：一个部门对象包含多个员工对象

java pojo类设计

![20191020193833612.png][]

表设计（外键在 many方）：同上

**1、新增保存**

DepartmentMapper：

int insert(Department department);
    
        <insert id="insert" useGeneratedKeys="true" keyProperty="id">
            insert into department(name) values(#{name})
        </insert>

EmployeeMapper：

测试类：

@Test
        public void testSave() throws Exception {
            Department department = new Department();
            department.setName("开发部");
    
            Employee employee1 = new Employee();
            employee1.setName("lisi");
            Employee employee2 = new Employee();
            employee2.setName("zs");
    
            SqlSession session = MyBatisUtil.getSession();
            DepartmentMapper departmentMapper = session.getMapper(DepartmentMapper.class);
            EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
    
            departmentMapper.insert(department);
    
            employee1.setDeptId(department.getId());
            employee2.setDeptId(department.getId());
            employeeMapper.insert(employee1);
            employeeMapper.insert(employee2);
    
            // 提交事务
            session.commit();
            session.close();
        }

**2、查询**

1）额外SQL

DepartmentMapper：

EmployeeMapper：

Employee getByDeptId(@Param("deptId") Long deptId);
    
        <select id="getByDeptId" resultMap="baseMap">
            select id,name,dept_id deptId from Employee where dept_id = #{deptId}
        </select>

测试类：

@Test
        public void testGet() throws Exception {
            SqlSession session = MyBatisUtil.getSession();
            DepartmentMapper departmentMapper = session.getMapper(DepartmentMapper.class);
            EmployeeMapper employeeMapper = session.getMapper(EmployeeMapper.class);
    
            Department department = departmentMapper.get(2L);
            System.out.println(department);
    
            session.close();
        }

![20191020195124662.png][]

2）内联映射

DepartmentMapper：

Department get(@Param("id") Long id);
    
        <resultMap id="baseMap" type="cn.jq.jqmybatis.model.Department">
            <id column="id" property="id"/>
            <result column="name" property="name"/>
            <collection property="employees" ofType="cn.jq.jqmybatis.model.Employee">
                <id column="e_id" property="id"/>
                <result column="e_name" property="name"/>
                <result column="id" property="deptId"/>
            </collection>
        </resultMap>
    
        <select id="get" resultMap="baseMap">
            select d.id,d.name,e.id AS e_id,e.name e_name from department d left join Employee e on d.id = e.dept_id where d.id = #{id}
        </select>

EmployeeMapper：不需要写

测试类：同上   ![2019102019575458.png][]

### 三、多对多 ###

多对多：一个A对象属于多个B对象，一个B对象属于多个A对象（单向关系）。

java pojo类设计

![20191020202157325.png][]

表和中间表设计：

CREATE TABLE `student` (
      `id` bigint(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(255) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
    
    CREATE TABLE `teacher` (
      `id` bigint(11) NOT NULL AUTO_INCREMENT,
      `name` varchar(11) DEFAULT NULL,
      PRIMARY KEY (`id`)
    ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;
    
    CREATE TABLE `student_teacher` (
      `student_id` bigint(11) NOT NULL,
      `teacher_id` bigint(11) NOT NULL
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

**1、新增保存**

StudentMapper：

int insert(Student student);
        int insertRelatino(@Param("studentId") Long studentId, @Param("teacherId") Long teacherId);
    
        <insert id="insert" useGeneratedKeys="true" keyProperty="id">
            insert into student(name) values (#{name});
        </insert>
        <insert id="insertRelatino">
            insert into student_teacher(student_id,teacher_id) values (#{studentId}, #{teacherId})
        </insert>

TeacherMapper：

int insert(Teacher teacher);
    
        <insert id="insert" useGeneratedKeys="true" keyProperty="id">
            insert into teacher(name) values (#{name})
        </insert>

测试类：

@Test
        public void testSave() throws Exception {
            Student s1 = new Student();
            s1.setName("s1");
            Student s2 = new Student();
            s2.setName("s2");
    
            Teacher t1 = new Teacher();
            t1.setName("t1");
            Teacher t2 = new Teacher();
            t2.setName("t2");
    
            // 维护对象之前的关系
            s1.getTeachers().add(t1);
            s1.getTeachers().add(t2);
    
            s2.getTeachers().add(t1);
            s2.getTeachers().add(t2);
    
            SqlSession session = MyBatisUtil.getSession();
            StudentMapper studentMapper = session.getMapper(StudentMapper.class);
            TeacherMapper teacherMapper = session.getMapper(TeacherMapper.class);
    
            studentMapper.insert(s1);
            studentMapper.insert(s2);
            teacherMapper.insert(t1);
            teacherMapper.insert(t2);
    
            //维护中间表的数据
            for (Teacher teacher : s1.getTeachers()) {
                studentMapper.insertRelatino(s1.getId(),teacher.getId());
            }
            for (Teacher teacher : s2.getTeachers()) {
                studentMapper.insertRelatino(s2.getId(),teacher.getId());
            }
    
            // 提交事务
            session.commit();
            session.close();
        }

**2、查询**

1）额外SQL

StudentMapper：

Student get(Long id);
    
        <resultMap id="baseMap" type="cn.jq.jqmybatis.model.Student">
            <id column="id" property="id"/>
            <result column="name" property="name"/>
            <collection property="teachers" select="cn.jq.jqmybatis.dao.TeacherMapper.get" column="id"/>
        </resultMap>
        <select id="get" resultMap="baseMap">
            select id,name from student where id = #{id}
        </select>

TeacherMapper：

<select id="get" resultType="cn.jq.jqmybatis.model.Teacher">
            select t.id,t.name from teacher t left join student_teacher st on t.id = st.teacher_id where t.id= #{id}
        </select>

测试类：

@Test
        public void testGet() throws Exception {
            SqlSession session = MyBatisUtil.getSession();
            StudentMapper studentMapper = session.getMapper(StudentMapper.class);
    
            Student student = studentMapper.get(1L);
            System.out.println(student);
    
            session.close();
        }

![20191020204506694.png][]

2）内联映射

StudentMapper：

Student get(Long id);
    
        <resultMap id="baseMap" type="cn.jq.jqmybatis.model.Student">
            <id column="id" property="id"/>
            <result column="name" property="name"/>
            <collection property="teachers" ofType="cn.jq.jqmybatis.model.Teacher" columnPrefix="t_">
                <id column="id" property="id"/>
                <result column="name" property="name"/>
            </collection>
        </resultMap>
        <select id="get" resultMap="baseMap">
            select s.id,s.name,t.id t_id,t.name t_name
            from student s left join student_teacher st on s.id = st.student_id
                left join teacher t on st.teacher_id = t.id where s.id = #{id}
        </select>

TeacherMapper：不需要写

测试类：同上

![20191020204941768.png][]

**3、删除Student**

StudentMapper：

int delete(Long id);
        int deleteRelatino(@Param("studentId") Long studentId);
    
        <delete id="delete">
            delete from student where id = #{id}
        </delete>
        <delete id="deleteRelatino">
            delete from student_teacher where  student_id = #{studentId}
        </delete>

测试类：

@Test
        public void testDelete() throws Exception {
            SqlSession session = MyBatisUtil.getSession();
            StudentMapper studentMapper = session.getMapper(StudentMapper.class);
    
            //先删除关联关系
            studentMapper.deleteRelatino(1L);
            studentMapper.delete(1L);
    
            // 提交事务
            session.commit();
            session.close();
        }

**额外SQL和内联映射查询的选择：**

在开发中，多对一的关联关系，一般都是在列表中显示时或单对象类型，通常直接使用多表查询，即内联查询

如果在当前页不显示数据，需要点击进入另一个页面再显示数据时或集合对象类型，使用额外SQL方式，即分步关联查询

select查询可参考这篇文章：[MyBatis select查询(resultType/resultMap)和延迟加载][MyBatis select_resultType_resultMap]

对象关系设计可参考这篇文章：[对象关系设计][Link 1]

站在前辈的肩膀上，每天进步一点点

ends~

[20191020184536168.png]: /images/20230531/6cef3a421c7f42b6a68924322550029b.png
[201910201903501.png]: /images/20230531/cb4202b638db4877947b9eb7145239b3.png
[20191020190544743.png]: /images/20230531/b0cee4cced2245d9b263b9dee2c5ed9c.png
[20191020191241652.png]: /images/20230531/447cb78365384ce897f5112e2b02f462.png
[20191020193833612.png]: /images/20230531/dd76900e898d47158063666fb6ddd0ea.png
[20191020195124662.png]: /images/20230531/1617de44eeda430493b2d83d3f90c3d7.png
[2019102019575458.png]: /images/20230531/c2877ab39584466abdff7c9b60745a69.png
[20191020202157325.png]: /images/20230531/620670139bfb4179af37d3577e3eec4c.png
[20191020204506694.png]: /images/20230531/b6f22a67f0304d30afe960622cd006da.png
[20191020204941768.png]: /images/20230531/3f743199a4af49f89e576ce931e896d4.png
[MyBatis select_resultType_resultMap]: https://blog.csdn.net/qq_42402854/article/details/84111051
[Link 1]: https://blog.csdn.net/qq_42402854/article/details/102649380