mybatis（四）：Mapper XML 文件（resultMap）

resultMap是Mybatis最强大的元素，它可以将查询到的复杂数据（比如查询到几个表中数据）映射到一个结果集当中。

Mybatis处理“一对多”的关系时，需要用到associasion元素。处理”多对一“用collection元素来实现。

<!--column不做限制，可以为任意表的字段，而property须为type 定义的pojo属性-->
<resultMap id="唯一的标识" type="映射的pojo对象">
  <id column="表的主键字段，或者可以为查询语句中的别名字段" jdbcType="字段类型" property="映射pojo对象的主键属性" />
  <result column="表的一个字段（可以为任意表的一个字段）" jdbcType="字段类型" property="映射到pojo对象的一个属性（须为type定义的pojo对象中的一个属性）"/>
  <association property="pojo的一个对象属性" javaType="pojo关联的pojo对象">
    <id column="关联pojo对象对应表的主键字段" jdbcType="字段类型" property="关联pojo对象的主席属性"/>
    <result  column="任意表的字段" jdbcType="字段类型" property="关联pojo对象的属性"/>
  </association>
  <!-- 集合中的property须为oftype定义的pojo对象的属性-->
  <collection property="pojo的集合属性" ofType="集合中的pojo对象">
    <id column="集合中pojo对象对应的表的主键字段" jdbcType="字段类型" property="集合中pojo对象的主键属性" />
    <result column="可以为任意表的字段" jdbcType="字段类型" property="集合中的pojo对象的属性" />  
  </collection>
</resultMap>

1. 一对一映射

例子表：

学生表

地址表

public class Address
{
  private Integer addrId;
  private String street;
  private String city;
  private String state;
  private String zip;
  private String country;
  // setters & getters
}
public class Student
{
  private Integer studId;
  private String name;
  private String email;
     private PhoneNumber phone;
     private Address address;
     //setters & getters
}

方法一: 使用点符号和嵌套对象


<resultMap type="Student" id="StudentWithAddressResult">
    <id property="studId" column="stud_id"/>
    <result property="name" column="name"/>
    <result property="email" column="email"/>
    <result property="phone" column="phone"/>
    <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>


 映射接口:

public interface StudentMapper
{
 Student selectStudentWithAddress(int studId);
}

方法二: 使用嵌套ResultMap

使用<association>


<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>

association也可以用于内联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" 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>

方法三: 使用嵌套select

<association property="关联属性" column="数据库中外键id" select="嵌套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>
<select id="findAddressById" parameterType="int" resultMap="AddressResult">
     SELECT * FROM ADDRESSES WHERE ADDR_ID=#{id}
</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" column="addr_id" select="findAddressById"/>
</resultMap>
<select id="findStudentWithAddress" parameterType="int" resultMap="StudentWithAddressResult">
      SELECT * FROM STUDENTS WHERE STUD_ID=#{Id}
</select>

二. 一对多映射

老师表

课程表

public class Course
{
    private Integer courseId;
    private String name;
    private String description;
    private Date startDate;
    private Date endDate;
    private Integer tutorId;
    //setters & getters
}

public class Tutor
{
    private Integer tutorId;
    private String name;
    private String email;
    private Address address;
    
    private List<Course> courses;
    /setters & getters
}

方法一: 使用嵌套ResultMap + collection关键字


<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>

方法二: 使用嵌套collection + select


<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"/>
    <association property="address" resultMap="AddressResult"/> 

    <collection property="courses" column="tutor_id" select="findCoursesByTutor"/>
</resultMap>

<select id="findTutorById" parameterType="int" resultMap="TutorResult">
    SELECT T.TUTOR_ID, T.NAME AS TUTOR_NAME, EMAIL
    FROM TUTORS T 
    WHERE T.TUTOR_ID=#{tutorId}
</select>

<select id="findCoursesByTutor" parameterType="int" resultMap="CourseResult">
    SELECT * FROM COURSES WHERE TUTOR_ID=#{tutorId}
</select>

public interface TutorMapper
{
 Tutor findTutorById(int tutorId);
}