Mybatis—— 嵌套查询

1. 一对一映射

数据库表：

java bean:

public class Stu {
	private int id;
	private int clazzId;
	private String name;
	private String sex;
	private int age;
	
    private Score score;
    
    /*getter setter toString*/
}

public class Score {
	private int  id;
	private int  stuId;
	private int  math;
	private int  english;
	private int  pe;

    /*getter setter toString*/
}

public class Clazz {
  private int	 id;
  private String name;
  
  List<Stu> stus = new ArrayList<Stu>();
 /*getter setter toString*/
}

1.1 使用 ResultMap 实现一对一关系映射

XML code:

<resultMap type="stu" id="stuscore">
	<id property="id" column="id"/>
	<result property="clazzId" column="clazz_id"/>
	<result property="name" column="name"/>
	<result property="sex" column="sex"/>
	<result property="age" column="age"/>

	<result property="score.math" column="math"/>
	<result property="score.english" column="english"/>
	<result property="score.pe" column="pe"/>
</resultMap>


<select id="getAllStus01" resultMap="stuscore">
    select s.id,s.clazz_id,s.name,s.sex,s.age,c.math,c.english,c.pe from stu s,score c where c.stu_id=s.id
</select>

接口定义：

public List<Stu> getAllStus01();

 

1.2  使用 ResultMap extends 实现一对一关系映射

<resultMap type="stu" id="stus">
	<id property="id" column="id"/>
	<result property="clazzId" column="clazz_id"/>
	<result property="name" column="name"/>
	<result property="sex" column="sex"/>
	<result property="age" column="age"/>
</resultMap>

<resultMap id="stuScore" type="stu" extends="stus">
	<result property="score.math" column="math"/>
	<result property="score.english" column="english"/>
	<result property="score.pe" column="pe"/>
</resultMap>


<select id="getAllStus02" resultMap="stuScore">
  select s.id,s.clazz_id,s.name,s.sex,s.age,c.math,c.english,c.pe from stu s,score c where c.stu_id=s.id
</select>

接口定义：

public List<Stu> getAllStus02();

1.3  使用 ResultMap association 实现一对一关系映射

<resultMap type="stu" id="stus">
	<id property="id" column="id"/>
	<result property="clazzId" column="clazz_id"/>
	<result property="name" column="name"/>
	<result property="sex" column="sex"/>
	<result property="age" column="age"/>
	
	<association property="score" column="stu_id" javaType="Score">
		<id property="id" column="id"/>
		<result property="math" column="math"/>
		<result property="english" column="english"/>
		<result property="pe" column="pe"/>
	</association>
</resultMap>

<select id="getAllStus03" resultMap="stus">
  select s.id,s.clazz_id,s.name,s.sex,s.age,c.math,c.english,c.pe from stu s,score c where c.stu_id=s.id
</select>

接口定义：

public List<Stu> getAllStus03();

1.4  使用 ResultMap association 实现一对一关系映射(2)

<resultMap type="stu" id="stus">
	<id property="id" column="id"/>
	<result property="clazzId" column="clazz_id"/>
	<result property="name" column="name"/>
	<result property="sex" column="sex"/>
	<result property="age" column="age"/>
	<association property="score" resultMap="score"/>
</resultMap>

<resultMap id="score" type="Score">
	<id property="id" column="id"/>
	<result property="math" column="math"/>
	<result property="english" column="english"/>
	<result property="pe" column="pe"/>
</resultMap>

<select id="getAllStus04" resultMap="stus">
  select s.id,s.clazz_id,s.name,s.sex,s.age,c.math,c.english,c.pe from stu s,score c where c.stu_id=s.id
</select>

接口定义：

public List<Stu> getAllStus04();

1.5 多条 sql 语句实现一对一关系映射

<mapper namespace="com.mapper.StudentMapper">

    <resultMap type="stu" id="stus">
        <id property="id" column="id"/>
        <result property="clazzId" column="clazz_id"/>
        <result property="name" column="name"/>
        <result property="sex" column="sex"/>
        <result property="age" column="age"/>
        <association property="score" select="com.mapper.StudentMapper.findScoreById" column="id" />
    </resultMap>
    
    <select id="getAllStus05" resultMap="stus">
      select * from stu
    </select>

    <select id="findScoreById" resultType="score" parameterType="int">
        select * from score where stu_id=#{id}
    </select>

</mapper>

接口定义：

public List<Stu> getAllStus05();

2. 一对多映射

2.1 使用嵌套 Select 语句实现一对多映射

XML Code:

<resultMap id="stusMap" type="stu">
	<id property="id" column="id"/>
	<result property="clazzId" column="clazz_id"/>
	<result property="name" column="name"/>
	<result property="sex" column="sex"/>
	<result property="age" column="age"/>
	<association property="score" select="findScorebyid" column="id"/>
	<association property="clazz" select="findClazzById" column="clazz_id" fetchType="lazy"/>
</resultMap>

<select id="getAllStu" resultMap="stusMap">
	select * from stu
</select>
<select id="findScorebyid" resultType="Score" parameterType="int">
	select * from score where stu_id=#{id}
</select>
<select id="findClazzById" resultType="Clazz" parameterType="int">
	select * from clazz where id = #{id};
</select>

【注】<association property="score" select="findScorebyid" column="id"/> 

接口定义：

public List<Stu> getAllStu();

2.2 使用内嵌结果 ResultMap 实现一对多映射

XML:

<?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.mapper.StudentMapper">

    <resultMap id="stusMap" type="stu">
        <id property="id" column="id"/>
        <result property="clazzId" column="clazz_id"/>
        <result property="name" column="name"/>
        <result property="sex" column="sex"/>
        <result property="age" column="age"/>
        <association property="score" select="findScorebyid" column="id"/>
        <association property="clazz" select="findClazzById" column="clazz_id" fetchType="lazy"/>
    </resultMap>

    <resultMap id="classMap" type="Clazz">
        <id property="id" column="id"/>
        <result property="name" column="name"/>

        <collection property="stus" select="findStuByClassId" column="id" />
    </resultMap>


    <select id="getAllStu" resultMap="stusMap">
        select * from stu
    </select>
    <select id="findScorebyid" resultType="Score" parameterType="int">
        select * from score where stu_id=#{id}
    </select>
    <select id="findClazzById" resultType="Clazz" parameterType="int">
        select * from clazz where id = #{id};
    </select>

   
    <select id="getAllClazz" resultMap="classMap">
        select * from clazz;
    </select>
    <select id="findStuByClassId" parameterType="int" resultMap="stusMap">
        select * from stu where clazz_id = #{id}
    </select>
</mapper>

【注】<collection property="stus" select="findStuByClassId" column="id" />

接口定义：

 public List<Clazz> getAllClazz();

3. 多对多映射

 一个老师可以对应多个学生同时一个学生可以对应多名老师。这意味着老师和学生之间存在多对多的映射关系。可以用两个一对多的映射来实现多对多。

多对多要借用第三方表实现。

java bean: 

public class Teacher {
    private int id;
    private String name;
    private int age;
    private String sex;

    List<Stu> stus=new ArrayList<Stu>();
   
   /*getter setter toString*/
}

 

XML:

<?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.mapper.StudentMapper">

    <resultMap id="teachMap" type="Teacher">
        <id column="id" property="id"/>
        <result property="sex" column="sex"/>
        <result property="age" column="age"/>
        <result property="name" column="name"/>

        <collection property="stus" select="findStuByTeacherId" column="id" fetchType="lazy"/>
    </resultMap>

    <resultMap id="stusMap" type="stu">
        <id property="id" column="id"/>
        <result property="clazzId" column="clazz_id"/>
        <result property="name" column="name"/>
        <result property="sex" column="sex"/>
        <result property="age" column="age"/>
        <collection property="teas" select="findTeachByStuId" column="id" fetchType="lazy"/>
    </resultMap>

    <resultMap id="studentMap" type="stu">
        <result property="name" column="name"/>
        <result property="sex" column="sex"/>
        <result property="age" column="age"/>
    </resultMap>

    <select id="findTeachByStuId" parameterType="int" resultMap="teachMap" >
        select teacher.* from teacher,stu_tea  where stu_tea.stu_id = #{id} and stu_tea.tea_id = teacher.id
    </select>

    <select id="findStuByTeacherId" parameterType="int" resultMap="studentMap">
        select stu.* from stu,stu_tea  where stu_tea.tea_id = #{id} and stu_tea.stu_id = stu.id;
    </select>

    <select id="getAllStu" resultMap="stusMap" >
         select * from stu
    </select>

</mapper>

接口：

public List<Teacher> getAllStu();