在 MyBatis：一对一和一对多的实现 中我们提到：
什么是多对多？一个老师有多个学生，一个学生有多个老师，学生和老师之间就是多对多。
多对多其实就是两个一对多组合在一起。但实现上稍微麻烦，以老师和学生为例子，除了在数据库中建立老师表、学生表，还要建立老师和学生之间的关系表。然后映射 SQL 代码的写法和一对多类似。
我们以下图中的学生-老师关系为例，学习下如何实现多对多。
本节示例代码在 mybatis-demo-017 。
数据准备
创建学生表、老师表：
-- 创建数据库 school
DROP DATABASE IF EXISTS `school`;
CREATE DATABASE `school` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
-- 创建学生表
CREATE TABLE `school`.`student` (
`id` BIGINT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_general_ci;
-- 学生表添加数据
INSERT INTO `school`.`student` (`name`) VALUES ('张三'),('李四'),('王五');
-- 创建老师表
CREATE TABLE `school`.`teacher` (
`id` BIGINT NOT NULL AUTO_INCREMENT ,
`name` VARCHAR(45) NOT NULL ,
PRIMARY KEY (`id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_general_ci;
-- 老师表插入数据
INSERT INTO `school`.`teacher` (`name`) VALUES ('刘老师'),('李老师');
查看学生表、老师表中的数据：
mysql> select * from school.student;
+----+--------+
| id | name |
+----+--------+
| 1 | 张三 |
| 2 | 李四 |
| 3 | 王五 |
+----+--------+
3 rows in set (0.02 sec)
mysql> select * from school.teacher;
+----+-----------+
| id | name |
+----+-----------+
| 1 | 刘老师 |
| 2 | 李老师 |
+----+-----------+
2 rows in set (0.00 sec)
建立关系表，并插入数据：
-- 创建关系表
CREATE TABLE `school`.`relationship` (
`student_id` BIGINT NOT NULL,
`teacher_id` BIGINT NOT NULL ,
INDEX `idx_student_id` (`student_id`),
INDEX `idx_teacher_id` (`teacher_id`)
)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8mb4
COLLATE = utf8mb4_general_ci;
-- 插入数据
INSERT INTO `school`.`relationship` (`student_id`, `teacher_id`)
VALUES
(1, 1),
(2, 1),
(2, 2),
(3, 2);
查看数据：
mysql> select * from `school`.`relationship`;
+------------+------------+
| student_id | teacher_id |
+------------+------------+
| 1 | 1 |
| 2 | 1 |
| 2 | 2 |
| 3 | 2 |
+------------+------------+
4 rows in set (0.00 sec)
上面的的结果可读性太差，我们换种方式查询：
use school;
SELECT
student.id AS student_id,
student.name AS student_name,
teacher.id AS teacher_id,
teacher.name AS teacher_name
FROM
relationship,
student,
teacher
WHERE
relationship.student_id = student.id
AND relationship.teacher_id = teacher.id
;
查询结果是：
+------------+--------------+------------+--------------+
| student_id | student_name | teacher_id | teacher_name |
+------------+--------------+------------+--------------+
| 1 | 张三 | 1 | 刘老师 |
| 2 | 李四 | 1 | 刘老师 |
| 2 | 李四 | 2 | 李老师 |
| 3 | 王五 | 2 | 李老师 |
+------------+--------------+------------+--------------+
然后，我们按照类似 MyBatis：一对一和一对多的实现的思路编写代码。
增加Bean类
在 bean package 下新增两张表对应的 bean类：
package bean;
import lombok.Data;
import java.util.List;
@Data
public class Student {
private Long id;
private String name;
private List<Teacher> teacherList;
}
package bean;
import lombok.Data;
import java.util.List;
@Data
public class Teacher {
private Long id;
private String name;
private List<Student> studentList;
}
增加映射类和映射XML
在 mapper package 下增加 StudentMapper 接口：
package mapper;
import bean.Student;
public interface StudentMapper {
// 根据 id 查找学生
Student findById(Long id);
}
对应的 StudentMapper.xml 如下：
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC
"-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mapper.StudentMapper">
<resultMap id="studentResult" type="bean.Student">
<result property="id" column="student_id"/>
<result property="name" column="student_name"/>
<collection property="teacherList" ofType="bean.Teacher">
<result property="id" column="teacher_id"/>
<result property="name" column="teacher_name"/>
</collection>
</resultMap>
<select id="findById" parameterType="Long" resultMap="studentResult" resultType="bean.Student">
SELECT
student.id AS student_id,
student.name AS student_name,
teacher.id AS teacher_id,
teacher.name AS teacher_name
FROM student, teacher, relationship
WHERE
student.id = #{id}
AND student.id = relationship.student_id
AND relationship.teacher_id = teacher.id
</select>
</mapper>
在 mapper package 下增加 TeacherMapper 接口：
package mapper;
import bean.Teacher;
public interface TeacherMapper {
// 根据 id 查找老师
Teacher findById(Long id);
}
对应的 TeacherMapper.xml 如下：
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper PUBLIC
"-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="mapper.TeacherMapper">
<resultMap id="teacherResult" type="bean.Teacher">
<result property="id" column="teacher_id"/>
<result property="name" column="teacher_name"/>
<collection property="studentList" ofType="bean.Student">
<result property="id" column="student_id"/>
<result property="name" column="student_name"/>
</collection>
</resultMap>
<select id="findById" parameterType="Long" resultMap="teacherResult" resultType="bean.Teacher">
SELECT
teacher.id AS teacher_id,
teacher.name AS teacher_name,
student.id AS student_id,
student.name AS student_name
FROM teacher, student, relationship
WHERE
teacher.id = #{id}
AND teacher.id = relationship.teacher_id
AND relationship.student_id = student.id
</select>
</mapper>
编写示例代码
在 Main 类中增加查找学生及其所有老师的示例：
@Slf4j
public class Main {
@Test
public void test_01() throws IOException {
try (SqlSession sqlSession = getSqlSession()) {
StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
log.info("{}", studentMapper.findById(1L));
log.info("{}", studentMapper.findById(2L));
}
}
private SqlSession getSqlSession() throws IOException {
SqlSessionFactoryBuilder sqlSessionFactoryBuilder = new SqlSessionFactoryBuilder();
SqlSessionFactory sessionFactory;
sessionFactory = sqlSessionFactoryBuilder.build(
Resources.getResourceAsReader("mybatis-config.xml"),
"development"
);
return sessionFactory.openSession();
}
}
执行 test_01 方法，结果是：
INFO [main] - Student(id=1, name=张三, teacherList=[Teacher(id=1, name=刘老师, studentList=null)])
INFO [main] - Student(id=2, name=李四, teacherList=[Teacher(id=1, name=刘老师, studentList=null), Teacher(id=2, name=李老师, studentList=null)])
再增加查找老师及其所有学生的示例：
@Test
public void test_02() throws IOException {
try (SqlSession sqlSession = getSqlSession()) {
TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
log.info("{}", teacherMapper.findById(1L));
}
}
执行结果如下：
INFO [main] - Teacher(id=1, name=刘老师, studentList=[Student(id=1, name=张三, teacherList=null), Student(id=2, name=李四, teacherList=null)])