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>