提供了编程的基础技术教程

zl程序教程

您现在的位置是：首页 >  其他

当前栏目

Mybatis关联查询【附实战案例】

案例mybatis 查询 实战 关联
2023-06-13 09:18:47 时间

一、Mybatis一对一关联查询

        查询学生时，将关联的一个班级对象查询出来，就是一对一关联查询。这里还新增了几个实体类

新增Classes实体类

package com.example.pojo;

import java.util.List;

public class Classes {
    private int cid;
    private String className;
    private List<Student> studentList;
    private List<Teacher> teacherList;

    public int getCid() {
        return cid;
    }

    public void setCid(int cid) {
        this.cid = cid;
    }

    public String getClassName() {
        return className;
    }

    public void setClassName(String className) {
        this.className = className;
    }

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

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

    public List<Teacher> getTeacherList() {
        return teacherList;
    }

    public void setTeacherList(List<Teacher> teacherList) {
        this.teacherList = teacherList;
    }

    @Override
    public String toString() {
        return "Classes[ " +
                "cid=" + cid +
                ", className='" + className + '\'' +
                ", studentList=" + studentList + '\'' +
                ", teacherList=" + teacherList +
                " ]";
    }
}

新增Student实体类

package com.example.pojo;

public class Student {
    private int sid;
    private String name;
    private int age;
    private String sex;
    private Classes classes;

    public int getSid() {
        return sid;
    }

    public void setSid(int sid) {
        this.sid = sid;
    }

    public String getName() {
        return name;
    }

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

    public int getAge() {
        return age;
    }

    public void setAge(int age) {
        this.age = age;
    }

    public String getSex() {
        return sex;
    }

    public void setSex(String sex) {
        this.sex = sex;
    }

    public Classes getClasses() {
        return classes;
    }

    public void setClasses(Classes classes) {
        this.classes = classes;
    }

    @Override
    public String toString() {
        return "Student[ " +
                "sid=" + sid +
                ", name='" + name + '\'' +
                ", age=" + age +
                ", sex='" + sex + '\'' +
                ", classes=" + classes +
                " ]";
    }
}

新增Teacher实体类 

package com.example.pojo;

import java.util.List;

public class Teacher {
    private Integer tid;
    private String tname;
    private List<Classes> classes;

    public Integer getTid() {
        return tid;
    }

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

    public String getTname() {
        return tname;
    }

    public void setTname(String tname) {
        this.tname = tname;
    }

    public List<Classes> getClasses() {
        return classes;
    }

    public void setClasses(List<Classes> classes) {
        this.classes = classes;
    }

    @Override
    public String toString() {
        return "Teacher[ " +
                "tid=" + tid +
                ", tname='" + tname + '\'' +
                ", classes=" + classes +
                " ]";
    }
}

1. 新增持久层接口方法

package com.example.mapper;

import com.example.pojo.Student;

import java.util.List;

public interface StudentMapper {
    List<Student> findAll();
}

2. 新增映射文件对应的标签

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.example.mapper.StudentMapper">
    <resultMap id="studentMapper" type="com.example.pojo.Student">
        <!-- 主键列 -->
        <id property="sid" column="sid"></id>
        <!-- 普通列 -->
        <result property="name" column="name"></result>
        <result property="age" column="age"></result>
        <result property="sex" column="sex"></result>
        <!-- 一对一对象列 property:属性名 column:关联列名 javaType：对象类型 -->
        <association property="classes" column="classId" javaType="com.example.pojo.Classes">
            <!-- 关联对象主键列 -->
            <id property="cid" column="cid"></id>
            <!-- 关联对象普通列 -->
            <result property="className" column="className"></result>
        </association>
    </resultMap>
    <!-- 多表查询，级联查询学生和其他班级 -->
    <select id="findAll" resultMap="studentMapper">
        select * from student left join classes on student.classId = classes.cid;
    </select>
</mapper>

3. 新增测试方法

    @Test
    public void testFindAll(){
        StudentMapper studentMapper = session.getMapper(StudentMapper.class);
        List<Student> all = studentMapper.findAll();
        all.forEach(System.out::println);
    }

4. 运行效果

Ok，从运行效果来看确实查询出来每个学生对应的班级了 

二、Mybatis一对多关联查询

查询班级时，将关联的学生集合查询出来，就是一对多关联查询。

1. 新增持久层接口方法

package com.example.mapper;

import com.example.pojo.Classes;

import java.util.List;

public interface ClassesMapper {
    List<Classes> findAll();
}

2. 新增映射文件对应的标签

<?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.example.mapper.ClassesMapper">
    <resultMap id="classesMapper" type="com.example.pojo.Classes">
        <id property="cid" column="cid"></id>
        <result property="className" column="className"></result>
        <!-- 集合列 property:属性名  column：关联列名  ofType:集合的泛型 -->
        <collection property="studentList" column="classId" ofType="com.example.pojo.Student">
            <id property="sid" column="sid"></id>
            <result property="name" column="name"></result>
            <result property="age" column="age"></result>
            <result property="sex" column="sex"></result>
        </collection>
    </resultMap>

    <!-- 多表查询，级联查询班级和它的学生 -->
    <select id="findAll" resultMap="classesMapper">
        select * from classes left join student on classes.cid = student.classId;
    </select>
</mapper>

3. 新增测试方法

// 测试查询一对多关联查询
    @Test
    public void testFindAllClasses(){
        ClassesMapper classesMapper = session.getMapper(ClassesMapper.class);
        List<Classes> all = classesMapper.findAll();
        all.forEach(System.out::println);
    }

4. 运行效果

OK，观察运行效果，确实是将每个班级对应的学生都查询出来了。 

三、Mybatis多对多关联查询

MyBatis多对多关联查询本质就是两个一对多关联查询。 例如有老师类和班级类： 一个老师对应多个班级，也就是老师类中有一个班级集合属性。 一个班级对应多个老师，也就是班级类中有一个老师集合属性。 本次我们的目的就是查询各个老师对应所教的的班级，并且各个班级的老师也一并查询出来，这里有点拗口。稍加理解即可。

1. 新增持久层接口方法

package com.example.mapper;

import com.example.pojo.Teacher;

import java.util.List;

public interface TeacherMapper {
    List<Teacher> findAll();
}

2. 新增映射文件对应的标签

<?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.example.mapper.TeacherMapper">
    <resultMap id="teacherMapper" type="com.example.pojo.Teacher">
        <id property="tid" column="tid"></id>
        <result property="tname" column="tname"></result>
        <collection property="classes" column="tid" ofType="com.example.pojo.Classes">
            <id property="cid" column="cid"></id>
            <result column="className" property="className"></result>
        </collection>
    </resultMap>
    <select id="findAll" resultMap="teacherMapper">
        select * from teacher left join classes_teacher
            on teacher.tid = classes_teacher.tid
            left join classes
            on classes_teacher.cid = classes.cid
    </select>
</mapper>

3. 新增测试方法

// 测试多对多关联查询
    @Test
    public void testFindAllTeacher(){
        TeacherMapper teacherMapper = session.getMapper(TeacherMapper.class);
        List<Teacher> all = teacherMapper.findAll();
        all.forEach(System.out::println);
    }

4. 运行效果

        OK，这里也是可以查询出每个老师教的班级，一个班级有多个老师，一个老师可以对应多个 班级，这就是多对多查询

四、进阶查询班级信息

        因为在前面，我们查询所有班级信息的时候，老师列表是空的，现在如果想查询班级时，将关联的老师集合查询出来，只需要修改班级映射文件的Sql语句和 <resultMap> 即可：

1. 新增持久层接口方法

List<Classes> findAll1();

2. 新增映射文件对应的标签

    <!--如果想查询班级时，将关联的老师集合查询出来，只需要修改班级
映射文件的Sql语句和 <resultMap> 即可：-->
    <resultMap id="classesMapper1" type="com.example.pojo.Classes">
        <id property="cid" column="cid"></id>
        <result property="className" column="className"></result>
        <!-- 集合列 property:属性名  column：关联列名  ofType:集合的泛型 -->
        <collection property="studentList" column="classId" ofType="com.example.pojo.Student">
            <id property="sid" column="sid"></id>
            <result property="name" column="name"></result>
            <result property="age" column="age"></result>
            <result property="sex" column="sex"></result>
        </collection>
        <collection property="teacherList" column="cid" ofType="com.example.pojo.Teacher">
            <id property="tid" column="tid"></id>
            <result property="tname" column="tname"></result>
        </collection>
    </resultMap>

    <!-- 多表查询，级联查询班级和它的学生 -->
    <select id="findAll1" resultMap="classesMapper1">
        select * from classes
            left join student
                on classes.cid = student.classId
            left join classes_teacher
                on classes.cid = classes_teacher.cid
            left join teacher
                on classes_teacher.tid = teacher.tid;
    </select>

3. 新增测试方法

    @Test
    public void testFindAllClasses1(){
        ClassesMapper classesMapper = session.getMapper(ClassesMapper.class);
        List<Classes> all = classesMapper.findAll1();
        all.forEach(System.out::println);
    }

4. 运行效果

OK，可以看到查询班级信息的时候确实将老师列表也查询出来了。

相关文章