MyBatis 教程

文章列表
简介 准备工作 回顾 JDBC 数据准备 查找id为1的用户信息 自定义连接池 不用MyBatis配置文件 查询密码为123的所有用户 如果Bean中成员变量和表中字段命名不一致 更多查询用户的方式 对查询结果排序 日志 添加、删除、修改数据 事务 动态SQL 一对一和一对多的实现 一对一和一对多的延迟加载 多对多的实现 分页查询 把SQL写在注解中 自动生成Mapper代码和映射XML mybatis generator 生成 select for update mybatis generator 支持数据版本号

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

( 本文完 )

文章目录