=========================
MyBatis关联映射之多多关联
=========================

学生<->课程

1 数据表

1.1 学生

create table t_student(
	id int primary key auto_increment,
	number char(5) not null unique,
	name varchar(20) not null,
	gender char(2) not null,
	age int not null
);

1.2 课程

create table t_course(
    cid int primary key auto_increment,
    cname varchar(50) not null unique
);

1.3 分数

create table t_score(
    student int not null,
    course int not null,
    score int not null
);

2 实体类

2.1 学生

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Student {
    private int    id;
    private String number;
    private String name;
    private String gender;
    private int    age;
}

2.2 课程

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Course {
    private int cid;
    private String cname;
}

2.3 分数

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Score {
    private int student;
    private int course;
    private int score;
}

3 DAO

3.1 学生

public interface StudentDao {
    public int addStudent(Student student);
}

3.2 课程

public interface CourseDao {
    public int addCourse(Course course);
}

3.3 分数

public interface ScoreDao {
    public int addScore(Score score);
}

4 映射

<typeAliases>
    ...
    <typeAlias type="cn.tedu.association.pojo.Student" alias="Student" />
    <typeAlias type="cn.tedu.association.pojo.Course" alias="Course" />
    <typeAlias type="cn.tedu.association.pojo.Score" alias="Score" />
    ...
</typeAliases>

<mappers>
    ...
    <mapper resource="mappers/StudentMapper.xml" />
    <mapper resource="mappers/CourseMapper.xml" />
    <mapper resource="mappers/ScoreMapper.xml" />
    ...
</mappers>

4.1 学生

<mapper namespace="cn.tedu.association.dao.StudentDao">
    <insert id="addStudent" useGeneratedKeys="true" keyProperty="id">
        insert into t_student(number, name, gender, age)
        values(#{number}, #{name}, #{gender}, #{age})
    </insert>
</mapper>

4.2 课程

<mapper namespace="cn.tedu.association.dao.CourseDao">
    <insert id="addCourse" useGeneratedKeys="true" keyProperty="cid">
        insert into t_course(cname) values(#{cname})
    </insert>
</mapper>

4.3 分数

<mapper namespace="cn.tedu.association.dao.ScoreDao">
    <insert id="addScore">
        insert into t_score(student, course, score)
        values(#{student}, #{course}, #{score})
    </insert>
</mapper>

5 测试

public class StudentDaoTest {
    @Test
    public void testAddStudent() {
        StudentDao studentDao = MyBatisUtil.getMapper(StudentDao.class);
        Student caocao = new Student(0, "2001", "曹操", "男", 50);
        assertEquals(1, studentDao.addStudent(caocao));
        Student liubei = new Student(0, "2002", "刘备", "男", 40);
        assertEquals(1, studentDao.addStudent(liubei));
        Student sunquan = new Student(0, "2003", "孙权", "男", 30);
        assertEquals(1, studentDao.addStudent(sunquan));

        CourseDao courseDao = MyBatisUtil.getMapper(CourseDao.class);
        Course chinese = new Course(0, "语文");
        assertEquals(1, courseDao.addCourse(chinese));
        Course math = new Course(0, "数学");
        assertEquals(1, courseDao.addCourse(math));
        Course english = new Course(0, "英语");
        assertEquals(1, courseDao.addCourse(english));

        ScoreDao scoreDao = MyBatisUtil.getMapper(ScoreDao.class);
        assertEquals(1, scoreDao.addScore(
            new Score(caocao.getId(), chinese.getCid(), 50)));
        assertEquals(1, scoreDao.addScore(
            new Score(caocao.getId(), math.getCid(), 60)));
        assertEquals(1, scoreDao.addScore(
            new Score(liubei.getId(), math.getCid(), 70)));
        assertEquals(1, scoreDao.addScore(
            new Score(liubei.getId(), english.getCid(), 80)));
        assertEquals(1, scoreDao.addScore(
            new Score(sunquan.getId(), english.getCid(), 90)));
        assertEquals(1, scoreDao.addScore(
            new Score(sunquan.getId(), chinese.getCid(), 100)));
    }
}

运行测试用例。

例程：Association

6 查询课程附带学生

6.1 连接查询

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class CourseStudents {
    private Course course;
    private List<Student> students;
}

public interface CourseDao {
    ...
    public CourseStudents getCourseStudents(String cname);
    ...
}

<typeAliases>
    ...
    <typeAlias type="cn.tedu.association.pojo.CourseStudents" alias="CourseStudents" />
    ...
</typeAliases>

<mapper namespace="cn.tedu.association.dao.CourseDao">
    ...
    <!-- 连接查询 -->
    <resultMap id="courseStudents" type="CourseStudents">
        <result column="cid" property="course.cid" />
        <result column="cname" property="course.cname" />
        <collection property="students" ofType="Student">
            <result column="id" property="id" />
            <result column="number" property="number" />
            <result column="name" property="name" />
            <result column="gender" property="gender" />
            <result column="age" property="age" />
        </collection>
    </resultMap>
    <select id="getCourseStudents" resultMap="courseStudents">
        select cid, cname, id, number, name, gender, age
        from t_course inner join t_score inner join t_student
        on cid = course and student = id
        where cname = #{cname}
    </select>
    ...
</mapper>

public class CourseDaoTest {
    ...
    @Test
    public void testGetCourseStudents() {
        CourseDao courseDao = MyBatisUtil.getMapper(CourseDao.class);
        CourseStudents courseStudents = courseDao.getCourseStudents("语文");
        assertNotEquals(null, courseStudents);
        System.out.println(courseStudents);
    }
    ...
}

运行测试用例。

例程：Association

6.2 子查询

public interface StudentDao {
    ...
    public List<Student> getStudents(int course);
    ...
}

<mapper namespace="cn.tedu.association.dao.StudentDao">
    ...
    <select id="getStudents" resultType="Student">
        select id, number, name, gender, age
        from t_student inner join t_score on id = student
        where course = #{course}
    </select>
    ...
</mapper>

<mapper namespace="cn.tedu.association.dao.CourseDao">
    ...
    <!-- 子查询 -->
    <resultMap id="courseStudents" type="CourseStudents">
        <result column="cid" property="course.cid" />
        <result column="cname" property="course.cname" />
        <association column="cid" property="students"
                     select="cn.tedu.association.dao.StudentDao.getStudents" />
    </resultMap>
    <select id="getCourseStudents" resultMap="courseStudents">
        select cid, cname from t_course where cname = #{cname}
    </select>
    ...
</mapper>

运行测试用例。

例程：Association

7 查询学生附带课程

7.1 连接查询

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class StudentCourses {
    private Student student;
    private List<Course> courses;
}

public interface StudentDao {
    ...
    public StudentCourses getStudentCourses(String number);
    ...
}

<typeAliases>
    ...
    <typeAlias type="cn.tedu.association.pojo.StudentCourses" alias="StudentCourses" />
    ...
</typeAliases>

<mapper namespace="cn.tedu.association.dao.StudentDao">
    ...
    <!-- 连接查询 -->
    <resultMap id="studentCourses" type="StudentCourses">
        <result column="id" property="student.id" />
        <result column="number" property="student.number" />
        <result column="name" property="student.name" />
        <result column="gender" property="student.gender" />
        <result column="age" property="student.age" />
        <collection property="courses" ofType="Course">
            <result column="cid" property="cid" />
            <result column="cname" property="cname" />
        </collection>
    </resultMap>
    <select id="getStudentCourses" resultMap="studentCourses">
        select id, number, name, gender, age, cid, cname
        from t_student inner join t_score inner join t_course
        on id = student and course = cid
        where number = #{number}
    </select>
    ...
</mapper>

public class StudentDaoTest {
    ...
    @Test
    public void testGetStudentCourses() {
        StudentDao studentDao = MyBatisUtil.getMapper(StudentDao.class);
        StudentCourses studentCourses = studentDao.getStudentCourses("2001");
        assertNotEquals(null, studentCourses);
        System.out.println(studentCourses);
    }
    ...
}

运行测试用例。

例程：Association

7.2 子查询

public interface CourseDao {
    ...
    public List<Course> getCourses(int student);
    ...
}

<mapper namespace="cn.tedu.association.dao.CourseDao">
    ...
    <select id="getCourses" resultType="Course">
        select cid, cname
        from t_course inner join t_score on cid = course
        where student = #{student}
    </select>
    ...
</mapper>

<mapper namespace="cn.tedu.association.dao.StudentDao">
    ...
    <!-- 子查询 -->
    <resultMap id="studentCourses" type="StudentCourses">
        <result column="id" property="student.id" />
        <result column="number" property="student.number" />
        <result column="name" property="student.name" />
        <result column="gender" property="student.gender" />
        <result column="age" property="student.age" />
        <association column="id" property="courses"
                     select="cn.tedu.association.dao.CourseDao.getCourses" />
    </resultMap>
    <select id="getStudentCourses" resultMap="studentCourses">
        select id, number, name, gender, age from t_student
        where number = #{number}
    </select>
    ...
</mapper>

运行测试用例。

例程：Association