MyBatis一对一、一对多、多对一、多对多

1 实验描述

三个实体：学生，班级，课程
一对一： 一个学生 对应 一个班级
一对多： 一个班级 对应 多个学生
多对一： 多个学生 对应 一个班级
多对多： 多个课程 对应 多个学生

2 Mysql创建数据表

在数据库test下创建数据表
学生表：tb_student
班级表：tb_clazz
课程表：tb_course
学生课程表：tb_student_course

CREATE TABLE `tb_clazz` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `tb_course`;
CREATE TABLE `tb_course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `tb_student`;
CREATE TABLE `tb_student` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `clazz_id` int(11) DEFAULT NULL,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `clazz_id` (`clazz_id`),
  CONSTRAINT `tb_student_ibfk_1` FOREIGN KEY (`clazz_id`) REFERENCES `tb_clazz` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8mb4;

DROP TABLE IF EXISTS `tb_student_course`;
CREATE TABLE `tb_student_course` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) DEFAULT NULL,
  `course_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `student_id` (`student_id`),
  KEY `course_id` (`course_id`),
  CONSTRAINT `tb_student_course_ibfk_1` FOREIGN KEY (`student_id`) REFERENCES `tb_student` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `tb_student_course_ibfk_2` FOREIGN KEY (`course_id`) REFERENCES `tb_course` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8mb4;

省略各表插入数据sql

3 编写实验项目

创建Maven项目，建立下图所示结构：

编辑 pom.xml 引入下列依赖：

<dependencies>
    <dependency>
        <groupId>org.mybatis</groupId>
        <artifactId>mybatis</artifactId>
        <version>3.5.4</version>
    </dependency>
    <dependency>
        <groupId>mysql</groupId>
        <artifactId>mysql-connector-java</artifactId>
        <version>8.0.18</version>
    </dependency>
    <dependency>
        <groupId>junit</groupId>
        <artifactId>junit</artifactId>
        <version>4.12</version>
        <scope>test</scope>
    </dependency>
</dependencies>

编辑 Clazz类：

public class Clazz {
    private int id;
    private String name;
    private List<Student> students;

    @Override
    public String toString() {
        return "Clazz{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", students=" + students.toString() +
                '}';
    }
    省略getter、setter
}

编辑 Course类：

public class Course {
    private int id;
    private String name;
    private List<Student> students;

    @Override
    public String toString() {
        return "Course{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", students=" + students.toString() +
                '}';
    }
    省略getter、setter
}

编辑 Student类：

public class Student {
    private int id;
    private String name;
    private Clazz clazz;
    private List<Course> courses;

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", clazz=" + clazz.getName() +
                '}';
    }
	省略getter、setter

编辑 ClazzMapper接口类：

public interface ClazzMapper {
    public Clazz findById(int id);
}

编辑 CourseMapper接口类：

public interface CourseMapper {
    public List<Course> findAll();
}

编辑 StudentMapper接口类：

public interface StudentMapper {
    public Student findById(int id);
    public List<Student> findByClazzId(int clazz_id);
}

编辑 mybatis-config.xml：

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE configuration PUBLIC "-//mybatis.org//DTD Config 3.0//EN" "http://mybatis.org/dtd/mybatis-3-config.dtd">
<configuration>
    <environments default="development">
        <environment id="development">
            <transactionManager type="JDBC"/>
            <!--在下面的value处填写自己的数据库连接信息-->
            <dataSource type="POOLED">
                <property name="driver" value="com.mysql.cj.jdbc.Driver"/>
                <property name="url" value="jdbc:mysql://127.0.0.1:3306/test?useSSL=false&amp;serverTimezone=UTC&amp;characterEncoding=utf-8"/>
                <property name="username" value="root"/>
                <property name="password" value="123456"/>
            </dataSource>
        </environment>
    </environments>
    <mappers>
        <mapper resource="mapper/StudentMapper.xml" />
        <mapper resource="mapper/ClazzMapper.xml" />
        <mapper resource="mapper/CourseMapper.xml" />
    </mappers>
</configuration>

编辑 ClazzMapper.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="com.mybatis_learn.mapper.ClazzMapper">
    <resultMap id="clazzResult" type="com.mybatis_learn.entity.Clazz">
        <id property="id" column="clazz_id"/>
        <result property="name" column="clazz_name"/>
        <collection property="students" ofType="com.mybatis_learn.entity.Student">
            <id property="id" column="student_id" />
            <result property="name" column="student_name"/>
            <association property="clazz" javaType="com.mybatis_learn.entity.Clazz">
                <id property="id" column="clazz_id"/>
                <result property="name" column="clazz_name"/>
            </association>
        </collection>
    </resultMap>
    <select id="findById" resultMap="clazzResult">
        select
        a.id as clazz_id,
        a.name as clazz_name,
        b.id as student_id,
        b.name as student_name
        from tb_clazz as a
        LEFT JOIN tb_student as b ON a.id=b.clazz_id
        where a.id=#{id}
    </select>
</mapper>

编辑 CourseMapper.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="com.mybatis_learn.mapper.CourseMapper">
    <resultMap id="courseResult" type="com.mybatis_learn.entity.Course">
        <id property="id" column="course_id" />
        <result property="name" column="course_name"/>
        <collection property="students" ofType="com.mybatis_learn.entity.Student">
            <id property="id" column="student_id" />
            <result property="name" column="student_name"/>
            <association property="clazz" javaType="com.mybatis_learn.entity.Clazz">
                <id property="id" column="clazz_id"/>
                <result property="name" column="clazz_name"/>
            </association>
        </collection>
    </resultMap>
    <select id="findAll" resultMap="courseResult">
        select
        a.id as course_id,
        a.name as course_name,
        c.id as student_id,
        c.name as student_name,
        d.id as clazz_id,
        d.name as clazz_name
        from tb_course as a
        LEFT JOIN tb_student_course as b on a.id=b.course_id
        LEFT JOIN tb_student as c on b.student_id=c.id
        LEFT JOIN tb_clazz as d on c.clazz_id=d.id
    </select>
</mapper>

编辑 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="com.mybatis_learn.mapper.StudentMapper">
    <resultMap id="studentResult" type="com.mybatis_learn.entity.Student">
        <id property="id" column="student_id" />
        <result property="name" column="student_name"/>
        <association property="clazz" javaType="com.mybatis_learn.entity.Clazz">
            <id property="id" column="clazz_id"/>
            <result property="name" column="clazz_name"/>
        </association>
    </resultMap>
    <select id="findById" resultMap="studentResult">
        select
        a.id as student_id,
        a.name as student_name,
        a.clazz_id as student_clazz_id,
        b.id as clazz_id,
        b.name as clazz_name
        from tb_student as a
        left join tb_clazz as b on a.clazz_id=b.id
        where a.id=#{id}
    </select>
    <select id="findByClazzId" resultMap="studentResult">
        select
        a.id as student_id,
        a.name as student_name,
        a.clazz_id as student_clazz_id,
        b.id as clazz_id,
        b.name as clazz_name
        from tb_student as a
        left join tb_clazz as b on a.clazz_id=b.id
        where b.id=#{clazz_id}
    </select>
</mapper>

编写 test1测试类

public class test1 {

    public SqlSession sqlSession;

    @Before
    public void init() throws IOException {
        String resource = "mybatis-config.xml";
        // 构建xml配置文件输入流
        InputStream inputStream = Resources.getResourceAsStream(resource);
        // 通过SqlSessionFactoryBuilder创建SqlSessionFactory
        SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(inputStream);
        sqlSession = sqlSessionFactory.openSession();
    }

    @Test
    public void OneToOne(){
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        Student student = studentMapper.findById(1);
        System.out.println(student.toString());
        sqlSession.close();
    }

    @Test
    public void OneToMany(){
        ClazzMapper clazzMapper = sqlSession.getMapper(ClazzMapper.class);
        Clazz clazz = clazzMapper.findById(1);
        System.out.println(clazz.toString());
    }

    @Test
    public void ManyToOne(){
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> students = studentMapper.findByClazzId(1);
        for(Student student : students){
            System.out.println(student.toString());
        }
        sqlSession.close();
    }

    @Test
    public void ManyToMany(){
        CourseMapper courseMapper = sqlSession.getMapper(CourseMapper.class);
        List<Course> courses = courseMapper.findAll();
        for(Course course : courses){
            System.out.println(course.toString());
        }
    }
}

4 运行

使用JUnit进行测试
OneToOne 运行结果：

OneToMany 运行结果：

ManyToOne 运行结果：

ManyToMany 运行结果：

5 小结

JUnit中@Before标签所标记的方法，会在执行标记@Test标签方法前运行
此处 init()方法完成了读取mybatis配置文件创建会话的工作
MyBatis的使用，需熟练运用sql语句，并深入了解各种连接查询