Mybatis学习笔记(九):一对多及多对一查询

一、前言

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

二、多对一

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

2.1 建表语句

1
-- ----------------------------
2
-- Table structure for student
3
-- ----------------------------
4
DROP TABLE IF EXISTS "public"."student";
5
CREATE TABLE "public"."student" (
6
  "id" int4 NOT NULL,
7
  "name" varchar(255) COLLATE "pg_catalog"."default",
8
  "tid" int4
9
)
10
;
11
-- ----------------------------
12
-- Table structure for teacher
13
-- ----------------------------
14
DROP TABLE IF EXISTS "public"."teacher";
15
CREATE TABLE "public"."teacher" (
16
  "id" int4 NOT NULL,
17
  "name" varchar(255) COLLATE "pg_catalog"."default"
18
)
19
;
20
-- ----------------------------
21
-- Primary Key structure for table student
22
-- ----------------------------
23
ALTER TABLE "public"."student" ADD CONSTRAINT "student_pkey" PRIMARY KEY ("id");
24
-- ----------------------------
25
-- Primary Key structure for table teacher
26
-- ----------------------------
27
ALTER TABLE "public"."teacher" ADD CONSTRAINT "teacher_pkey" PRIMARY KEY ("id");
28
-- ----------------------------
29
-- Foreign Keys structure for table student
30
-- ----------------------------
31
ALTER TABLE "public"."student" ADD CONSTRAINT "fk_tid" FOREIGN KEY ("tid") REFERENCES "public"."teacher" ("id") ON DELETE NO ACTION ON UPDATE NO ACTION;

ER图:

image-20211204205000997

插入测试数据:

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

2.2 实体类对象

Student:

1
public class Student {
2
    
3
    private int id;
4
5
    private String name;
6
7
    private Teacher teacher;
8
    
9
}

Teacher:

1
public class Teacher {
2
3
    private int id;
4
5
    private String name;
6
7
}

2.3 查询

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

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

结果如下:

image-20211204211608285

现在使用mybatis查询:

mapper接口:

1
List<Student> selectStudentList();

xml实现:

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

junit测试:

1
@Test
2
public void selectStudentList() {
3
    SqlSession sqlSession = MybatisUtils.getSqlSession();
4
    try {
5
        StudentMapper studentMapper = sqlSession.getMapper(StudentMapper.class);
6
        List<Student> studentList = studentMapper.selectStudentList();
7
        for (Student student : studentList) {
8
            System.out.println(student);
9
        }
10
    } catch (Exception e) {
11
        e.printStackTrace();
12
    } finally {
13
        sqlSession.close();
14
    }
15
}

查询结果:

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

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

2.4 多对一的两种查询

2.4.1 嵌套查询

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

因此,写出根据id查出老师的语句:

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

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

1
<resultMap id="selectStudentMap" type="Student">
2
    <result property="id" column="id" />
3
    <result property="name" column="name" />
4
    <!--        复杂属性需要单独处理: 1. 对象:使用 association 2. 集合:使用 collection-->
5
    <association property="teacher"
6
                 column="tid"
7
                 javaType="Teacher"
8
                 select="getTeacher"/>
9
10
</resultMap>
11
12
<select id="selectStudentList" resultMap="selectStudentMap">
13
    SELECT * from student
14
</select>
15
16
<select id="getTeacher" resultType="Teacher">
17
    select * from teacher where id = #{id}
18
</select>

此时查出结果如下:

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

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

2.4.2 连表查询

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

1
<resultMap id="selectStudentMap2" type="Student">
2
    <result property="id" column="sid"/>
3
    <result property="name" column="sname"/>
4
    <association property="teacher"
5
                 javaType="Teacher">
6
        <result property="id" column="tid"/>
7
        <result property="name" column="tname" />
8
    </association>
9
10
</resultMap>
11
12
<select id="selectStudentList2" resultMap="selectStudentMap2">
13
    SELECT t1.id as sid,t1.name as sname,t2.id as tid,t2.name as tname
14
    FROM student t1
15
    LEFT JOIN teacher t2
16
    on t1.tid = t2.id
17
</select>

查询结果:

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

三、一对多

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

3.1 实体类

Student类:

1
public class Student {
2
    private int id;
3
    private String name;
4
    private int tid;
5
}

Teacher类:

1
public class Teacher {
2
    private int id;
3
    private String name;
4
    private List<Student> studentList;
5
}

3.2 查询

查询所有老师mapper接口:

1
List<Teacher> selectTeachers();

xml实现:

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

junit测试:

1
@Test
2
public void selectStudentList2() {
3
    SqlSession sqlSession = MybatisUtils.getSqlSession();
4
    try {
5
        TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
6
        List<Teacher> teacherList = teacherMapper.selectTeachers();
7
        for (Teacher teacher : teacherList) {
8
            System.out.println(teacher);
9
        }
10
    } catch (Exception e) {
11
        e.printStackTrace();
12
    } finally {
13
        sqlSession.close();
14
    }
15
}

测试结果:

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

3.3 一对多查询

3.3.1 按结果嵌套查询

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

查询结果如下:

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

修改上面的Mybatis查询:

mapper接口:

1
List<Teacher> getTeachers();

xml实现:

1
<resultMap id="getTeachersMap" type="mybatis07.domain.Teacher">
2
    <result property="id" column="tid"/>
3
    <result property="name" column="tname"/>
4
    <collection property="studentList" ofType="mybatis07.domain.Student">
5
        <result property="id" column="sid"/>
6
        <result property="name" column="sname"/>
7
        <result property="tid" column="tid"/>
8
    </collection>
9
</resultMap>
10
11
<select id="getTeachers" resultMap="getTeachersMap">
12
    SELECT t.id as tid,t.name as tname, s.id as sid,s.name as sname
13
    FROM teacher t
14
    LEFT JOIN student s
15
    on t.id = s.tid
16
</select>

其中,ofTypeList<T>中的泛型类型。

查询结果:

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

3.3.2 子查询

步骤:先查老师,在查学生。

1
<resultMap id="getTeachersMap2" type="mybatis07.domain.Teacher">
2
    <result property="id" column="id"/>
3
    <result property="name" column="name"/>
4
    <collection property="studentList"
5
                javaType="ArrayList"
6
                ofType="mybatis07.domain.Student"
7
                select="getStudents2"
8
                column="id"/>
9
10
</resultMap>
11
12
<select id="getStudents2" resultType="mybatis07.domain.Student">
13
    select * from student where tid = #{tid}
14
</select>
15
16
<select id="getTeachers2" resultMap="getTeachersMap2">
17
    select * from teacher
18
</select>

查询结果:

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

四、总结

  1. 关联 - 多对一:使用association
  2. 集合 - 一对多:使用collection
  3. javaType:指定实体类中属性的类型
  4. ofType:指定映射到集合中的pojo类型,如List<T>中的T类型。