直接写demo，这里表都没加外键约束，开发中通过 java代码维护
1、N+1问题？
N+1问题来源于数据库中常见的级联技术，即 N个数据库表形成关联关系，当再增加一个关联表时，也就是 N+1个级联关系，
由于某些时候，我们并不需要加载数据库的所有数据，而是某一个数据库表中数据，这时 Mybatis会自动加载所有表的数据，多执行几条无关sql语句，会造成数据库资源的浪费以及系统性能的下降，这就是级联表的缺点。
解决方案：全局定义延迟加载。
延迟加载会解决上述的 N+1问题，也就是在 N+1个级联表的情况下，只加载需求的数据库表数据。这是互联网发展的需求，性能提升的途径。
<settings>
<!-- 开启延迟加载 -->
<setting name="lazyLoadingEnabled" value="true"/>
<!-- 禁用积极延迟加载 -->
<setting name="aggressiveLazyLoading" value="false"/>
<!-- 延迟加载触发方法 -->
<setting name="lazyLoadTriggerMethods" value="clone"/>
</settings>
下面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 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();
} 
也可以使用分步关联查询，让 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();
} 
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" ><!-- columnPrefix="d_" 可以统一设置列的别名-->
<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();
} 
二、一对多
在设计的时候，我们一般考虑使用单向的many2one，一般不使用单向的one2many，即使要使用one2many，也应该做成双向关联或者在many方存储one方的ID。
案例：一个部门对象包含多个员工对象
java pojo类设计
表设计（外键在 many方）：同上
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.deptId})
</insert>测试类：
@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);
<resultMap id="baseMap" type="cn.jq.jqmybatis.model.Department">
<id column="id" property="id"/>
<result column="name" property="name"/>
<collection property="employees" select="cn.jq.jqmybatis.dao.EmployeeMapper.getByDeptId" column="id"/>
</resultMap>
<select id="get" resultMap="baseMap">
select id,name from department where id = #{id}
</select>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();
} 
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：不需要写
测试类：同上 
三、多对多
多对多：一个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 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();
} 
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：不需要写
测试类：同上
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)和延迟加载
对象关系设计可参考这篇文章：对象关系设计
站在前辈的肩膀上，每天进步一点点
ends~
478
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
