mybatis如何写关系映射

一.一对一关系映射
1.(对象.属性名)的方式为内嵌的对象的属性赋值。

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

2.1嵌套结果ResultMap实现一对一关系映射

<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" /> 
		<result property="dob" column="dob"/>
		 <association property="address" resultMap="AddressResult" /> 
</resultMap>
<select id="findStudentWithAddress" parameterType="int" resultMap="StudentWithAddressResult"> 
		select stud_id, name, email,dob,phone, 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> 

2.2定义内联的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>

3.3嵌套查询select实现一对一关系映射

<resultMap id="AddressResult" type="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" /> 
</resultMap>
<select id="findAddressById" parameterType="int" resultMap="AddressResult"> 
			select * from addresses where addr_id=#{id} 
</select> 

<resultMap id="findStudentByIdWithAddress" type="Student"> 
		  <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="findStudentByIdWithAddress"> 
			select * from students where stud_id=#{id} 
</select> 

二.一对多映射
1.使用内嵌结果 ResultMap 实现一对多映射。

<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="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="name" property="name" /> 
		 <result column="email" property="email" /> 
		 <association property="address" resultMap="AddressResult" />
		 <collection property="courses" resultMap="CourseResult" /> 
</resultMap> 
		
<select id="findTutorById" parameterType="int" resultMap="TutorResult"> 
		select t.tutor_id, t.name, t.email, c.course_id, c.name, description, start_date, end_date 
		from tutors t left outer join address 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>

2.使用嵌套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="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" column="addr_id" select="findAddressById"></association>
		  <!-- 这里要注意:是把当前tutor_id表中列的值当做参数去执行findCoursesByTutor这个查询语句,最后把查询结果封装到Tutor类中的courses属性中 -->
		 <collection property="courses" column="tutor_id" select="findCoursesByTutor" /> 
</resultMap> 
		<select id="findTutorById" parameterType="int" resultMap="TutorResult"> 
			select *  
			from tutors
			where tutor_id=#{tutor_id} 
		</select>
<select id="findAddressById" parameterType="int" resultMap="AddressResult">
			select *
			from addresses
			where addr_id = #{addr_id}
</select>
<select id="findCoursesByTutor" parameterType="int" resultMap="CourseResult">
		   select * 
		   from courses 
		   where tutor_id=#{tutor_id} 
</select>

2.2定义内联的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> 
		  <collection property="courses" javaType="Course" > 
			<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" /> 
		  </collection>
</resultMap>

三.多对多映射

多对多中需要有一个作为桥表,在这里的桥表是id,学生id,课程id
<resultMap id="StudentResult" type="Student">
			<id property="id" column="id"/>
			<result property="name" column="name"/>
			<result property="gender" column="gender"/>
			<result property="major" column="major"/>
			<result property="grade" column="grade"/>
</resultMap>
<!-- 继承上面那个基本的映射,再扩展出级联查询 -->
<resultMap id="StudentResultWithCourses" type="Student" extends="StudentResult">
			<collection property="courses" resultMap="CourseResult"></collection>
</resultMap>
<!-- 这里特别要是的是column="cid" 这是和select语句中的 c.id as cid对应的 一定一定一定要对应起来 -->
<resultMap id="CourseResult" type="Course">
			<id property="id" column="cid"/>
			<result property="courseCode" column="course_code"/>
			<result property="courseName" column="course_name"/>
</resultMap>
<!-- 
		注意:查询语句的中的c.id as cid这个地方,避免名字相同出现查询结果不正确的情况
		同时在id="CourseResult"的resultMap中也有与这里对应的设置要特别特别注意
-->
<select id="getStudentByIdWithCourses" parameterType="int" resultMap="StudentResultWithCourses">
			select s.id,s.name,s.gender,s.major,s.grade,c.id as cid,c.course_code,c.course_name,sc.id,sc.student_id,sc.course_id
			from student s,course c,student_course sc
			where 
			s.id=#{id}
			and
			s.id=sc.student_id 
			and 
			sc.course_id=c.id
</select>