7.mybatis一对多关联查询

和第5节一对一查询类似，但是不同的是，一对一使用的是association，而一对多使用collection。

实例：

 

1个班级Class，对应1个老师Teacher，对应多个学生Student

1.建表如下：

class[cid, cname, tid]

teacher[tid, tname]

student[sid, sname, cid]

插入数据：

class：
 insert into class values(1,'一年级',1);
 insert into class values(2,'二年级',2);

teacher:
 insert into teacher values(1,'老师A');
 insert into teacher values(2,'老师B');

student:
 INSERT INTO `mybatis`.`student`(`sid`,`sname`,`cid`) VALUES ( 1,'学生A',1);
 INSERT INTO `mybatis`.`student`(`sid`,`sname`,`cid`) VALUES ( 2,'学生B',1);
 INSERT INTO `mybatis`.`student`(`sid`,`sname`,`cid`) VALUES ( 3,'学生C',1);
 INSERT INTO `mybatis`.`student`(`sid`,`sname`,`cid`) VALUES ( 4,'学生D',2);
 INSERT INTO `mybatis`.`student`(`sid`,`sname`,`cid`) VALUES ( 5,'学生E',2);

2.新建Java实体类：

Clazz类：

public class Clazz {
    private int id;
    private String name;
    private Teacher teacher;
    private List<Student> stuList;
    public Clazz() {
        super();
    }
    
    public Clazz(int id, String name, Teacher teacher, List<Student> stuList) {
        super();
        this.id = id;
        this.name = name;
        this.teacher = teacher;
        this.stuList = stuList;
    }

    public List<Student> getStuList() {
        return stuList;
    }

    public void setStuList(List<Student> stuList) {
        this.stuList = stuList;
    }

    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public Teacher getTeacher() {
        return teacher;
    }
    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }

    @Override
    public String toString() {
        return "Clazz [id=" + id + ", name=" + name + ", teacher=" + teacher
                + ", stuList=" + stuList + "]";
    }
    
}

Teacher：

public class Teacher {
    private int id;
    private String name;
    
    
    public Teacher() {
        super();
    }
    public Teacher(int id, String name) {
        super();
        this.id = id;
        this.name = name;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    @Override
    public String toString() {
        return "Teacher [id=" + id + ", name=" + name + "]";
    }
    
}

Teacher类：

public class Student {
    private int id;
    private String name;
    public Student(int id, String name) {
        super();
        this.id = id;
        this.name = name;
    }
    public Student() {
        super();
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    @Override
    public String toString() {
        return "Student [id=" + id + ", name=" + name + "]";
    }
    
    
}

3.要获取一个班级Clazz的完整关联信息，写clazzMapper.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的标签，在此要在Window-Preference-Xml Catalog中配置mybatis-3-mapper.dtd，
    key=-//mybatis.org//DTD Mapper 3.0//EN
 -->
<mapper namespace="com.mlxs.mybatis.test5.clazzMapper">
    
    <!-- 
         一对多 关联查询：
     
         方式一：嵌套结果：使用嵌套结果映射来处理重复的联合结果的子集
         SELECT * FROM class c, teacher t, student s WHERE c.tid=t.tid AND c.cid=s.cid AND c.cid=#{id}
      -->
    <!-- 查询班级及其老师信息 -->
    <select id="getClazz" parameterType="int" resultMap="getClazzMap">
        SELECT * FROM class c, teacher t, student s WHERE c.tid=t.tid AND c.cid=s.cid AND c.cid=#{id}
    </select>
    
    <resultMap type="Clazz" id="getClazzMap">
        <id property="id" column="cid"/>
        <result property="name" column="cname"/>
        <!-- 关联查询teacher 一对一 -->
        <association property="teacher" javaType="Teacher">
            <id property="id" column="tid"/>
            <result property="name" column="tname"/>
        </association>
        <!-- 关联班级对应的student 一对多 ofType代表集合中存放的对象类型-->
        <collection property="stuList" ofType="Student">
            <id property="id" column="sid"/>
            <result property="name" column="sname"/>
        </collection>
    </resultMap>
    
    
    <!-- 
         一对多 关联查询：
     
         方式二：嵌套查询：使用嵌套结果映射来处理重复的联合结果的子集
         SELECT * FROM class WHERE cid=#{id}   //查询得到tid、cid用于给下面2条语句使用
        SELECT * FROM teacher WHERE tid=#{id} //tid为第一条sql查询后的tid
        SELECT * FROM student WHERE cid=#{id} //cid为第一条sql查询后的cid
      -->
    <!-- 查询班级及其老师信息 -->
    <select id="getClazz2" parameterType="int" resultMap="getClazzMap2">
        SELECT * FROM class WHERE cid=#{id}
    </select>
    <!-- 这里必须写成别名形式SELECT tid id, tname name，否则查询结果为null -->
    <select id="getTeacher" parameterType="int" resultType="Teacher">
        SELECT tid id, tname name FROM teacher WHERE tid=#{id}
    </select>
    <select id="getStuList" parameterType="int" resultType="Student">
        SELECT sid id, sname name FROM student WHERE cid=#{id}
    </select>
    
    <resultMap type="Clazz" id="getClazzMap2">
        <id property="id" column="cid"/>
        <result property="name" column="cname"/>
        <!-- 关联查询teacher 一对一 -->
        <association property="teacher" column="tid" select="getTeacher"/>
        <!-- 关联班级对应的student 一对多 ofType代表集合中存放的对象类型-->
        <collection property="stuList" column="cid" select="getStuList"/>
    </resultMap>
    
    
</mapper>

4.写测试类：

public class _Test6OneToMany {
    
    /**
     * 方式一：嵌套结果
     * @author 魅力_小生
     *
     */
    @Test
    public void getClazz1(){
        //创建session，设置事务为true
        SqlSession session = MyBatisUtil.getSessionFactory().openSession(true);
        String statement = "com.mlxs.mybatis.test5.clazzMapper.getClazz";
        Clazz clazz = session.selectOne(statement, "1");
        System.out.println("clazz--->"+clazz);
        session.close();
    }
    /**
     * 方式二：嵌套查询，通过执行另外一个SQL 映射语句来返回预期的复杂类型
         SELECT * FROM class WHERE cid=#{id}   //查询得到tid、cid用于给下面2条语句使用
        SELECT * FROM teacher WHERE tid=#{id} //tid为第一条sql查询后的tid
        SELECT * FROM student WHERE cid=#{id} //cid为第一条sql查询后的cid
     * @author 魅力_小生
     *
     */
    @Test
    public void getClazz2(){
        //创建session，设置事务为true
        SqlSession session = MyBatisUtil.getSessionFactory().openSession(true);
        String statement = "com.mlxs.mybatis.test5.clazzMapper.getClazz2";
        Clazz clazz = session.selectOne(statement, "2");
        System.out.println("clazz--->"+clazz);
        session.close();
    }
    
    
}

5.结果：

getClazz1：
clazz--->Clazz [id=1, name=一年级, teacher=Teacher [id=1, name=老师A], stuList=[Student [id=1, name=学生A], Student [id=2, name=学生B], Student [id=3, name=学生C]]]



getClazz2：
clazz--->Clazz [id=2, name=二年级, teacher=Teacher [id=2, name=老师B], stuList=[Student [id=4, name=学生D], Student [id=5, name=学生E]]]