ResultMap 关联映射
使用ResultMap来进行关联查询,是利用主键表和外键表的关系来处理的,它相比前面的少写了一条sql语句,效率相对较高;
通过表连接查询将所需要的数据一并查询出来,然后再通过ResultMap将结果集映射到对应的对象中;单个对象==>association,集合==>collection;
1)写sql 查询需要的数据
2)配置ResultMap
①查询学生和班级信息,并通过ResultMap映射到单个Student类中;
//查询学生和班级信息,并通过resultMap映射到单个Student类中 Student2 selectWithClassMapping(Integer id);
<resultMap id="commonResultMap" type="Student2"> <id column="id" property="id"></id> <result property="studentName" column="name"></result> <result property="classid" column="classid"></result> <result property="version" column="version"></result> </resultMap> <resultMap id="studentClassMapping" type="Student2" extends="commonResultMap"> <association property="hClass" javaType="HClass"> <id column="classid" property="id"></id> <result column="classname" property="name"></result> <result column="city" property="city"></result> </association> </resultMap> <select id="selectWithClassMapping" resultMap="studentClassMapping"> select t1.*,t2.name classname,t2.city from h_student t1,h_class t2 where t1.classid=t2.id and t1.id=#{id} </select>
测试文件为:
System.out.println("=============测试查询学生和班级信息,并通过resultMap映射到单个Student类中======================"); Student2 student24 = mapper.selectWithClassMapping(2); System.out.println(student24); System.out.println(student24.gethClass());
②查询学生和学生详细信息,并通过ResultMap映射到单个StudentInfo类中;
//查询学生和学生详细信息,并通过resultMap映射到单个类中 Student2 selectWithStudentInfoMapping(Integer id);
<resultMap id="StudentInfoMapping" type="Student2"> <id property="id" column="id"></id> <result property="studentName" column="name"></result> <result property="classid" column="classid"></result> <result property="version" column="version"></result> <association property="studentInFo" javaType="StudentInFo"> <result property="age" column="age"></result> <result property="address" column="address"></result> </association> </resultMap> <select id="selectWithStudentInfoMapping" resultMap="StudentInfoMapping"> select t1.*,t2.age,t2.address from h_student t1,h_student_info t2 where t1.id=t2.id and t1.id=#{id} </select>
测试文件为:
System.out.println("==============测试学生和学生详细信息的映射================="); Student2 student2 = mapper.selectWithStudentInfoMapping(2); System.out.println(student2); System.out.println(student2.getStudentInFo());
③查询班级和学生信息,并通过ResultMap映射到Student类中;
//通过结果resultMap映射,复杂结果集 HClass selectWithStudentMapping(Integer id);
<!-- 复杂结果集映射--> <resultMap id="classStudentMapping" type="HClass"> <id column="id" property="id"></id> <result property="name" column="name"></result> <result property="city" column="city"></result> <collection property="student2s" ofType="Student2"> <id column="studentid" property="id"></id> <result column="studentname" property="studentName"></result> <result column="id" property="classid"></result> <result column="version" property="version"></result> </collection> </resultMap> <select id="selectWithStudentMapping" resultMap="classStudentMapping"> select t1.*,t2.id studentid,t2.name studentname,t2.version from h_class t1,h_student t2 where t1.id=t2.classid and t1.id=#{id} </select>
测试文件为:
System.out.println("============测试resultMap=========="); HClass hClass2 = mapper.selectWithStudentMapping(21); System.out.println(hClass2); System.out.println(hClass2.getStudent2s());
④通过班级查找老师,利用map映射;
//班级查找老师,通过map映射 HClass seletcHClassWithTeacher(Integer id);
<resultMap id="HClassWithTeacherMapping" type="HClass"> <id column="id" property="id"></id> <result property="name" column="name"></result> <result property="city" column="city"></result> <collection property="teachers" ofType="Teacher"> <id column="teacherids" property="id"></id> <result column="teachername" property="name"></result> <result column="age" property="age"></result> </collection> </resultMap> <select id="seletcHClassWithTeacher" resultMap="HClassWithTeacherMapping"> select t1.*,t2.id teacherids,t2.name teachername,t2.age from h_class t1,h_teacher t2,h_class_teacher t3 where t1.id=t3.classid and t2.id=t3.teacherid and t1.id=#{id} </select>
测试文件为:
System.out.println("========测试班级和老师之间的映射关系=============="); HClass hClass3 = mapper.seletcHClassWithTeacher(21); System.out.println(hClass3); System.out.println(hClass3.getTeachers());