[Java知识库] Mybatis学习笔记（九）：一对多及多对一查询

开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> Java知识库 -> Mybatis学习笔记（九）：一对多及多对一查询 -> 正文阅读

[Java知识库]Mybatis学习笔记（九）：一对多及多对一查询

一、前言

在之前的mybatis学习中，只有一些简单的查询，但是在业务开发的过程中，有大量的复杂查询，比如一对多及多对一等。本篇主要学习Mybatis如何处理一对多及多对一查询。

二、多对一

新建两张表，一张学生表，一张老师表。其中学生表中有字段tid为外键，关联老师表的主键。在这个场景下，是学生和老师的多对一查询。

2.1 建表语句

-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS "public"."student";
CREATE TABLE "public"."student" (
  "id" int4 NOT NULL,
  "name" varchar(255) COLLATE "pg_catalog"."default",
  "tid" int4
)
;
-- ----------------------------
-- Table structure for teacher
-- ----------------------------
DROP TABLE IF EXISTS "public"."teacher";
CREATE TABLE "public"."teacher" (
  "id" int4 NOT NULL,
  "name" varchar(255) COLLATE "pg_catalog"."default"
)
;
-- ----------------------------
-- Primary Key structure for table student
-- ----------------------------
ALTER TABLE "public"."student" ADD CONSTRAINT "student_pkey" PRIMARY KEY ("id");
-- ----------------------------
-- Primary Key structure for table teacher
-- ----------------------------
ALTER TABLE "public"."teacher" ADD CONSTRAINT "teacher_pkey" PRIMARY KEY ("id");
-- ----------------------------
-- Foreign Keys structure for table student
-- ----------------------------
ALTER TABLE "public"."student" ADD CONSTRAINT "fk_tid" FOREIGN KEY ("tid") REFERENCES "public"."teacher" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;

ER图：

插入测试数据：

INSERT INTO "public"."teacher"("id", "name") VALUES (1, '李老师');
INSERT INTO "public"."teacher"("id", "name") VALUES (2, '王老师');

INSERT INTO "public"."student"("id", "name", "tid") VALUES (1, '张三', 1);
INSERT INTO "public"."student"("id", "name", "tid") VALUES (2, '李四', 1);
INSERT INTO "public"."student"("id", "name", "tid") VALUES (3, '王五', 2);

2.2 实体类对象

Student：

public class Student {
    
    private int id;

    private String name;

    private Teacher teacher;
    
}

Teacher：

public class Teacher {

    private int id;

    private String name;

}

2.3 查询

现在查询学生信息和对应的老师，在数据库查询语句如下：

SELECT t1.id,t1.name,t2.name FROM student t1
LEFT JOIN teacher t2
on t1.tid = t2.id

结果如下：

现在使用mybatis查询：

mapper接口：

List<Student> selectStudentList();

xml实现：

<select id="selectStudentList" resultType="Student">
    SELECT * from student
</select>

junit测试：

@Test
public void selectStudentList() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    try {
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
        List<Student> studentList = studentMapper.selectStudentList();
        for (Student student : studentList) {
            System.out.println(student);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        sqlSession.close();
    }
}

查询结果：

Student{id=1, name='张三', teacher=null}
Student{id=2, name='李四', teacher=null}
Student{id=3, name='王五', teacher=null}

所有的学生都可以查出来了，但是学生对象里面的老师属性都是null的。

2.4 多对一的两种查询

2.4.1 嵌套查询

由上数据库查询可知，我们需要通过tid和id将学生和老师关联起来。

因此，写出根据id查出老师的语句：

<select id="getTeacherById" resultType="Teacher">
    select * from teacher where id = #{id}
</select>

将查询学生的selectStudentList接口的返回类型由Student改造一下：

<resultMap id="selectStudentMap" type="Student">
    <result property="id" column="id" />
    <result property="name" column="name" />
    <!--        复杂属性需要单独处理： 1. 对象：使用 association 2. 集合：使用 collection-->
    <association property="teacher"
                 column="tid"
                 javaType="Teacher"
                 select="getTeacher"/>

</resultMap>

<select id="selectStudentList" resultMap="selectStudentMap">
    SELECT * from student
</select>

<select id="getTeacher" resultType="Teacher">
    select * from teacher where id = #{id}
</select>

此时查出结果如下：

Student{id=1, name='张三', teacher=Teacher{id=1, name='李老师'}}
Student{id=2, name='李四', teacher=Teacher{id=1, name='李老师'}}
Student{id=3, name='王五', teacher=Teacher{id=2, name='王老师'}}

小结： 当对象里的属性为另外一个对象的时候，使用<association>属性，property即Java对象的属性，column为数据库的字段，这里就是通过tid传给getTeacherselect语句来根据主键查询老师的，javaType即为属性的Java类型。

2.4.2 连表查询

使用左连接查询，不需要分为两个子查询，xml如下：

<resultMap id="selectStudentMap2" type="Student">
    <result property="id" column="sid"/>
    <result property="name" column="sname"/>
    <association property="teacher"
                 javaType="Teacher">
        <result property="id" column="tid"/>
        <result property="name" column="tname" />
    </association>

</resultMap>

<select id="selectStudentList2" resultMap="selectStudentMap2">
    SELECT t1.id as sid,t1.name as sname,t2.id as tid,t2.name as tname
    FROM student t1
    LEFT JOIN teacher t2
    on t1.tid = t2.id
</select>

查询结果：

Student{id=1, name='张三', teacher=Teacher{id=1, name='李老师'}}
Student{id=2, name='李四', teacher=Teacher{id=1, name='李老师'}}
Student{id=3, name='王五', teacher=Teacher{id=2, name='王老师'}}

三、一对多

换个角度，老师和学生的关系就是一对多了。

3.1 实体类

Student类：

public class Student {
    private int id;
    private String name;
    private int tid;
}

Teacher类：

public class Teacher {
    private int id;
    private String name;
    private List<Student> studentList;
}

3.2 查询

查询所有老师mapper接口：

List<Teacher> selectTeachers();

xml实现：

<select id="selectTeachers" resultType="Teacher">
    select * from teacher
</select>

junit测试：

@Test
public void selectStudentList2() {
    SqlSession sqlSession = MybatisUtils.getSqlSession();
    try {
        TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
        List<Teacher> teacherList = teacherMapper.selectTeachers();
        for (Teacher teacher : teacherList) {
            System.out.println(teacher);
        }
    } catch (Exception e) {
        e.printStackTrace();
    } finally {
        sqlSession.close();
    }
}

测试结果：

Teacher{id=1, name='李老师', studentList=null}
Teacher{id=2, name='王老师', studentList=null}

3.3 一对多查询

3.3.1 按结果嵌套查询

SELECT t.id as tid,t.name as tname, s.id as sid,s.name as sname 
FROM teacher t 
LEFT JOIN student s
on t.id = s.tid

查询结果如下：

tid	tname	sid		sname
1	李老师		1		张三
1	李老师		2		李四
2	王老师		3		王五

修改上面的Mybatis查询：

mapper接口：

List<Teacher> getTeachers();

xml实现：

<resultMap id="getTeachersMap" type="mybatis07.domain.Teacher">
    <result property="id" column="tid"/>
    <result property="name" column="tname"/>
    <collection property="studentList" ofType="mybatis07.domain.Student">
        <result property="id" column="sid"/>
        <result property="name" column="sname"/>
        <result property="tid" column="tid"/>
    </collection>
</resultMap>

<select id="getTeachers" resultMap="getTeachersMap">
    SELECT t.id as tid,t.name as tname, s.id as sid,s.name as sname
    FROM teacher t
    LEFT JOIN student s
    on t.id = s.tid
</select>

其中，ofType是List<T>中的泛型类型。

查询结果：

Teacher{id=1, name='李老师', studentList=[Student{id=1, name='张三', tid=1}, Student{id=2, name='李四', tid=1}]}
Teacher{id=2, name='王老师', studentList=[Student{id=3, name='王五', tid=2}]}

3.3.2 子查询

步骤：先查老师，在查学生。

<resultMap id="getTeachersMap2" type="mybatis07.domain.Teacher">
    <result property="id" column="id"/>
    <result property="name" column="name"/>
    <collection property="studentList"
                javaType="ArrayList"
                ofType="mybatis07.domain.Student"
                select="getStudents2"
                column="id"/>

</resultMap>

<select id="getStudents2" resultType="mybatis07.domain.Student">
    select * from student where tid = #{tid}
</select>

<select id="getTeachers2" resultMap="getTeachersMap2">
    select * from teacher
</select>

查询结果：

Teacher{id=1, name='李老师', studentList=[Student{id=1, name='张三', tid=1}, Student{id=2, name='李四', tid=1}]}
Teacher{id=2, name='王老师', studentList=[Student{id=3, name='王五', tid=2}]}