ResultMap结果映射解决复杂属性
之前我们提到了用resultMap解决数据表中字段名与bean属性名不一致的问题,这是resultMap的一种简单实现。下面我们来看如何利用ResultMap来解决更复杂的属性问题
场景:当我们需要联查两张表的时候,通常会在sql层面对两个表进行外键关联。那么设置了外键的从表对应的实体Bean中就需要定义一个对应主表的实例对象。
多对一关系处理
按照查询嵌套
示例:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
// 学生表 从表 public class Student { private int id; private String name; private Teacher teacher; // 定义主表对应bean实例 // 教师表 主表 public class Teacher { private int id; private String name; //teacher接口中定义方法 @Select ( "select name from teacher where id = #{tid}" ) Teacher getTeacherById( @Param ( "tid" ) int id); // student接口中定义方法 List<Student> getStudent(); |
sql:
1
2
3
4
5
|
-- 可以直接利用多表联查sql 但是最终无法正确输出teacher类信息 select * from student s join teacher t on s.tid = t.id; -- 将上面的sql拆开成两句 先查询到学生信息 用学生的tid字段对应教师的id查询教师 select * from student; select * from teacher where id = #{tid} |
打印日志:
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@79da8dc5]
==> Preparing: select * from student;
==> Parameters:
<== Columns: id, name, tid
<== Row: 1, 小子三, 1
<== Row: 2, 小子四, 1
<== Row: 3, 小子五, 1
<== Row: 4, 小子六, 1
<== Row: 5, 小子七, 1
<== Row: 6, 小子八, 1
<== Total: 6
Student{id=1, name='小子三', teacher=null}
Student{id=2, name='小子四', teacher=null}
Student{id=3, name='小子五', teacher=null}
Student{id=4, name='小子六', teacher=null}
Student{id=5, name='小子七', teacher=null}
Student{id=6, name='小子八', teacher=null}
可以很清晰的看出sql一共只执行了一条,而根据学生表tid字段查询教师信息的sql根本就没有运行
下面我们对select * from teacher where id = #{tid}
这句sql返回的结果配置,利用resultMap为其配置合理的结果集来接收查询到的结果
resultMap及sql配置如下:
1
2
3
4
5
6
7
8
|
< resultMap id = "studentTeacher" type = "student" > <!-- 普通映射 --> < result column = "id" property = "id" /> < result column = "name" property = "name" /> <!-- 复杂映射 association对象 collection集合 --> < association property = "teacher" column = "tid" javaType = "teacher" select = "getTeacherById" /> <!-- student表中tid字段对应实体类student中的teacher属性 对应程序中的teacher类型 执行select语句 --> </ resultMap > |
最终打印结果如下:
Setting autocommit to false on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@79da8dc5]
// 执行sql1
==> Preparing: select * from student;
==> Parameters:
<== Columns: id, name, tid
<== Row: 1, 小子三, 1
// 执行sql1
====> Preparing: select * from teacher where id = ?
====> Parameters: 1(Integer)
<==== Columns: id, name
<==== Row: 1, 秦老师
<==== Total: 1
<== Row: 2, 小子四, 1
<== Row: 3, 小子五, 1
<== Row: 4, 小子六, 1
<== Row: 5, 小子七, 1
<== Row: 6, 小子八, 1
<== Total: 6
// 最终teacher也以对象的形式打印出来
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{id=6, name='小子八', teacher=Teacher{id=1, name='秦老师'}}
按照结果嵌套
studentMapper.xml配置resultMap如下:
1
2
3
4
5
6
7
8
9
10
11
12
|
<!-- 按照结果嵌套 --> < select id = "getStudent2" resultMap = "studentTeacher2" > select s.id sid,s.name sname,t.id tid,t.name tname from student s join teacher t on s.tid = t.id; </ select > < resultMap id = "studentTeacher2" type = "student" > < result column = "sid" property = "id" /> < result column = "sname" property = "name" /> < association property = "teacher" javaType = "teacher" > < result column = "tid" property = "id" /> < result property = "name" column = "tname" /> </ association > </ resultMap > |
如果采用结果嵌套配置,此时不论bean类属性是否是基本类型都需要用result进行映射,否则输出结果就会采用默认值
一对多关系处理
一对多关系处理,依旧以上述老师和学生为例。但是实体类需要修改,如下:
1
2
3
4
5
6
7
8
|
public class Teacher { private int id; private String name; private List<Student> student; public class Student { private int id; private String name; private int tid; |
一个老师下对应多个学生,所以我们定义一个集合用于存储学生
下面尝试获取指定老师下的所有学生信息及该老师信息
按照结果嵌套
程序:
1
2
3
|
// TeacherMapper // 指定老师下面的所有学生 Teacher getTeaById( @Param ( "teaId" ) int id); |
resultMap配置
1
2
3
4
5
6
7
8
9
10
11
12
13
14
|
< select id = "getTeaById" resultMap = "TeaStu" > select s.id sid, s.name sname, t.name tname, t.id tpid,s.tid from student s ,teacher t where t.id = s.tid and t.id = #{teaId}; </ select > < resultMap id = "TeaStu" type = "Teacher" > < result column = "tpid" property = "id" /> < result column = "tname" property = "name" /> < collection property = "student" ofType = "student" > < result column = "sid" property = "id" /> < result column = "sname" property = "name" /> < result column = "tid" property = "tid" /> </ collection > </ resultMap > |
因为复杂属性的类型为集合,所以我们在配置resultMap结果集映射时不再使用association对象,换成collection集合。在配置collection与association不同的是将JavaType(Java类型)换成了OfType其他依旧不变
按照查询嵌套
TeacherMapper.xml配置resultMap
1
2
3
4
5
6
7
8
9
10
11
|
< select id = "getTeaById" resultMap = "TeaStu2" > select id,name from teacher where id = #{teaId} </ select > < resultMap id = "TeaStu2" type = "teacher" > < result column = "id" property = "id" /> < result column = "name" property = "name" /> < collection property = "student" javaType = "ArrayList" ofType = "student" select = "getStuList" column = "id" /> </ resultMap > < select id = "getStuList" resultType = "student" > select id,name,tid from student where tid = #{tid} </ select > |
小结:个人感觉,按照查询嵌套虽然一定程度上简化了sql语句的编写,但是针对resultMap的配置极其复杂,如果使用次数多可能还好。建议使用按照结果嵌套,sql语句编写虽然复杂一些但是只要基础扎实都是有理可循的,而且sql的编写你可以在数据库中进行调试,选择最优sql,并且对于结果集的映射配置也比较简单,容易理解。
也可能是初学所以觉得按照子查询会有点难度,总之还是水平太低了
到此这篇关于MyBatis高级映射ResultMap解决属性问题的文章就介绍到这了,更多相关MyBatis高级映射ResultMap内容请搜索服务器之家以前的文章或继续浏览下面的相关文章希望大家以后多多支持服务器之家!
原文链接:https://blog.csdn.net/yuqu1028/article/details/128883996