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 | 李老师       |
+------------+--------------+------------+--------------+

然后,我们按照类似 一对一和一对多的实现 的思路编写代码。

增加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)])


(本文完)

MyBatis 教程