MyBatis之一对多, 多对一 关联查询

Hello，大家好，本周博主为大家带来MyBatis中的一对多，多对一查询，关于MyBatis中查询的操作，下面步入正题

开发环境

IDE：IntelliJ IDEA

jdk:1.8

数据库：mysql 5.7

管理工具：Maven

技术栈：Java + MyBatis

需求

现有一个班级，班级内有多个学生，1个老师，该场景为多对一，一对多

要求使用MyBatis查出学生对应的老师，及每个老师教的学生。

数据表准备

teacher：

CREATE TABLE `teacher` (
  `id` int(10) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
)

student：

CREATE TABLE `student` (
  `id` int(10) NOT NULL,
  `name` varchar(30) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `tid` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fktid` (`tid`),
  CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) 

项目结构

项目搭建

MyBatis项目搭建传送门

实体类

Student

package com.wanshi.bean;

public class Student {

    private Integer id;
    private String name;
    private Teacher teacher;
    private Integer tid;

    public Student() {
    }

    public Student(Integer id, String name, Teacher teacher) {
        this.id = id;
        this.name = name;
        this.teacher = teacher;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Teacher getTeacher() {
        return teacher;
    }

    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }

    public Integer getTid() {
        return tid;
    }

    public void setTid(Integer tid) {
        this.tid = tid;
    }

    @Override
    public String toString() {
        return "Student{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", teacher=" + teacher +
                '}';
    }

}

Teacher

package com.wanshi.bean;

import java.util.List;

public class Teacher {

    private Integer id;
    private String name;

    private List<Student> studentList;

    public Teacher() {
    }

    public Teacher(Integer id, String name, List<Student> studentList) {
        this.id = id;
        this.name = name;
        this.studentList = studentList;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<Student> getStudentList() {
        return studentList;
    }

    public void setStudentList(List<Student> studentList) {
        this.studentList = studentList;
    }

    @Override
    public String toString() {
        return "com.wanshi.bean.Teacher{" +
                "id=" + id +
                ", name='" + name + '\'' +
                ", studentList=" + studentList +
                '}';
    }
}

多对一

这里以学生对老师，多个学生对应一个老师，查询出每个学生对应的老师信息

第一种：关联查询

<!--    结果集映射，按照结果嵌套查询-->
    <select id="getStudentList" resultMap="StudentTeacher">
        select s.id sid, s.name sname, t.id tid, t.name tname from student s join teacher t on s.tid = t.id;
    </select>
    
    <resultMap id="StudentTeacher" type="student">
        <result property="id" column="sid" />
        <result property="name" column="sname"/>
        <association property="teacher" javaType="teacher">
            <result property="name" column="tname"/>
        </association>
    </resultMap>

第二种：子查询

<!--    子查询方式实现多对一查询-->
    <select id="getStudentList2" resultType="student" resultMap="getStudentTeacher">
        select * from student
    </select>

    <resultMap id="getStudentTeacher" type="student">
        <result property="id" column="id"/>
        <result property="name" column="name"/>
        <association column="tid" property="teacher" javaType="teacher" select="getTeacher"/>
    </resultMap>

    <select id="getTeacher" resultType="teacher">
        select * from teacher
    </select>

结果

两种结果均为下图所示

一对多

以老师对学生，一个老师对应多个学生，要求查询老师教的所有学生信息

第一种：关联查询

<!--    关联查询实现一对多查询-->
    <select id="getTeacherStudentList" parameterType="int" resultMap="TeacherStudent">
        select t.id tid, t.name tname, s.id sid, s.name sname from student s join teacher t on s.tid = t.id where t.id = #{tid}
    </select>

    <resultMap id="TeacherStudent" type="teacher">
        <result property="id" column="sid"/>
        <result property="name" column="tname"/>
<!--        实体类中有集合，使用collection标签-->
        <collection property="studentList" ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
        </collection>
    </resultMap>

第二种：子查询

<!--    子查询实现一对多查询-->
    <select id="getTeacherStudentList" resultMap="TeacherStudent">
        select * from teacher
        <where>
            <if test="tid != null">id = #{tid}</if>
        </where>
    </select>

    <resultMap id="TeacherStudent" type="teacher">
        <result property="id" column="id"/>
        <collection property="studentList" javaType="ArrayList" ofType="Student" select="getStudentByTeacherId" column="id"/>
    </resultMap>

    <select id="getStudentByTeacherId" resultType="student">
        select * from student
        <where>
            <if test="tid != null">tid = #{tid}</if>
        </where>
    </select>

结果

两种方式，建议使用关联查询

结语

本周分享到此结束了，如果有问题的小伙伴可在评论区留言，若有不对之处，望各位大佬在评论区留言，我们共同进步，每周一个小案例，虚心学习，学习路上我们共行，加油，我们下周见，MyBatis系列文章！