MyBatis - (二) 一对一映射和一对多映射

1. 一对一映射

例子表：

学生表

地址表

 

POJO类

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>

 

二. 一对多映射

老师表

课程表

 

POJO对象：

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);
}

 

技术改变世界