10. 复杂结果集映射:一对多

和前面一样我们现在反过来，查询指定老师下有多少个学生该如何做？

10.1 编写SQL

查询教师编号为1的教师教的所有学生

select s.name sname,s.id sid,t.name tname,t.id tid
from student s,teacher t
where s.tid=t.id and t.id =1;

10.2 编写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.lu.dao.TeacherMapper">
    <select id="getTeacher" resultMap="TeacherStudent">
        select s.name sname, s.id sid, t.name tname, t.id tid
        from student s,
             teacher t
        where s.tid = t.id
          and t.id = #{tid};
    </select>
<!--查询所有老师下的学生,先查询老师-->
    <resultMap id="TeacherStudent" type="Teacher">
        <result property="id" column="tid" />
        <result property="name" column="tname"/>
        <collection property="students" ofType="Student">
            <result property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="tid"/>
            <association property="teacher" javaType="Teacher">
                <result property="id" column="tid"/>
                <result property="name" column="tname"/>
            </association>
        </collection>
    </resultMap>
</mapper>

这是一个简单的 多表查询的例子
需要注意的是这里的 一对多 是 用 collection 表示 oftype 表示 集合内元素类型。

10.3 测试

SqlSessionFactory sqlSessionFactory = new SqlSessionFactoryBuilder().build(Resources.getResourceAsStream("mybatis-config.xml"));
SqlSession sqlSession = sqlSessionFactory.openSession();
sqlSession.getMapper(TeacherMapper.class).getTeacher(1).getStudents().forEach(System.out::println);
sqlSession.close();

Student(id=1, name=Tom, tid=1, teacher=Teacher(id=1, name=Teacher Zhang, students=[]))
Student(id=2, name=Jack, tid=1, teacher=Teacher(id=1, name=Teacher Zhang, students=[]))
Student(id=3, name=Alice, tid=1, teacher=Teacher(id=1, name=Teacher Zhang, students=[]))
Resetting autocommit to true on JDBC Connection [com.mysql.cj.jdbc.ConnectionImpl@22fcf7ab]