Mybatis的查询可以将查询出的结果集转换成Java对象。ResultMap有以下几种用途:
- 如果数据表的列名和Java对象的属性名不一致,在ResultMap中可以进行关联
- 定义一对一的关联
- 定义一对多的关联
第一个用途很好理解。
第二个用途举例说明。假设我们有一张学生表信息Students(studId,name,email,addressId),以及一张学生地址表StuAddress(addressId,country,province,city,details),Students(addressId)关联StuAddress(addressId)。我们可以这样来定义两个Java类
public class Student {
private int studId;
private String name;
private String email;
private int addressId;
//一对一的关联关系
private StuAddress address;
// getters and setters......
}
public class StuAddress {
private int addressId;
private String country;
private String province;
private String city;
private String details;
// getters and setters......
}
如果我们进行一个连接查询时,配置一下ResultMap,就可以将结果集直接映射到Student对象,该对象具有学生信息和地址信息,后面我将会介绍一对一映射的方式。
第三个用途举例说明。如果我们还有一个课程表Course(courseId,courseName,teacherName)以及一个学生选课表StuCourse(id,courseId,studentId),Course表保存所有的课程,而StuCourse表保存学生与课程的关联关系,这种关联关系是一对多。那么,我们需要一个Course类
public class Course {
private int courseId;
private String courseName;
private String teacherName;
//getters and setters......
}
我们的Student类也要加上一个关联属性,添加后我们的Student类就变成了这样
public class Student {
private int studId;
private String name;
private String email;
private int addressId;
//一对一的关联关系
private StuAddress address;
//一对多的关联关系
private List<Course> courses;
// getters and setters......
}
接下来我们来看看ResultMap的使用细节。
简单的用法
<resultMap id="StudentResult" type="Student">
<!--如果数据表的列名和Java类的属性名不一致,则可以这样配置-->
<id property="studId" column="stud_id"/>
<result property="name" column="name"/>
<result property="email" column="email"/>
</resultMap>
<select id="findAllStudents" resultMap="StudentResult" >
SELECT * FROM STUDENTS
</select>
如果想使用某个ResultMap作为返回值,则在select节点中应该使用resultMap而不是resultType。在<resultMap>
节点内,如果子节点为主键,则应该使用<id>
,而不是<result>
。
一对一的映射
<resultMap type="Student" id="StudentWithAddressResult">
<id property="studId" column="stud_id"/>
<result property="name" column="name"/>
<result property="email" column="email"/>
<result property="address.addrId" column="addr_id"/>
<result property="address.street" column="street"/>
<result property="address.city" column="city"/>
<result property="address.state" column="state"/>
<result property="address.zip" column="zip"/>
<result property="address.country" column="country"/>
</resultMap>
<select id="selectStudentWithAddress" parameterType="int"
resultMap="StudentWithAddressResult">
SELECT STUD_ID, NAME, EMAIL, A.ADDR_ID, STREET, CITY, STATE,
ZIP, COUNTRY
FROM STUDENTS S LEFT OUTER JOIN ADDRESSES A ON
S.ADDR_ID=A.ADDR_ID
WHERE STUD_ID=#{studId}
</select>
我们也可以使用嵌套的方式来指定一对一的映射关系
<resultMap type="Address" id="AddressResult">
<id property="addrId" column="addr_id"/>
<result property="street" column="street"/>
<result property="city" column="city"/>
<result property="state" column="state"/>
<result property="zip" column="zip"/>
<result property="country" column="country"/>
</resultMap>
<resultMap type="Student" id="StudentWithAddressResult">
<id property="studId" column="stud_id"/>
<result property="name" column="name"/>
<result property="email" column="email"/>
<association property="address" resultMap="AddressResult"/>
</resultMap>
<select id="findStudentWithAddress" parameterType="int"
resultMap="StudentWithAddressResult">
SELECT STUD_ID, NAME, EMAIL, A.ADDR_ID, STREET, CITY, STATE,
ZIP, COUNTRY
FROM STUDENTS S LEFT OUTER JOIN ADDRESSES A ON
S.ADDR_ID=A.ADDR_ID
WHERE STUD_ID=#{studId}
</select>
或者可以这样写
<resultMap type="Student" id="StudentWithAddressResult">
<id property="studId" column="stud_id"/>
<result property="name" column="name"/>
<result property="email" column="email"/>
<association property="address" javaType="Address">
<id property="addrId" column="addr_id"/>
<result property="street" column="street"/>
<result property="city" column="city"/>
<result property="state" column="state"/>
<result property="zip" column="zip"/>
<result property="country" column="country"/>
</association>
</resultMap>
一对多的映射
<resultMap type="Course" id="CourseResult">
<id column="course_id" property="courseId"/>
<result column="name" property="name"/>
<result column="description" property="description"/>
<result column="start_date" property="startDate"/>
<result column="end_date" property="endDate"/>
</resultMap>
<resultMap type="Tutor" id="TutorResult">
<id column="tutor_id" property="tutorId"/>
<result column="tutor_name" property="name"/>
<result column="email" property="email"/>
<!--一对多的关系体现在这里-->
<collection property="courses" resultMap="CourseResult"/>
</resultMap>
<select id="findTutorById" parameterType="int" resultMap="TutorResult">
SELECT T.TUTOR_ID, T.NAME AS TUTOR_NAME, EMAIL, C.COURSE_ID,
C.NAME, DESCRIPTION, START_DATE, END_DATE
FROM TUTORS T LEFT OUTER JOIN ADDRESSES A ON T.ADDR_ID=A.ADDR_ID
LEFT OUTER JOIN COURSES C ON T.TUTOR_ID=C.TUTOR_ID
WHERE T.TUTOR_ID=#{tutorId}
</select>