Mybatis五----关联查询（4）多对多查询

多对多关系，例如学生跟课程之间，就是多对多关系。

实体类：

package com.mybatisdemo.beans;

import java.util.LinkedHashSet;
import java.util.Set;

public class Student {

    private Integer id;
    private String name;
    private Integer age;
    private Teacher teacher;
    
    private Set<Courses> courses = new LinkedHashSet<>();

    public Student() {
        super();
        // TODO Auto-generated constructor stub
    }

    public Student(String name, Integer age, Teacher teacher) {
        super();
        this.name = name;
        this.age = age;
        this.teacher = teacher;
    }

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public Integer getAge() {
        return age;
    }

    public void setAge(Integer age) {
        this.age = age;
    }

    public Teacher getTeacher() {
        return teacher;
    }

    public void setTeacher(Teacher teacher) {
        this.teacher = teacher;
    }

    public Set<Courses> getCourses() {
        return courses;
    }

    public void setCourses(Set<Courses> courses) {
        this.courses = courses;
    }

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

}

package com.mybatisdemo.beans;

public class Courses {

    private Integer id;
    private String name;

    public Integer getId() {
        return id;
    }

    public void setId(Integer id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

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

}

Dao层对应映射配置文件：

<resultMap type="com.mybatisdemo.beans.Student" id="stuCourseMap">
        <id column="id" property="id"/>
        <result column="name" property="name"/>
        <result column="age" property="age"/>
        <collection property="courses" ofType="com.mybatisdemo.beans.Courses">
            <id column="course_id" property="id"/>
            <result column="course_name" property="name"/>
        </collection>
    </resultMap>

    <select id="queryStudentById" parameterType="int" resultMap="studentMap">
        select id,name,age,teacher from student where id = #{id}
    </select>

    <select id="queryStudentAndCoursesById" parameterType="int" resultMap="stuCourseMap">
        select a.id,a.name,a.age,b.id as course_id,b.name as course_name from 
            student a,courses b, stu_course_rel r where a.id = r.stu_id and b.id = r.course_id and a.id = #{id}
    </select>

测试类：

    @Test
    public void testSelectStudentAndCourse() {
        StudentDao studentDao = sqlSession.getMapper(StudentDao.class);
        Student student = studentDao.queryStudentAndCoursesById(1);
        System.out.println(student);
    }    

PS：数据库表结构

create table if not exists courses(
    id int(10) not null primary key,
    name varchar(25)
) charset utf8;

create table if not exists stu_course_rel(
    stu_id int(10),
    course_id int(10),
    foreign key(stu_id) references student(id),
    foreign key(course_id) references courses(id)
) charset utf8;