Mybatis高级结果映射

有时侯，我们用SQL取得的结果需要映射到类似Map<key, Bean>这样的数据结构中或是映射到多个实体类中时，我们就需要使用到resultMap。下面用3个例子说明Mybatis高级结果映射的用法。

<环境准备>

请参照上一篇blog<Mybatis Guide>

<数据准备>

创建班级--学生表的一对多的数据结构

 1 --创建班级表
 2 CREATE TABLE tempdb..test_class
 3 (
 4 class_id int NOT NULL PRIMARY KEY,
 5 class_name varchar(255),
 6 class_admin_name varchar(255)
 7 );
 8 --创建学生表
 9 CREATE TABLE tempdb..test_student
10 (
11 student_id int NOT NULL PRIMARY KEY,
12 class_id int,
13 student_name varchar(255),
14 student_age int,
15 student_address varchar(255)
16 );
17 --班级表中插入数据
18 insert tempdb..test_class values (101, 'Class 1, Grade 1', 'Mr. Zhang');
19 insert tempdb..test_class values (102, 'Class 2, Grade 1', 'Mr. Li');
20 insert tempdb..test_class values (103, 'Class 3, Grade 1', 'Mr. Wang');
21 insert tempdb..test_class values (201, 'Class 1, Grade 2', 'Mr. Zhao');
22 insert tempdb..test_class values (202, 'Class 2, Grade 2', 'Mr. Liu');
23 --学生表中插入数据
24 insert tempdb..test_student values (101001, 101, 'Name 1', 20, 'Address 1');
25 insert tempdb..test_student values (101002, 101, 'Name 2', 21, 'Address 2');
26 insert tempdb..test_student values (101003, 101, 'Name 3', 20, 'Address 3');
27 insert tempdb..test_student values (102001, 102, 'Name 4', 22, 'Address 4');
28 insert tempdb..test_student values (102002, 102, 'Name 5', 21, 'Address 5');
29 insert tempdb..test_student values (201001, 201, 'Name 6', 23, 'Address 6');
30 insert tempdb..test_student values (201002, 201, 'Name 7', 22, 'Address 7');
31 insert tempdb..test_student values (202001, 202, 'Name 8', 22, 'Address 8');

<创建实体类Entity>

 1 package mybatistest;
 2 
 3 import java.io.Serializable;
 4 
 5 public class Clazz implements Serializable {
 6 
 7     private static final long serialVersionUID = 1L;
 8 
 9     private Integer classId;
10 
11     private String className;
12 
13     private String classAdminName;
14 
15     public Integer getClassId() {
16         return classId;
17     }
18 
19     public void setClassId(Integer classId) {
20         this.classId = classId;
21     }
22 
23     public String getClassName() {
24         return className;
25     }
26 
27     public void setClassName(String className) {
28         this.className = className;
29     }
30 
31     public String getClassAdminName() {
32         return classAdminName;
33     }
34 
35     public void setClassAdminName(String classAdminName) {
36         this.classAdminName = classAdminName;
37     }
38 
39     @Override
40     public String toString() {
41         return "Clazz [classId=" + classId + ", className=" + className + ", classAdminName=" + classAdminName + "]";
42     }
43 }

Class

 1 package mybatistest;
 2 
 3 import java.io.Serializable;
 4 
 5 public class Student implements Serializable {
 6 
 7     private static final long serialVersionUID = 1L;
 8 
 9     private Integer studentId;
10 
11     private Integer classId;
12 
13     private String studentName;
14 
15     private Integer studentAge;
16 
17     private String studentAddress;
18 
19     public Integer getStudentId() {
20         return studentId;
21     }
22 
23     public void setStudentId(Integer studentId) {
24         this.studentId = studentId;
25     }
26 
27     public String getStudentName() {
28         return studentName;
29     }
30 
31     public Integer getClassId() {
32         return classId;
33     }
34 
35     public void setClassId(Integer classId) {
36         this.classId = classId;
37     }
38 
39     public void setStudentName(String studentName) {
40         this.studentName = studentName;
41     }
42 
43     public Integer getStudentAge() {
44         return studentAge;
45     }
46 
47     public void setStudentAge(Integer studentAge) {
48         this.studentAge = studentAge;
49     }
50 
51     public String getStudentAddress() {
52         return studentAddress;
53     }
54 
55     public void setStudentAddress(String studentAddress) {
56         this.studentAddress = studentAddress;
57     }
58 
59     @Override
60     public String toString() {
61         return "Student [classId=" + classId + ", studentId=" + studentId + ", studentName=" + studentName
62                 + ", studentAge=" + studentAge + ", studentAddress=" + studentAddress + "]";
63     }
64 }

Student

<创建StudentsPerClass(OutputBean)>

创建一个Class的OutBean，其中包含classId，Class实体类，及Student实体类List以对应班级--学生的1对多关系。

 1 package mybatistest;
 2 
 3 import java.io.Serializable;
 4 import java.util.List;
 5 
 6 public class StudentsPerClass implements Serializable {
 7 
 8     private static final long serialVersionUID = 1L;
 9 
10     private Integer classId;
11 
12     private Clazz clazz;
13 
14     private List<Student> students;
15 
16     public Integer getClassId() {
17         return classId;
18     }
19 
20     public void setClassId(Integer classId) {
21         this.classId = classId;
22     }
23 
24     public Clazz getClazz() {
25         return clazz;
26     }
27 
28     public void setClazz(Clazz clazz) {
29         this.clazz = clazz;
30     }
31 
32     public List<Student> getStudents() {
33         return students;
34     }
35 
36     public void setStudents(List<Student> students) {
37         this.students = students;
38     }
39 
40     @Override
41     public String toString() {
42         StringBuilder sb = new StringBuilder(
43                 "StudentsPerClass [classId=" + classId + ", clazz=" + clazz + ", students=" + System.lineSeparator());
44         students.forEach(p -> {
45             sb.append("--").append(p).append(System.lineSeparator());
46         });
47         return sb.append("]").toString();
48     }
49 }

StudentsPerClass

<创建三个SQL实例>

(1) 用Map<学号, 学生实体类>映射一个学生集合

(2) 用List<StudentsPerClass>映射班级--学生的1对多关系

(3) 用Map<班级号, StudentsPerClass>映射班级--学生集合

ClassStudentRepository.java

 1 package mybatistest;
 2 
 3 import java.util.List;
 4 import java.util.Map;
 5 
 6 import org.apache.ibatis.annotations.MapKey;
 7 
 8 public interface ClassStudentRepository {
 9 
10     // 获取一个班级的所有学生[Map<[Student Id], [Student Info]>]
11     @MapKey("studentId")
12     Map<Integer, Student> getStudentsFromOneClass(Integer classId);
13 
14     // 获取一个年级的所有学生
15     List<StudentsPerClass> getStudentsFromOneGrade(Integer gradeId);
16 
17     // 获取所有学生
18     @MapKey("classId")
19     Map<Integer, StudentsPerClass> getAllStudents();
20 }

ClassStudentRepository.xml

注意黄色部分非常重要，只有设置了1对多关系中的主键class_id，Mybatis才能自动映射这种1对多的嵌套关系。

 1 <?xml version="1.0" encoding="UTF-8" ?>
 2 <!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
 3 <mapper namespace="mybatistest.ClassStudentRepository">
 4     <!-- // 获取一个班级的所有学生[Map<[Student Id], [Student Info]>] -->
 5     <select id="getStudentsFromOneClass" resultMap="studentsFromOneClassMap">
 6         SELECT
 7             t2.student_id,
 8             t2.class_id,
 9             t2.student_name,
10             t2.student_age,
11             t2.student_address
12         FROM
13             tempdb..test_class t1
14         INNER JOIN
15             tempdb..test_student t2
16         ON
17             t1.class_id = t2.class_id
18         WHERE
19             t1.class_id = #{classId} 
20     </select>
21     <resultMap id="studentsFromOneClassMap" type="mybatistest.Student">
22         <id property="studentId" column="student_id" />
23         <result property="classId" column="class_id" />
24         <result property="studentName" column="student_name" />
25         <result property="studentAge" column="student_age" />
26         <result property="studentAddress" column="student_address" />
27     </resultMap>
28 
29     <!-- 获取一个年级的所有学生 -->
30     <select id="getStudentsFromOneGrade" resultMap="studentsFromOneGradeMap">
31         SELECT
32             t1.class_id,
33             t1.class_name,
34             t1.class_admin_name,
35             t2.student_id,
36             t2.student_name,
37             t2.student_age,
38             t2.student_address
39         FROM
40             tempdb..test_class t1
41         INNER JOIN
42             tempdb..test_student t2
43         ON
44             t1.class_id = t2.class_id
45         WHERE
46             t1.class_id/100 = #{gradeId} 
47     </select>
48     <resultMap id="studentsFromOneGradeMap" type="mybatistest.StudentsPerClass">
49         <id property="classId" column="class_id"/>
50         <association property="clazz" javaType="mybatistest.Clazz" >
51             <id property="classId" column="class_id"/>
52             <result property="className" column="class_name"/>
53             <result property="classAdminName" column="class_admin_name"/>
54         </association>
55         <collection property="students" ofType="mybatistest.Student">
56             <id property="studentId" column="student_id" />
57             <result property="classId" column="class_id"/>
58             <result property="studentName" column="student_name" />
59             <result property="studentAge" column="student_age" />
60             <result property="studentAddress" column="student_address" />
61         </collection>
62     </resultMap>
63 
64     <!-- 获取所有学生 -->
65     <select id="getAllStudents" resultMap="allStudentsMap">
66         SELECT
67             t1.class_id,
68             t1.class_name,
69             t1.class_admin_name,
70             t2.student_id,
71             t2.student_name,
72             t2.student_age,
73             t2.student_address
74         FROM
75             tempdb..test_class t1
76         INNER JOIN
77             tempdb..test_student t2
78         ON
79             t1.class_id = t2.class_id
80     </select>
81     <resultMap id="allStudentsMap" type="mybatistest.StudentsPerClass">
82         <id property="classId" column="class_id"/>
83         <association property="clazz" javaType="mybatistest.Clazz" >
84             <id property="classId" column="class_id"/>
85             <result property="className" column="class_name"/>
86             <result property="classAdminName" column="class_admin_name"/>
87         </association>
88         <collection property="students" ofType="mybatistest.Student">
89             <id property="studentId" column="student_id" />
90             <result property="classId" column="class_id"/>
91             <result property="studentName" column="student_name" />
92             <result property="studentAge" column="student_age" />
93             <result property="studentAddress" column="student_address" />
94         </collection>
95     </resultMap>
96 </mapper>

mybatis-config.xml中添加映射关系

1     <mappers>
2         <mapper resource="mybatistest/ClassStudentRepository.xml" />
3     </mappers>

<测试结果>

 1 package mybatistest;
 2 
 3 import java.util.List;
 4 import java.util.Map;
 5 
 6 import org.apache.ibatis.session.SqlSession;
 7 import org.apache.ibatis.session.SqlSessionFactory;
 8 
 9 public class MainTest {
10 
11     public static void main(String[] args) {
12         SqlSessionFactory sqlSessionFactory = MybatisTestSessionFactory.getSqlSessionFactoryInstance();
13         SqlSession session = sqlSessionFactory.openSession();
14         ClassStudentRepository classStudentRepository = session.getMapper(ClassStudentRepository.class);
15         Map<Integer, Student> res1 = classStudentRepository.getStudentsFromOneClass(102);
16         res1.forEach((p, q) -> {
17             System.out.println("Student ID: " + p + "; Student info: " + q);
18         });
19         // [Output]
20         // Student ID: 102001; Student info: Student [classId=102, studentId=102001, studentName=Name 4, studentAge=22, studentAddress=Address 4]
21         // Student ID: 102002; Student info: Student [classId=102, studentId=102002, studentName=Name 5, studentAge=21, studentAddress=Address 5]
22         List<StudentsPerClass> res2 = classStudentRepository.getStudentsFromOneGrade(1);
23         res2.forEach((p) -> {
24             System.out.println(p);
25         });
26         // [Output]
27         // StudentsPerClass [classId=101, clazz=Clazz [classId=101, className=Class 1, Grade 1, classAdminName=Mr. Zhang], students=
28         // --Student [classId=101, studentId=101001, studentName=Name 1, studentAge=20, studentAddress=Address 1]
29         // --Student [classId=101, studentId=101002, studentName=Name 2, studentAge=21, studentAddress=Address 2]
30         // --Student [classId=101, studentId=101003, studentName=Name 3, studentAge=20, studentAddress=Address 3]
31         // ]
32         // StudentsPerClass [classId=102, clazz=Clazz [classId=102, className=Class 2, Grade 1, classAdminName=Mr. Li], students=
33         // --Student [classId=102, studentId=102001, studentName=Name 4, studentAge=22, studentAddress=Address 4]
34         // --Student [classId=102, studentId=102002, studentName=Name 5, studentAge=21, studentAddress=Address 5]
35         // ]
36         Map<Integer, StudentsPerClass> res3 = classStudentRepository.getAllStudents();
37         res3.forEach((p, q) -> {
38             System.out.println("Class ID: " + p + System.lineSeparator() + q);
39         });
40         // [Output]
41         // Class ID: 101
42         // StudentsPerClass [classId=101, clazz=Clazz [classId=101, className=Class 1, Grade 1, classAdminName=Mr. Zhang], students=
43         // --Student [classId=101, studentId=101001, studentName=Name 1, studentAge=20, studentAddress=Address 1]
44         // --Student [classId=101, studentId=101002, studentName=Name 2, studentAge=21, studentAddress=Address 2]
45         // --Student [classId=101, studentId=101003, studentName=Name 3, studentAge=20, studentAddress=Address 3]
46         // ]
47         // Class ID: 102
48         // StudentsPerClass [classId=102, clazz=Clazz [classId=102, className=Class 2, Grade 1, classAdminName=Mr. Li], students=
49         // --Student [classId=102, studentId=102001, studentName=Name 4, studentAge=22, studentAddress=Address 4]
50         // --Student [classId=102, studentId=102002, studentName=Name 5, studentAge=21, studentAddress=Address 5]
51         // ]
52         // Class ID: 201
53         // StudentsPerClass [classId=201, clazz=Clazz [classId=201, className=Class 1, Grade 2, classAdminName=Mr. Zhao], students=
54         // --Student [classId=201, studentId=201001, studentName=Name 6, studentAge=23, studentAddress=Address 6]
55         // --Student [classId=201, studentId=201002, studentName=Name 7, studentAge=22, studentAddress=Address 7]
56         // ]
57         // Class ID: 202
58         // StudentsPerClass [classId=202, clazz=Clazz [classId=202, className=Class 2, Grade 2, classAdminName=Mr. Liu], students=
59         // --Student [classId=202, studentId=202001, studentName=Name 8, studentAge=22, studentAddress=Address 8]
60         // ]
61         session.close();
62     }
63 }

posted @ 2018-01-18 15:07 Storm_L 阅读(3185) 评论(1) 编辑收藏举报

会员力量，点亮园子希望

刷新页面返回顶部

Storm_L

Mybatis高级结果映射

公告