【MyBatis09】MyBatis处理关联关系(多表连接):多对多

3 多对多关系

此处采用学生和课程的关系来做演示:每一个学生可以选择多门课程,每一门课程也可以被多个学生所选择。

此时便需要建立第三张关系表,其中存储两张表中的外键,并设置联合主键。

3.1 数据准备

建表

create table t_students(
    id int primary key auto_increment,
    name varchar(50),
    sex varchar(1)
)default charset = utf8;
create table t_subjects(
    id int primary key auto_increment,
    name varchar(50),
    grade int
)default charset =utf8;
create table t_stu_sub(
  student_id int,
  subject_id int,
  foreign key (student_id) references t_students(id),
  foreign key (subject_id) references t_subjects(id),
  primary key (student_id,subject_id)
)default charset =utf8;
  • foreign key (student_id)...foreign key (subject_id)...

    • 外键关联两张表

  • primary key (student_id,subject_id)

    • student_id列和subject_id列的联合主键

    • student_id列单列可以重复,subject_id列单列可以重复

    • 但是双列的组合(student_id,subject_id)不可以重复

插入测试数据

insert into t_students values (1,"jack",'m'),(2,"carina",'f');
insert into t_subjects values (1001,"JavaSE",1),(1002,"JavaWeb",2);
insert into t_stu_sub values (1,1001),(1,1002),(2,1001),(2,1002);

 

建立对应实体

Students:

public class Students {
    private Integer id;
    private String name;
    private String sex;

    public Students() {
    }
    ......

Subject:

public class Subject {
    private Integer id;
    private String name;
    private Integer grade;

    public Subject() {
    }
    ......

3.2 添加关系属性

由于是多对对关系,因此两个类中每一个类都需要额外添加一个List类型的关系属性。

Students:

public class Students {
    private Integer id;
    private String name;
    private String sex;

    private List<Subject> subjects;

    public Students() {
    }
	......

Subject:

public class Subject {
    private Integer id;
    private String name;
    private Integer grade;

    private List<Students> students;

    public Subject() {
    }
    ......

3.3 进行关联查询

与一对多基本一致,此处仅展示Mapper.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.qianglj.test2.dao.StudentsDao">
    <resultMap id="stu_sub" type="Students">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="sex" column="sex"/>
        <collection property="subjects" ofType="Subject">
            <id property="id" column="subId"/>
            <result property="name" column="subName"/>
            <result property="grade" column="subGrade"/>
        </collection>
    </resultMap>
    <select id="queryStudentsById" resultMap="stu_sub">
        select tstu.id, tstu.name, tstu.sex,
               tsub.id subId, tsub.name subName, tsub.grade subGrade
        from t_students tstu
        join t_stu_sub tss on tstu.id = tss.student_id
        join t_subjects tsub on tss.subject_id = tsub.id
        where tstu.id =#{id};
    </select>
</mapper>

Subject同理:

<?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.qianglj.test2.dao.SubjectDao">
    <resultMap id="sub_stu" type="Subject">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <result property="grade" column="grade"/>
        <collection property="students" ofType="Students">
            <id property="id" column="stuId"/>
            <result property="name" column="stuName"/>
            <result property="sex" column="stuSex"/>
        </collection>
    </resultMap>
    <select id="querySubjectById" resultMap="sub_stu">
        select tsub.id, tsub.name, tsub.grade,
               tstu.id stuId, tstu.name stuName, tstu.sex stuSex
        from t_subjects tsub
        join t_stu_sub tss on tsub.id = tss.subject_id
        join t_students tstu on tss.student_id = tstu.id
        where tsub.id = #{id}
    </select>
</mapper>

版权声明:
作者:jackqiang
链接:http://www.jackqiang.com/framework/mybatis/1946/many2many/
来源:JackQiang's
文章版权归作者所有,未经允许请勿转载。

THE END
分享
二维码
< <上一篇
下一篇>>
文章目录
关闭
目 录