策略
MyBatis中遇到联表查询时有两个策略:
- 按照结果嵌套查询
- 按照查询嵌套查询
而联表查询中又会有多种情况,比较常见的就是:
- 一对多
- 多对一
0. 持久层
老师表:
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');
1. 一对多 <collection>
1.1 实体类
Teacher
中有一个List<Student>
来存放一个老师的所有学生实例
Student
中有一个tid
来标记一个学生对应的老师的id
一对多:一个老师可能对应多个学生,一个学生只能对应一个老师
Teacher
package com.zzw.pojo;
import lombok.Data;
import java.util.List;
// lombok会自动填空基本方法
@Data
public class Teacher {
private int id;
private String name;
private List<Student> students;
}
Student
package com.zzw.pojo;
import lombok.Data;
// lombok会自动填空基本方法
@Data
public class Student {
private int id;
private String name;
private int tid;
}
1.2 接口
期望在数据库中通过老师的tid
直接查询到特定老师并返回其对应的所有学生
TeacherMapper:
package com.zzw.dao;
import com.zzw.pojo.Teacher;
import org.apache.ibatis.annotations.Param;
import java.util.List;
public interface TeacherMapper {
// 根据Teacher的id查询相对应的Teacher和他所有的Student
Teacher getTeacher(@Param("tid") int id);
}
1.3 XML配置文件
这里先使用根据结果嵌套查询的方式来举例一对多的查询,根据查询嵌套查询将在学生的多对一的例子中列出。
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zzw.dao.TeacherMapper">
<!-- 按结果嵌套查询 -->
<select id="getTeacher" resultMap="TeacherStudent">
select s.id as sid, s.name as sname, t.name as tname, t.id as tid
from student s, teacher t
where s.tid = t.id and t.id = #{tid}
</select>
<resultMap id="TeacherStudent" type="Teacher">
<result property="id" column="tid" />
<result property="name" column="tname" />
<collection property="students" ofType="Student" >
<result property="id" column="sid" />
<result property="name" column="sname" />
<result property="tid" column="tid" />
</collection>
</resultMap>
</mapper>
因为Teacher
中拥有一个List<Student>
的字段,在MyBatis的配置文件中做<resultMap>
时需要使用<collection>
来一一映射Student
。在<collection>
需要标记这个list在原type中的字段名, 即students
,而它本身是一个list,需要使用ofType
来表明集合中的泛型类型,即Student
,然后在<collection>
里做Student
的结果映射。
1.4 测试
@Test
public void testGetTeacher() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.getTeacher(1);
System.out.println(teacher);
sqlSession.close();
}
运行结果:
Teacher(id=1, name=秦老师, students=[Student(id=1, name=小明, tid=1), Student(id=2, name=小红, tid=1), Student(id=3, name=小张, tid=1), Student(id=4, name=小李, tid=1), Student(id=5, name=小王, tid=1)])
2. 多对一 <association>
2.1 实体类
Teacher
中去掉List<Student> students
字段
Student
中添加一个Teacher teacher
字段,指向一个老师,多个学生可能都指向同一个老师
Teacher
package com.zzw.pojo;
import lombok.Data;
import java.util.List;
@Data
public class Teacher {
private int id;
private String name;
}
Student
package com.zzw.pojo;
import lombok.Data;
@Data
public class Student {
private int id;
private String name;
private Teacher teacher;
}
2.2 接口
期望在数据库中查询到所有的学生,并且每条学生记录中包含一条老师的记录(老师id,姓名)。
package com.zzw.dao;
import com.zzw.pojo.Student;
import java.util.List;
public interface StudentMapper {
// 演示 按照查询嵌套查询
List<Student> getAllStudent();
// 演示 按照结果嵌套查询
List<Student> getAllStudent2();
}
2.3 XML配置文件
这里分别使用了按照结果嵌套查询和按照查询嵌套查询,总体来说前者需要在写sql时就明确好结构,再通过Mybatis的resultMap来映射;后者不需要写太复杂的sql语句,但需要在xml中配置较多标签。
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Config 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.zzw.dao.StudentMapper">
<!-- 方法1:按照结果 嵌套 -->
<select id="getAllStudent" resultMap="StudentTeacher">
select s.id as sid, s.name as name, t.name as tname
from student s, teacher t
where s.tid = t.id
</select>
<resultMap id="StudentTeacher" type="Student">
<result property="id" column="sid" />
<result property="name" column="name" />
<association property="teacher" javaType="Teacher" >
<result property="name" column="tname" />
<!-- 这里忘记映射tid了 -->
</association>
</resultMap>
<!-- 方法2:按照查询 嵌套 -->
<select id="getAllStudent2" resultMap="StudentTeacher2">
select * from student
</select>
<resultMap id="StudentTeacher2" type="Student" >
<result property="id" column="id" />
<result property="name" column="name" />
<association property="teacher" column="tid" javaType="Teacher" select="getTeacher" />
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * from teacher where id = #{tid}
</select>
</mapper>
不同于一对多,多对一的没有list的结构,一条记录里只嵌套一个其他类,需要使用<association>
, 并且嵌套的类要使用<javaType>
标明,然后再在<association>
标签内作这个嵌套类,即Teacher
的结果映射。
2.4 测试
@Test
public void testGetAllStudents() {
SqlSession sqlSession = MybatisUtils.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> studentList = mapper.getAllStudent();
// getAllStudent()和getAllStudent2()运行结果一样
// List<Student> studentList = mapper.getAllStudent2();
for (Student student : studentList) {
System.out.println(student);
}
sqlSession.close();
}
运行结果:
// 按照结果嵌套查询
// Teacher的id为0是因为我们没有在association里映射tid,算是写SQL时的一个小错误
Student(id=1, name=小明, teacher=Teacher(id=0, name=秦老师))
Student(id=2, name=小红, teacher=Teacher(id=0, name=秦老师))
Student(id=3, name=小张, teacher=Teacher(id=0, name=秦老师))
Student(id=4, name=小李, teacher=Teacher(id=0, name=秦老师))
Student(id=5, name=小王, teacher=Teacher(id=0, name=秦老师))
// 按照查询嵌套查询
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=1, name=秦老师))
Student(id=4, name=小李, teacher=Teacher(id=1, name=秦老师))
Student(id=5, name=小王, teacher=Teacher(id=1, name=秦老师))
3. 两种查询方式的优缺点:
方法1:按照结果嵌套查询的sql事先写好,可以在console中提前运行测试,但其性能在表中数据量大时将很慢。
方法2:按照查询嵌套查询写起来比较繁琐,且无法事先写好sql语句在console中运行测试,调试困难。