MyBatis结果集映射(ResultMap)

Mybatis的查询可以将查询出的结果集转换成Java对象。ResultMap有以下几种用途：

1. 如果数据表的列名和Java对象的属性名不一致，在ResultMap中可以进行关联
2. 定义一对一的关联
3. 定义一对多的关联

第一个用途很好理解。
第二个用途举例说明。假设我们有一张学生表信息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>