详解Mybatis一对多、多对一、多对多

utopia

发布于 2023-03-21 11:01:40

1.5K0

发布于 2023-03-21 11:01:40

文章被收录于专栏：Utopia

1、什么是关系型数据库？ {#content_views}

这个东西我仿佛在大学的《数据库原理》中学过，好吧，我不记得了，来整理一下。

是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。简单说来就是关系型数据库用了选择、投影、连接、并、交、差、除、增删查改等数学方法来实现对数据的存储和查询。可以用SQL语句方便的在一个表及其多个表之间做非常复杂的数据查询。安全性高。

所谓的关系模型指用二维表的形式表示实体和实体间联系的数据模型

关系型数据库的优势：

复杂查询可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。
事务支持使得对于安全性能很高的数据访问要求得以实现。

2、实例详解一对多关系

实际情况：比如一个部门有很多员工，一个班级有很多学生

这里我们接着前几篇的内容，所以用班级与学生来举例

新建教室表：classroom

use demo;
create table classroom(
cid int NOT NULL AUTO_INCREMENT,
cname varchar(30)  DEFAULT NULL,
PRIMARY KEY (cid)
)ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

删除原有的学生表，新建一个：student

use demo;
create table student(
id int NOT NULL AUTO_INCREMENT,
sname varchar(30)  DEFAULT NULL,
age int,
cid int NOT NUll,
PRIMARY KEY (id)
)ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

插入数据：

use demo;
INSERT INTO classroom VALUES (1,"班级一");
INSERT INTO classroom VALUES (2,"班级二");
INSERT INTO student VALUES (1,'学生 a', 10, 1);
INSERT INTO student VALUES (2,'学生 b', 10, 1);
INSERT INTO student VALUES (3,'学生 c', 9, 1);
INSERT INTO student VALUES (4,'学生 x', 11, 2);
INSERT INTO student VALUES (5,'学生 y', 12, 2);
INSERT INTO student VALUES (6,'学生 z', 11, 2);

新建教室类：

public class Classroom {

    private Integer cid;

    private String cname;

    private Set<Student> students;

    geeter... seeter...

    @Override
    public String toString() {
        return "Classroom{" +
                "cid=" + cid +
                ", cname='" + cname + '\'' +
                '}';
    }
}

学生类不变

新建mapper：

public interface ClassroomMapper {

    List<Classroom> listClassroom();
}

新建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.test.demo.mapper.ClassroomMapper">
<resultMap id="ClassroomResult" type="com.test.demo.domain.Classroom">
    <id column="cid" property="cid" />
    <result column="cname" property="cname" />

    <!-- 一对多的关系 -->
    <!-- property: 指的是集合属性的值,对应类中的属性名 ofType：指的是集合中元素的类型 -->
    <collection property="students" ofType="com.test.demo.domain.Student">
        <id column="id" property="id" />
        <result column="sname" property="sname" />
        <result column="age" property="age" />
    </collection>
</resultMap>
    
    <select id="listClassroom" resultMap="ClassroomResult">
        select c.*,s.* from classroom c left join student s on c.cid = s.cid
    </select>
</mapper>

新建Controller：新建一个页面，有无内容都可，数据输出在控制台

@RequestMapping("/classManage")
@Controller
public class ClassroomController {

    @Autowired
    ClassroomMapper classroomMapper;

    @RequestMapping("/listClassroom")
    public String listClassroom(){
        List<Classroom> classrooms = classroomMapper.listClassroom();
        for (Classroom c : classrooms) {
            System.out.println(c);
            Set<Student> students = c.getStudents();
            for (Student s : students) {
                System.out.println("\t"+s);
            }
        }
      return "views/listClassroom";
    }
}

访问：http://localhost:8080/classManage/listClassroom 查看控制台数据：

注意点：该注意的也就是XML中加了注释的部分，文末会总结一下

3、实例详解多对一关系

到这里应该也很清楚了，教室对学生是一对多，那反过来，学生对教室就是多对一关系。

首先学生类新增属性Classroom

   private Classroom classroom;

    public Classroom getClassroom() {
        return classroom;
    }

    public void setClassroom(Classroom classroom) {
        this.classroom = classroom;
    }

修改StudentController中查询学生的接口

    @RequestMapping("/listStudent")
    public String toStudentView(Model model){
        List<Student> students = studentMapper.findAll();
        for (Student s : students) {
            System.out.println(s+" 对应的班级是 \t "+ s.getClassroom());
        }
       // model.addAttribute("students",students);
        return "views/listStudent";
    }

修改StudentMapper.xml

    <resultMap id="studentResult" type="com.test.demo.domain.Student">
        <id  column="id" property="id"/>
        <result column="sname" property="sname"/>
        <result column="age" property="age"/>

        <!-- 多对一的关系 -->
        <!-- property: 指的是属性名称, javaType：指的是属性的类型 -->
        <association property="classroom" javaType="com.test.demo.domain.Classroom">
            <id column="cid" property="cid"/>
            <result column="cname" property="cname"/>
        </association>
    </resultMap>


    <select id="findAll" resultMap="studentResult">
        select c.*,s.* from classroom c left join student s on c.cid = s.cid
    </select>

访问http://localhost:8080/studentManage/listStudent 后查看控制台数据

4、实例解析多对多关系

一个教师可以教很多班级，一个班级可以有很多老师，也就形成了多对多的关系

新建教师表：

create table teacher (
  tid int(11) NOT NULL AUTO_INCREMENT,
  tname varchar(32) DEFAULT NULL,
  PRIMARY KEY (tid)
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

新建教师与班级关联表：

CREATE TABLE `teacher_class`  (
  `id` int(11) UNSIGNED NOT NULL AUTO_INCREMENT,
  `tid` int(11) UNSIGNED NOT NULL,
  `cid` int(11) UNSIGNED NOT NULL,
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci ROW_FORMAT = Dynamic;

插入数据：

INSERT INTO teacher VALUES (1,'英语老师');
INSERT INTO teacher VALUES (2,'数学老师');
 
INSERT INTO teacher_class VALUES (null, 1, 1);
INSERT INTO teacher_class VALUES (null, 1, 2);
INSERT INTO teacher_class VALUES (null, 2, 2);
INSERT INTO teacher_class VALUES (null, 2, 1);

新增教师类：

public class Teacher {

    private Integer tid;

    private String tname;

    private List<TeacherClass> teacherClasses;

     getter... setter...
}

新增教师与班级关联类：

public class TeacherClass {
    private Integer id;

    private Teacher teacher;

    private Classroom classroom;

   getter... setter...
}

新增mapper:

public interface TeacherMapper {

    List<Teacher> listTeacher();
}

新增XML: teacherMapper.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.test.demo.mapper.TeacherMapper">
    <resultMap id="teacherResult" type="com.test.demo.domain.Teacher">
        <id column="tid" property="tid"/>
        <result column="tname" property="tname"/>

        <collection property="teacherClasses" ofType="com.test.demo.domain.TeacherClass">
            <id column="id" property="id"/>
            <association property="classroom" javaType="com.test.demo.domain.Classroom">
                <id column="cid" property="cid"/>
                <result column="cname" property="cname"/>
            </association>
        </collection>
    </resultMap>
    
    <select id="listTeacher" resultMap="teacherResult">
        select t.*,c.* from teacher t left join teacher_class tc on t.tid = tc.tid
                                      left join classroom c on tc.cid = c.cid
    </select>
</mapper>

新增Controller:

@Controller
@RequestMapping("/teacherManage")
public class TeacherController {

    @Autowired
    TeacherMapper teacherMapper;

    @RequestMapping("/listTeacher")
    @ResponseBody
    public List<Teacher> listTeacher() {
        List<Teacher> teachers = teacherMapper.listTeacher();
        for (Teacher t : teachers) {
            System.out.println(t.getTname());
            List<TeacherClass> tcs = t.getTeacherClasses();
            for (TeacherClass tc : tcs) {
                System.out.format("\t%d\t%s\t%n", tc.getClassroom().getCid(), tc.getClassroom().getCname());
            }
        }
        return teachers;
    }
}

访问：http://localhost:8080/teacherManage/listTeacher 控制台数据：