Mybatis复习与总结（三）——级联查询

1.一对一

<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
  PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
  "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ywj.one_to_one.HusbandMapper">
    <!--
        方式一：嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集
            封装联表查询的数据(去除重复的数据)
     -->
	<!-- 丈夫和妻子一对一,查询丈夫并且级联查询妻子 -->
	<select id="findHusbandCascadeById" parameterType="java.lang.Integer" resultMap="resultMap1">
		select h.id hid,h.name hname,
			   w.id wid,w.name wname 
		from wife w,husband h
		where w.id = h.wife_id and h.id = #{id}
	</select>
	<resultMap type="com.ywj.beanHusband" id="resultMap1">
		<result column="hid" property="id"/>
		<result column="hname" property="name"/>
 		<association property="wife" javaType="com.ywj.bean.Wife">
			<result column="wid" property="id"/>
			<result column="wname" property="name"/>
		</association>
	</resultMap>
    
    <!--
	         方式二：嵌套查询：通过执行另外一个SQL映射语句来返回预期的复杂类型
    -->
    <select id="findHusbandById" parameterType="java.lang.Integer" resultMap="resultMap2">
        select h.id hid,h.name hname,h.wife_id wid
        from husband h where h.id = #{id}
    </select>
    <resultMap type="com.ywj.beanHusband" id="resultMap2">
        <result column="hid" property="id"/>
		<result column="hname" property="name"/>
 		<association property="wife" javaType="com.ywj.bean.Wife" column="wid" select="findWifeById">
		</association>
    </resultMap>
    <select id="findWifeById" parameterType="java.lang.Integer" resultType="com.ywj.bean.Wife">
        select * from wife where id = #{id}
    </select>
</mapper>

2.一对多

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<!-- 一对多映射文件 -->
<mapper namespace="com.ywj.one_to_many.GroupMapper">

	<!-- 嵌套结果 -->
	<!-- 团队和成员为一对多的关系，查询团队级联查询出该团队下的成员 -->
	<select id="findGroupCascadeById" resultMap="result" parameterType="int">
		select u.id uid,u.name uname,
			   g.id gid,g.name gname
		from s_group g,s_user u
		where u.group_id=g.id and g.id =#{id}
	</select>
	<resultMap type="com.ywj.bean.Group" id="result">
		<result column="gid" property="id"/>
		<result column="gname" property="name"/>
		<!-- 封装得到一个集合 -->
		<collection property="users" ofType="com.ywj.bean.User" javaType="java.util.List">
			<result column="uid" property="id"/>
			<result column="uname" property="name"/>
		</collection>
	</resultMap>
		
	<!-- 嵌套查询 -->
	<select id="findGroupById" parameterType="int" resultMap="select">
		select * from s_group where id=#{id}
	</select>
	<resultMap type="com.ywj.bean.Group" id="select">
		<result column="id" property="id"/>
		<result column="name" property="name"/>
		<collection property="users" select="findUserByGroupId" column="id">	
		</collection>
	</resultMap>
	<!-- 通过团队id查询成员 -->
	<select id="findUserByGroupId" parameterType="int" resultType="com.ywj.bean.User">
		select * from s_user where group_id = #{id}
	</select>
	
</mapper>

3.多对多

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mapper PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN" 
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">

<mapper namespace="com.ywj.one_to_many.GroupMapper">

	<!-- 嵌套结果 -->
	<!-- 学生和课程为多对多的关系，通过学生级联查询出该学生所修的全部课程 -->
	<select id="findStudentCascadeById" resultMap="result" parameterType="int">
		select s.id sid,s.name sname,
			   c.id cid,c.name cname
		from student s,course c
		where s.course_id=c.id and s.id =#{id}
	</select>
	<resultMap type="com.ywj.bean.Student" id="result">
		<result column="sid" property="id"/>
		<result column="sname" property="name"/>
		<!-- 封装得到一个集合 -->
		<collection property="courses" ofType="com.ywj.bean.Course" javaType="java.util.List">
			<result column="cid" property="id"/>
			<result column="cname" property="name"/>
		</collection>
	</resultMap>
		
	<!-- 嵌套查询 -->
	<select id="findStudentById" parameterType="int" resultMap="select">
		select * from student where id=#{id}
	</select>
	<resultMap type="com.ywj.bean.Student" id="select">
		<result column="id" property="id"/>
		<result column="name" property="name"/>
		<collection property="courses" select="findCourseByStudentId" column="id">	
		</collection>
	</resultMap>
	<!-- 通过学生id查询课程 -->
	<select id="findCourseByStudentId" parameterType="int" resultType="com.ywj.bean.Course">
		select * from course where student_id = #{id}
	</select>
	
	<!-- 通过课程级联查询出该课程对应的所有学生 同理-->
</mapper>