15-多对多做左连接查询（查询老师，并且把关联的学生也查出来）

多对多左连接查询

目录

• 左连接查询

---

 

老师对学生是多对多的关系，把中心放在老师上，一个老师可以教多个学生，实际上老师对学生也可以理解为一对多的关系。

左连接查询

场景：查询老师，并且把关联的学生也查出来。

Teacher.java 映射文件：

@Getter@Setter@ToString
public class Teacher {
    private Integer teacher_id;
    private String teacher_name;

    List<Student> students = new ArrayList<>(); // 一个老师对应多个学生
}

创建一个 TeacherMapper 接口文件，代码如下：

public interface TeacherMapper {
    /*查询老师，并且把关联的学生也查出来*/
    public List<Teacher> getAllTeachers();
}

创建一个 TeacherMapper 的映射文件：

<mapper namespace="com.itlike.mapper.TeacherMapper">

    <select id="getAllTeachers" resultMap="teacherMap">
        SELECT * FROM `teacher` as t
        LEFT JOIN `stu_teacher_rel` as r
        ON t.teacher_id = r.teacher_id
        LEFT JOIN `student` as s
        ON s.stu_id = r.stu_id;
    </select>

    <resultMap id="teacherMap" type="teacher">
        <id column="teacher_id" property="teacher_id"/>
        <result column="teacher_id" property="teacher_id"/>
        <collection property="students" javaType="list" ofType="student">
            <id column="stu_id" property="stu_id"/>
            <result column="stu_name" property="stu_name"/>
        </collection>
    </resultMap>
    
</mapper>

测试类中：

public void test(){
    SqlSession sqlSession = MybatisUtils.openSession();
    TeacherMapper teacherMapper = sqlSession.getMapper(TeacherMapper.class);
    List<Teacher> allTeachers = teacherMapper.getAllTeachers();
    for (Teacher teacher : allTeachers) {
        System.out.println(teacher);
    }

}

运行效果：与数据库查询结果相同。