Mybatis框架-21：多表操作之多对多——分步查询

需求，使用分布查询，查询出指定老师信息，并把指定老师包含哪些学生也查出来

第一步：mapper接口

教师mapper

/**
     * 根据教师id查询指定的教师
     * @param teacherId 
     * @return
     */
    Teacher getTeacherWithId(Integer teacherId);

学生mapper

/**
     * 根据教师的id，查询教师下的所有学生
     * @param teacherId 
     * @return
     */
    List<Student> getStudentByTeacherId(Integer teacherId);

第二步：mapper.xml中SQL语句

teacher的sql：这里是使用resoultMap进行的分布查询

<resultMap id="getTeacherWithIdMapper" type="com.ctbu.domain.Teacher">
        <id property="teacherId" column="teacherId"/>
        <result property="teacherName" column="teacherName"/>
        <collection property="students" ofType="Student" select="com.ctbu.mapper.StudentMapper.getStudentByTeacherId" column="teacherId"/>

    </resultMap>
    <select id="getTeacherWithId" resultMap="getTeacherWithIdMapper">
        select * from `teacher` where teacherId = #{teacherId};
    </select>

student的sql：这里使用了范围查询，先传入teacher的id，根据teacherId查询出对应的学生id，再根据学生id查询出具体的学生信息

<select id="getStudentByTeacherId" resultType="com.ctbu.domain.Student">
        select * from `student` where
        studentId in (select studentId from
        `teacher_student` where teacherId=#{teacherId});
    </select>

第三步：测试类

@Test
    public void test2(){
        SqlSession session = MyUtils.openSession();
        TeacherMapper teacherMapper = session.getMapper(TeacherMapper.class);
        Teacher teacher = teacherMapper.getTeacherWithId(1);
        System.out.println(teacher);

        session.close();
    }