Mybatis-高级结果映射(多对一、一对多查询处理)
如果这个世界总是这么简单就好了。
MyBatis 创建时的一个思想是:数据库不可能永远是你所想或所需的那个样子。 我们希望每个数据库都具备良好的第三范式或 BCNF 范式,可惜它们并不都是那样。 如果能有一种数据库映射模式,完美适配所有的应用程序,那就太好了,但可惜也没有。 而 ResultMap 就是 MyBatis 对这个问题的答案。
resultMap元素有很多子元素和一个值得深入探讨的结构。 下面是resultMap 元素的概念视图。
下面列举比较常用的结果集映射的标签和属性
常用标签
id
:一个 ID 结果;标记出作为 ID 的结果可以帮助提高整体性能result
: 注入到字段或JavaBean
属性的普通结果association
:一个复杂类型的关联;许多结果将包装成这种类型- 嵌套结果映射 – 关联可以是
resultMap
元素,或是对其它结果映射的引用
- 嵌套结果映射 – 关联可以是
collection
:一个复杂类型的集合- 嵌套结果映射 – 集合可以是
resultMap
元素,或是对其它结果映射的引用
- 嵌套结果映射 – 集合可以是
常用属性
property
:映射到列结果的字段或属性。如果 JavaBean 有这个名字的属性(property
),会先使用该属性。否则
MyBatis 将会寻找给定名称的字段(field)。 无论是哪一种情形,你都可以使用常见的点式分隔形式进行复杂属性导航。
比如,你可以这样映射一些简单的东西:“username”,或者映射到一些复杂的东西上:“address.street.number”。column
:数据库中的列名,或者是列的别名。一般情况下,这和传递给 resultSet.getString(columnName)
方法的参数一样。javaType
:一个 Java 类的全限定名,或一个类型别名(关于内置的类型别名,可以参考上面的表格)。 如果你映射到一个
JavaBean
, MyBatis 通常可以推断类型。然而,如果你映射到的是 HashMap,那么你应该明确地指定 javaType
来保证行为与期望的相一致。
下面有两张相关联的Mysql表,思考如何通过结果集映射进行多对一查询
teacher
表
CREATE TABLE `teacher` (
`id` INT(10) NOT NULL,
`name` VARCHAR(30) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8;
student
表
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;
通过teacher
的id
作为外键关联到学生,多个学生对应一位老师
表中插入数据
teacher
表
student
表
实体类
teacher
实体类
public class Teacher {
private int id;
private String name;
public Teacher() {
}
public Teacher(int id, String name) {
this.id = id;
this.name = name;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@Override
public String toString() {
return "Teacher{" +
"id=" + id +
", name='" + name + '\'' +
'}';
}
}
student
实体类
public class Student {
private int id;
private String name;
private Teacher teacher;
public Student() {
}
public Student(int id, String name, Teacher teacher) {
this.id = id;
this.name = name;
this.teacher = teacher;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public Teacher getTeacher() {
return teacher;
}
public void setTeacher(Teacher teacher) {
this.teacher = teacher;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", teacher=" + teacher +
'}';
}
}
查询所有学生的信息以及对应的老师
接口
public interface StudentMapper {
public List<Student> selectAll();
}
mapper.xml
这里结果集映射有两种方法
方法一、写完整SQL,架构出结果集(速度快,阅读性好,不易维护)
<?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="cn.kexing.mybatis.dao.StudentMapper">
<select id="selectAll" resultMap="StudentTeacher">
select s.id sid,s.`name` sname,t.id tid,t.`name` tname
from student s LEFT JOIN teacher t
ON t.id=s.tid
</select>
<resultMap id="StudentTeacher" type="Student">
<result property="id" column="sid"></result>
<result property="name" column="sname"></result>
<association property="teacher" javaType="Teacher">
<id property="id" column="tid"></id>
<result property="name" column="tname"></result>
</association>
</resultMap>
</mapper>
javaType属性可不写,主要看如何规范
Mybatis得到mapper接口对象主要用的反射,所以有关mapper接口函数的信息可以省略
Test
@Test
public void test1(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
StudentMapper mapper = sqlSession.getMapper(StudentMapper.class);
List<Student> students = mapper.selectAll();
for(Student student:students){
System.out.println(student);
}
sqlSession.close();
}
结果
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='秦老师'}}
方法二、通过类似子查询、SQL简单、映射较麻烦(可读性低、速度慢)
<?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="cn.kexing.mybatis.dao.StudentMapper">
<select id="selectAll" resultType="Student" resultMap="StudentTeacher">
select * from student;
</select>
<resultMap id="StudentTeacher" type="Student">
<association property="teacher" column="tid" select="getTeacher"></association>
</resultMap>
<select id="getTeacher" resultType="Teacher">
select * from teacher where id=#{tid};
</select>
</mapper>
结果
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='秦老师'}}
仁者见仁智者见智,个人比较喜欢第一种方法
如何实现一对多、一个老师教多个学生
student
实体类
public class Student {
private int id;
private String name;
private int tid;
public Student() {
}
public Student(int id, String name, int tid) {
this.id = id;
this.name = name;
this.tid = tid;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public int getTid() {
return tid;
}
public void setTid(int tid) {
this.tid = tid;
}
@Override
public String toString() {
return "Student{" +
"id=" + id +
", name='" + name + '\'' +
", tid=" + tid +
'}';
}
}
teacher
实体类
//一对多,一个老师教多个学生
public class Teacher {
private int id;
private String name;
private List<Student> students;
public Teacher() {
}
public Teacher(int id, String name, List<Student> students) {
this.id = id;
this.name = name;
this.students = students;
}
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
public List<Student> getStudents() {
return students;
}
public void setStudents(List<Student> students) {
this.students = students;
}
@Override
public String toString() {
return "Teacher{" +
"id=" + id +
", name='" + name + '\'' +
", students=" + students +
'}';
}
}
mapper
接口
//根据老师id查询老师教的学生
public Teacher selectTeacher(@Param("tid") int id);
和上面的多对一类似,这里主要是如何使用collection
进行映射
mapper.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="cn.kexing.mybatis.dao.TeacherMapper">
<select id="selectTeacher" resultMap="teacherResult">
SELECT t.id tid,t.`name` tname,s.`name` sname,s.id sid
FROM teacher t LEFT JOIN student s
ON t.id=s.tid and t.id=#{tid}
</select>
<resultMap id="teacherResult" type="Teacher">
<id property="id" column="tid"></id>
<result property="name" column="tname"></result>
<collection property="students" ofType="Student">
<result property="id" column="sid"></result>
<result property="name" column="sname"></result>
<result property="tid" column="tid"></result>
</collection>
</resultMap>
</mapper>
Test
@Test
public void selectTeacher(){
SqlSession sqlSession = MybatisUtils.getSqlSession();
TeacherMapper mapper = sqlSession.getMapper(TeacherMapper.class);
Teacher teacher = mapper.selectTeacher(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}]}