9.多表查询结果集映射
问题:
-
需要多表查询,但是返回的结果集往往只能是一个实体类型;
-
数据库多表查询结果(可能多张表的字段),与实体类(可能涉及多个实体类),但是Mybatis返回结果中,只有一个returnType——结果集映射
两种情况:
student表:id,name,tid
techear表:id,name
-
多对一:需要查询所有学生,及其对应的老师姓名(s.id s.name t.name),主体是学生(查询结果存在多个学生对应一个老师)
查询条件是所有的学生,学生表join老师表
-
**一对多:**需要查询指定老师,及其所有的学生信息(s.id s.name t.name),主体是老师(一个老师对应多个学生)
查询结果是指定老师,老师表join学生表
此处把握住是**“谁是主体”+“谁连接谁”**,join后面的是一个对象(多对一),join后面是一个集合(一对多)
9.1 老师学生例子数据库表
- 对于学生而言:多个学生对应一个老师,association关系,多对一
- 对于老师而言:一个老师对应多个学生,collection关系,一对多
新建数据库表:
SQL:
CREATE TABLE `teacher` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO teacher(`id`, `name`) VALUES (1, '秦老师');
CREATE TABLE `student` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
`tid` INT(10) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `fktid` (`tid`),
CONSTRAINT `fktid` FOREIGN KEY (`tid`) REFERENCES `teacher` (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('1', '小明', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('2', '小红', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('3', '小张', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('4', '小李', '1');
INSERT INTO `student` (`id`, `name`, `tid`) VALUES ('5', '小王', '1');
9.2多对一
需求:
-
需要查询所有学生,及其对应的老师姓名(s.id s.name t.name),主体是学生(查询结果存在多个学生对应一个老师)
-
查询条件是所有的学生,学生表join老师表
(1)测试环境搭建
项目结构:
导入lombok
1.新建实体类
Student:
@Data
public class Student {
private int id;
private String name;
//学生需要关联一个老师,此处属性为一个对象
private Teacher teacher;
}
Teacher:
@Data
public class Teacher {
private int id;
private String name;
}
2.建立Mapper接口
StudentMapper:
public interface StudentMapper {
//查找所有学生,以及每个学生对应的老师姓名
List<Student> getStudent();
}
TeacherMapper:
public interface TeacherMapper {
//根据id查找老师,及老师的所有学生
Teacher getTeacher(int id);
}
3.建立Mapper.xml文件(后续改)
在Resources目录下,建立和java目录下同样的包结构com.kuang.dao
,易错:resources下建包用 / 分隔,java下建包用 . 分隔
而且Mapper.xml需要和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.kuang.dao.StudentMapper">
</mapper>
TeacherMapper.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.kuang.dao.TeacherMapper">
</mapper>
5.在核心配置文件中绑定注册Mapper接口或者Mapper.xml或者包
<mappers>
<mapper class="com.kuang.dao.TeacherMapper"/>
<mapper class="com.kuang.dao.StudentMapper"/>
</mappers>
6.测试是否能够查询成功(后续改)
(2)按照结果嵌套处理(类连接查询、重点)
查询主体是学生
StudentMapper.xml:
<select id="getStudent" resultMap="StudentTeacher">
select s.id sid,s.name sname,t.name tname
from student s,teacher t
where s.tid = t.id;
</select>
<!--查询主体是学生,学生表join老师表-->
<resultMap id="StudentTeacher" type="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<!--在学生实体类中,Teacher属性是一个对象-->
<association property="teacher" javaType="Teacher">
<result property="name" column="tname"/>
</association>
</resultMap>
先通过sql查询出结果,再 “sql结果字段” 与“实体类对象属性” 映射
测试:
@Test
public void testStudent(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> studentList = mapper.getStudent();
for (Student student : studentList) {
System.out.println(student);
}
sqlSession.close();
}
(3)按照查询嵌套处理(类子查询)
查询主体是学生
StudentMapper.xml:
<mapper namespace="com.kuang.dao.StudentMapper">
<!--
思路:
1.查询所有学生的信息
2.根据查询出来的学生的tid,寻找对应的老师
-->
<!--查询主体是学生,学生表join老师表-->
<select id="getStudent" resultMap="StudentTeacher">
select * from student
</select>
<resultMap id="StudentTeacher" type="Student">
<result property="id" column="id"/>
<result property="name" column="name"/>
<!--复杂的属性,我们需要单独处理
对象association 集合:collection
javaType="" 指定属性的类型
集合中的泛型信息,我们使用ofType获取(一对多采用)
-->
<!--在学生实体类中,Teacher属性是一个对象-->
<association property="teacher" javaType="Teacher" select="getTeacher" column="tid" />
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * from teacher where id = #{id}
</select>
</mapper>
测试:
@Test
public void testStudent(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
UserMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> studentList = mapper.getStudent();
for (Student student : studentList) {
System.out.println(student);
}
sqlSession.close();
}
9.3一对多
需求:
-
需要查询指定老师,及其所有的学生信息(s.id s.name t.name),主体是老师(一个老师对应多个学生)
-
查询结果是指定老师,老师表join学生表
(1)测试环境搭建
实体类:
Student:
@Data
public class Student {
private int id;
private String name;
private int tid;
}
Teacher:
@Data
public class Teacher {
private int id;
private String name;
//一个老师拥有多个学生,此处属性为一个List
private List<Student> students;
}
(2)按照结果嵌套处理(类连接查询、重点)
查询主体是老师
TeacherMapper.xml:
<select id="getTeacher" resultMap="TeacherStudent">
select s.id sid,s.name sname,t.name tname,t.id tid
from teacher t
inner join student s
on t.id=s.tid
where t.id=#{id}
</select>
<!--查询主体是老师,老师表join学生表-->
<resultMap id="TeacherStudent" type="Teacher">
<result property="id" column="tid"/>
<result property="name" column="tname"/>
<!--在Teacher实体类中,Student属性是一个List,泛型需要指明类型-->
<collection property="students" javaType="ArrayList" ofType="Student">
<result property="id" column="sid"/>
<result property="name" column="sname"/>
<result property="tid" column="tid"/>
</collection>
</resultMap>
先通过sql查询出结果,再 “sql结果字段” 与“实体类对象属性” 一一映射
测试:
public class TeacherMapperTest {
@Test
public void getTeacher(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher(1);
System.out.println(teacher);
sqlSession.close();
}
}
(3)按照查询嵌套处理(类子查询)
查询主体是老师
TeacherMapper.xml:
<select id="getTeacher" resultMap="TeacherStudent">
select * from `teacher` where id=#{id}
</select>
<resultMap id="TeacherStudent" type="Teacher">
<collection property="students" javaType="ArrayList" ofType="Student"
select="getStudent" column="id"/>
</resultMap>
<select id="getStudent" resultType="Student">
select * from `student` where tid=#{id};
</select>
测试:
public class TeacherMapperTest {
@Test
public void getTeacher(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher(1);
System.out.println(teacher);
sqlSession.close();
}
}
小结:
- 关联 association,多对一
- 集合 collection,一对多
- javaType 和 ofType
- javaType 用来制定实体类中属性的类型
- ofType用来指定映射到List(或者集合)中的pojo类型,泛型中的约束类
注意点:
- 保证SQL的可读性,尽量保证通俗易懂
- 注意一对多和多对一中 属性名和字段的问题
- 如果问题不好排查错误,可以使用日志,建议使用Log4j
面试高频:
- MySQL引擎
- InnoDB底层原理
- 索引
- 索引优化