1、什么是关系型数据库？ {#content_views}
这个东西我仿佛在大学的《数据库原理》中学过，好吧，我不记得了，来整理一下。
是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。简单说来就是关系型数据库用了选择、投影、连接、并、交、差、除、增删查改等数学方法来实现对数据的存储和查询。可以用SQL语句方便的在一个表及其多个表之间做非常复杂的数据查询。安全性高。
所谓的关系模型指用二维表的形式表示实体和实体间联系的数据模型
关系型数据库的优势：
- 复杂查询可以用SQL语句方便的在一个表以及多个表之间做非常复杂的数据查询。
- 事务支持使得对于安全性能很高的数据访问要求得以实现。
2、实例详解 一对多关系
实际情况：比如一个部门有很多员工，一个班级有很多学生
这里我们接着前几篇的内容，所以用班级与学生来举例
新建教室表：classroom
代码语言：javascript复制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
代码语言：javascript复制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;插入数据：
代码语言：javascript复制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);新建教室类：
代码语言：javascript复制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：
代码语言：javascript复制public interface ClassroomMapper {
List<Classroom> listClassroom();
}新建XML:
代码语言：javascript复制<?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： 新建一个页面，有无内容都可，数据输出在控制台
代码语言：javascript复制@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
代码语言：javascript复制 private Classroom classroom;
public Classroom getClassroom() {
return classroom;
}
public void setClassroom(Classroom classroom) {
this.classroom = classroom;
}修改StudentController中查询学生的接口
代码语言：javascript复制 @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
代码语言：javascript复制 <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、实例解析 多对多关系
一个教师可以教很多班级，一个班级可以有很多老师，也就形成了多对多的关系
新建教师表：
代码语言：javascript复制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;新建教师与班级关联表：
代码语言：javascript复制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;插入数据：
代码语言：javascript复制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);新增教师类：
代码语言：javascript复制public class Teacher {
private Integer tid;
private String tname;
private List<TeacherClass> teacherClasses;
getter... setter...
}新增教师与班级关联类：
代码语言：javascript复制public class TeacherClass {
private Integer id;
private Teacher teacher;
private Classroom classroom;
getter... setter...
}新增mapper:
代码语言：javascript复制public interface TeacherMapper {
List<Teacher> listTeacher();
}新增XML: teacherMapper.xml
代码语言：javascript复制<?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:
代码语言：javascript复制@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%dt%st%n", tc.getClassroom().getCid(), tc.getClassroom().getCname());
}
}
return teachers;
}
}访问：http://localhost:8080/teacherManage/listTeacher 控制台数据：
解析：
查询出所有的老师，再遍历查询该老师所教的班级；查询结果把tid、tname放在Teacher对象里
然后通过一对多的<collection>标签把id放在TeacherClass对象里
最后把cid、cname放到Classroom对象里
注意：
- 多对多时不存在修改关系的做法，基本上都是把旧的关系删除，再插入新的关系
- JavaType和ofType都是用来指定对象类型的，但是JavaType是用来指定pojo中属性的类型，而ofType指定的是 映射到list集合属性中pojo的类型
