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

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

1、N+1问题？

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

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

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

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

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

一、多对一

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

    java pojo类设计

   

    表设计（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 into department(name) values(#{name})
    

EmployeeMapper：

    int insert(@Param("employee") Employee employee);

    
        insert into Employee(name,dept_id) values (#{employee.name},#{employee.department.id})
    

测试类：

    @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,name from department where id = #{id}
    

EmployeeMapper：

    Employee get(@Param("id") Long id);

    
        
        
        
    

    
        select id,name,dept_id from Employee where id = #{id}
    

测试类：

    @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();
    }

  

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

    
        
        
        
        
    

    
        select id,name,dept_id from Employee where id = #{id}
    

    @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();
    }

   

2）内联映射

DepartmentMapper：不需要写

EmployeeMapper：

    Employee get(@Param("id") Long id);

   
        
        
        
            
            
        
    

    
        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}
    

测试类：

    @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();
    }

 

二、一对多

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

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

    java pojo类设计

    

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

1、新增保存

DepartmentMapper：

    int insert(Department department);

    
        insert into department(name) values(#{name})
    

EmployeeMapper：

    int insert(@Param("employee") Employee employee);

    
        insert into Employee(name,dept_id) values (#{employee.name},#{employee.deptId})
    

测试类：

    @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：

    Department get(@Param("id") Long id);

    
        
        
        
    
    
    
        select id,name from department where id = #{id}
    

EmployeeMapper：

    Employee getByDeptId(@Param("deptId") Long deptId);

    
        select id,name,dept_id deptId from Employee where dept_id = #{deptId}
    

测试类：

    @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();
    }

  

2）内联映射

DepartmentMapper：

    Department get(@Param("id") Long id);

    
        
        
        
            
            
            
        
    

    
        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}
    

EmployeeMapper：不需要写

测试类：同上   

三、多对多

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

    java pojo类设计

   

    表和中间表设计：

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 into student(name) values (#{name});
    
    
        insert into student_teacher(student_id,teacher_id) values (#{studentId}, #{teacherId})
    

TeacherMapper：

    int insert(Teacher teacher);

    
        insert into teacher(name) values (#{name})
    

测试类：

    @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);

    
        
        
        
    
    
        select id,name from student where id = #{id}
    

TeacherMapper：

    
        select t.id,t.name from teacher t left join student_teacher st on t.id = st.teacher_id where t.id= #{id}
    

测试类：

    @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();
    }

 

2）内联映射

StudentMapper：

    Student get(Long id);

    
        
        
        
            
            
        
    
    
        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}
    

TeacherMapper：不需要写

测试类：同上

 

3、删除Student

StudentMapper：

    int delete(Long id);
    int deleteRelatino(@Param("studentId") Long studentId);

    
        delete from student where id = #{id}
    
    
        delete from student_teacher where  student_id = #{studentId}
    

测试类：

    @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)和延迟加载

对象关系设计可参考这篇文章：对象关系设计

 

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

ends~