1、准备工作

1.1、准备数据库表

我们需要准备五张表,分别为 学生表 edu_student, 课程表 edu_course ,专业表 edu_profession 、 选课表 edu_select_course 和 身份卡表 edu_card

其中,学生与身份卡为一对一关系,学生与课程表为多对多映射关系,选课表为负责让学生表与课程表建立关系,学生与专业表为多对一映射关系。

  • 学生表 edu_student
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
create table edu_student
(
student_id char(19) not null comment '学生id
'
primary key,
name varchar(200) not null comment '学生姓名',
card_id char(19) null comment '学生卡id',
profession_id char(19) null comment '专业id'
) comment '学生表';

INSERT INTO mybatis_exercise.edu_student (student_id, name, card_id, profession_id) VALUES ('1', 'A', '1', '1');
INSERT INTO mybatis_exercise.edu_student (student_id, name, card_id, profession_id) VALUES ('2', 'B', '2', '1');
INSERT INTO mybatis_exercise.edu_student (student_id, name, card_id, profession_id) VALUES ('3', 'C', '3', '2');
INSERT INTO mybatis_exercise.edu_student (student_id, name, card_id, profession_id) VALUES ('4', 'D', '4', '3');
INSERT INTO mybatis_exercise.edu_student (student_id, name, card_id, profession_id) VALUES ('5', 'E', '5', '2');
  • 课程表 edu_course
1
2
3
4
5
6
7
8
9
10
create table edu_course
(
course_id char(19) not null comment '课程id'
primary key,
course_name varchar(500) null comment '课程名'
) comment '课程表';

INSERT INTO mybatis_exercise.edu_course (course_id, course_name) VALUES ('1', '运筹学');
INSERT INTO mybatis_exercise.edu_course (course_id, course_name) VALUES ('2', 'Java');
INSERT INTO mybatis_exercise.edu_course (course_id, course_name) VALUES ('3', '数据库');
  • 选课表 edu_select_course
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create table edu_select_course
(
sc_id char(19) not null comment '选课表id'
primary key,
course_id char(19) null comment '课程id',
student_id char(19) null comment '学生id',
score double null comment '成绩'
) comment '选课表';

INSERT INTO mybatis_exercise.edu_select_course (sc_id, course_id, student_id, score) VALUES ('1', '1', '1', 80);
INSERT INTO mybatis_exercise.edu_select_course (sc_id, course_id, student_id, score) VALUES ('2', '1', '2', 85);
INSERT INTO mybatis_exercise.edu_select_course (sc_id, course_id, student_id, score) VALUES ('3', '1', '3', 85);
INSERT INTO mybatis_exercise.edu_select_course (sc_id, course_id, student_id, score) VALUES ('4', '2', '1', 70);
INSERT INTO mybatis_exercise.edu_select_course (sc_id, course_id, student_id, score) VALUES ('5', '3', '1', 60);
INSERT INTO mybatis_exercise.edu_select_course (sc_id, course_id, student_id, score) VALUES ('6', '2', '2', 60);
INSERT INTO mybatis_exercise.edu_select_course (sc_id, course_id, student_id, score) VALUES ('7', '2', '3', 70);
INSERT INTO mybatis_exercise.edu_select_course (sc_id, course_id, student_id, score) VALUES ('8', '3', '3', 90);
  • 专业表 edu_profession
1
2
3
4
5
6
7
8
9
create table edu_profession
(
profession_id char(19) not null comment '专业id'
primary key,
profession_name varchar(50) null comment '专业名称'
) comment '专业表';
INSERT INTO mybatis_exercise.edu_profession (profession_id, profession_name) VALUES ('1', '信息与计算科学');
INSERT INTO mybatis_exercise.edu_profession (profession_id, profession_name) VALUES ('2', '数学与应用数学');
INSERT INTO mybatis_exercise.edu_profession (profession_id, profession_name) VALUES ('3', '应用统计学');
  • 身份卡表 edu_card
1
2
3
4
5
6
7
8
9
10
11
12
create table edu_card
(
card_id char(19) not null comment '学生卡id'
primary key,
info varchar(500) null comment '学生信息'
) comment 'id卡表';

INSERT INTO mybatis_exercise.edu_card (card_id, info) VALUES ('1', '这是一号卡');
INSERT INTO mybatis_exercise.edu_card (card_id, info) VALUES ('2', '这是二号卡');
INSERT INTO mybatis_exercise.edu_card (card_id, info) VALUES ('3', '这是三号卡');
INSERT INTO mybatis_exercise.edu_card (card_id, info) VALUES ('4', '这是四号卡');
INSERT INTO mybatis_exercise.edu_card (card_id, info) VALUES ('5', '这是五号卡');

1.2、创建测试工程

我们使用 Spring Boot + Mybatis-plus 来快速搭建本次学习所需项目

  • 使用Spring Initializr 快速构建Spring Boot项目
  • pom.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
<properties>
<project.build.sourceEncoding>UTF-8</project.build.sourceEncoding>
<encoding>UTF-8</encoding>
<java.version>1.8</java.version>
<maven.compiler.source>1.8</maven.compiler.source>
<maven.compiler.target>1.8</maven.compiler.target>
<mybatis-plus.version>3.3.1</mybatis-plus.version>
<velocity.version>2.0</velocity.version>
<mysql.version>8.0.19</mysql.version>
<springBoot.version>2.2.1.RELEASE</springBoot.version>
</properties>


<dependencies>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-web</artifactId>
<version>${springBoot.version}</version>
</dependency>

<!--mybatis-plus-->
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-boot-starter</artifactId>
<version>${mybatis-plus.version}</version>
</dependency>
<dependency>
<groupId>com.baomidou</groupId>
<artifactId>mybatis-plus-generator</artifactId>
<version>${mybatis-plus.version}</version>
</dependency>

<!--mysql-->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>${mysql.version}</version>
</dependency>
<!-- velocity 模板引擎, Mybatis Plus 代码生成器需要 -->
<dependency>
<groupId>org.apache.velocity</groupId>
<artifactId>velocity-engine-core</artifactId>
<version>${velocity.version}</version>
</dependency>
<dependency>
<groupId>io.springfox</groupId>
<artifactId>springfox-boot-starter</artifactId>
<version>3.0.0</version>
</dependency>

<!--lombok用来简化实体类:需要安装lombok插件-->
<dependency>
<groupId>org.projectlombok</groupId>
<artifactId>lombok</artifactId>
<version>1.18.16</version>
</dependency>
<!-- org.apache.commons.lang3.StringUtils -->
<dependency>
<groupId>org.apache.commons</groupId>
<artifactId>commons-lang3</artifactId>
<version>3.7</version>
</dependency>

<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-test</artifactId>
<version>${springBoot.version}</version>
<scope>test</scope>
<exclusions>
<exclusion>
<groupId>org.junit.vintage</groupId>
<artifactId>junit-vintage-engine</artifactId>
</exclusion>
</exclusions>
</dependency>
</dependencies>

在pom.xml文件中引入下面的代码,将java包下的xxxMapper.xml文件也编译进项目中

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
<build>
<plugins>
<plugin>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-maven-plugin</artifactId>
<configuration>
<fork>true</fork>
<addResources>true</addResources>
</configuration>
</plugin>
</plugins> <!--将mapper文件打包进去-->
<resources>
<resource> <!--指定根目录 到源文件夹 一般如下-->
<directory>src/main/java</directory>
<includes>
<include>**/*.yml</include>
<include>**/*.yaml</include>
<include>**/*.xml</include>
<include>**/*.properties</include>
</includes>
<filtering>false</filtering>
</resource>
<resource> <!--指定根目录 到源文件夹 一般如下-->
<directory>src/main/resources</directory>
<includes>
<include>**/*.yml</include>
<include>**/*.yaml</include>
<include>**/*.xml</include>
<include>**/*.properties</include>
</includes>
<filtering>false</filtering>
</resource>
</resources>
</build>
  • test 包下使用Mybatis-Plus的代码生成器生成controller、service、mapper和entity
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
public class CodeGenerator {

@Test
public void genCode() {
String moduleName = "edu";

// 1、创建代码生成器
AutoGenerator mpg = new AutoGenerator();

// 2、全局配置
GlobalConfig gc = new GlobalConfig();
String projectPath = System.getProperty("user.dir");
gc.setOutputDir(projectPath + "/src/main/java");
gc.setAuthor("five");
gc.setOpen(false); //生成后是否打开资源管理器
// gc.setFileOverride(false); //重新生成时文件是否覆盖
gc.setServiceName("%sService"); //去掉Service接口的首字母I
gc.setIdType(IdType.ASSIGN_ID); //主键策略
gc.setDateType(DateType.ONLY_DATE);//定义生成的实体类中日期类型
gc.setSwagger2(true);//开启Swagger2模式
mpg.setGlobalConfig(gc);

// 3、数据源配置
DataSourceConfig dsc = new DataSourceConfig();
dsc.setUrl("jdbc:mysql://localhost:3306/mybatis_exercise?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai");
dsc.setDriverName("com.mysql.cj.jdbc.Driver");
dsc.setUsername("root");
dsc.setPassword("tianxin1230.");
dsc.setDbType(DbType.MYSQL);
mpg.setDataSource(dsc);

// 4、包配置
PackageConfig pc = new PackageConfig();
pc.setModuleName(moduleName); //模块名
pc.setParent("com.five.wuhu");
pc.setController("controller");
pc.setEntity("entity");
pc.setService("service");
pc.setMapper("mapper");
mpg.setPackageInfo(pc);

// 5、策略配置
StrategyConfig strategy = new StrategyConfig();
strategy.setNaming(NamingStrategy.underline_to_camel);//数据库表映射到实体的命名策略
strategy.setTablePrefix(moduleName + "_");//设置表前缀不生成

strategy.setColumnNaming(NamingStrategy.underline_to_camel);//数据库表字段映射到实体的命名策略
strategy.setEntityLombokModel(true); // lombok 模型 @Accessors(chain = true) setter链式操作

strategy.setRestControllerStyle(true); //restful api风格控制器
strategy.setControllerMappingHyphenStyle(true); //url中驼峰转连字符
mpg.setStrategy(strategy);

// 6、执行
mpg.execute();
}
}
  • 执行代码生成器中的代码,生成项目结构如下

image-20210320152016401

1.3、添加主启动类

在主启动类中指定 Mapper 接口存放的文件夹,并进行包扫描

1
2
3
4
5
6
7
8
@SpringBootApplication(scanBasePackages = "com.five.wuhu")
@MapperScan("com.five.wuhu.edu.mapper")
public class EMSApplication {
public static void main(String[] args) {
SpringApplication.run(EMSApplication.class);
System.out.println("http://localhost:8080/swagger-ui/");
}
}

1.4、配置Swagger

在config包下创建 SwaggerConfiguration 类,添加以下代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
@Configuration
@EnableSwagger2
public class SwaggerConfiguration {

private ApiInfo webApiInfo(){

return new ApiInfoBuilder()
.title("网站-API文档")
.description("本文档描述了网站微服务接口定义")
.version("1.0")
.contact(new Contact("蔡大头", "http://wuhu.com", "763882220@qq.com"))
.build();
}

private ApiInfo adminApiInfo(){

return new ApiInfoBuilder()
.title("后台管理系统-API文档")
.description("本文档描述了后台管理系统微服务接口定义")
.version("1.0")
.contact(new Contact("蔡大头", "http://wuhu.com", "763882220@qq.com"))
.build();
}
}

1.5、创建配置文件并启动测试

  • resource 目录下创建 application.yml 配置文件,添加以下配置

这里需要根据你的环境进行修改

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
server:
port: 8080
spring:
jackson:
date-format: yyyy-MM-dd HH:mm:ss
time-zone: GMT+8
datasource:
driver-class-name: com.mysql.cj.jdbc.Driver
url: jdbc:mysql://localhost:3306/mybatis_exercise?useUnicode=true&characterEncoding=UTF-8&serverTimezone=Asia/Shanghai
password: tianxin1230.
username: root
# 打印sql语句
mybatis-plus:
configuration:
log-impl: org.apache.ibatis.logging.stdout.StdOutImpl
global-config:
db-column-underline: true
db-config:
logic-delete-value: 1
logic-not-delete-value: 0
mapper-locations: classpath:com/five/wuhu/edu/mapper/xml/*.xml
  • 启动测试

启动程序后 访问 http://localhost:8080/swagger-ui/ ,如果出现以下情况证明项目环境搭建完成

image-20210320152332311

2、映射测试

2.1、使用Mybatis进行一对一映射

  • StudentController 类中创建一个方法,这个方法返回所有学生以及每个学生对应的id卡信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
@RestController
@Api(tags = "学生控制器")
@RequestMapping("/edu/student")
public class StudentController {
@Autowired
private StudentService studentService;

@GetMapping("getAllStudentAndCardInfo")
@ApiOperation("获取所有学生信息及对应卡信息,一对一")
public Result getAllStudentAndCardInfo() {
List<StudentCardVo> studentCardVos = studentService.getAllStudentAndCardInfo();
return Result.ok().data("items",studentCardVos);
}
}
  • 创建一个vo类,这个类封装了学生以及对应id卡信息
1
2
3
4
5
6
@Data
public class StudentCardVo {
private String studentId;
private String name;
private Card card;
}
  • service层
1
2
3
4
5
6
7
@Service
public class StudentServiceImpl extends ServiceImpl<StudentMapper, Student> implements StudentService {
@Override
public List<StudentCardVo> getAllStudentAndCardInfo() {
return baseMapper.getAllStudentAndCardInfo();
}
}
  • Mapper接口层
1
2
3
4
@Repository
public interface StudentMapper extends BaseMapper<Student> {
List<StudentCardVo> getAllStudentAndCardInfo();
}
  • Mapper文件层

在一对一中,附带的“一”使用association标签进行修饰

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
<?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.five.wuhu.edu.mapper.StudentMapper">

<resultMap id="studentCardVo" type="com.five.wuhu.edu.entity.vo.StudentCardVo">
<id property="studentId" column="student_id" />
<result property="name" column="name" />
<association property="card" javaType="com.five.wuhu.edu.entity.Card" >
<id property="cardId" column="card_id" />
<result property="info" column="info" />
</association>
</resultMap>

<select id="getAllStudentAndCardInfo" resultMap="studentCardVo">
SELECT
s.student_id,
s.name as name,
c.info,
c.card_id
from
`mybatis_exercise`.edu_student as s
inner join
`mybatis_exercise`.edu_card as c
on
s.card_id = c.card_id
</select>
</mapper>
  • 使用 Swagger 进行测试

image-20210320153537831

  • 测试结果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
{
"success": true,
"code": 20000,
"message": "成功",
"data": {
"items": [
{
"studentId": "1",
"name": "A",
"card": {
"cardId": "1",
"info": "这是一号卡"
}
},
{
"studentId": "2",
"name": "B",
"card": {
"cardId": "2",
"info": "这是二号卡"
}
},
{
"studentId": "3",
"name": "C",
"card": {
"cardId": "3",
"info": "这是三号卡"
}
},
{
"studentId": "4",
"name": "D",
"card": {
"cardId": "4",
"info": "这是四号卡"
}
},
{
"studentId": "5",
"name": "E",
"card": {
"cardId": "5",
"info": "这是五号卡"
}
}
]
}
}

2.2、使用Mybatis进行一对多测试

查看一个专业下的所有学生

  • 创建一个vo类

注意,这里的泛型是 StudentCardVo 而不是Student,这是因为除了专业-学生这一映射外,我们还希望添加学生-id卡这一映射

1
2
3
4
5
6
@Data
public class ProfessionStudentVo {
private String professionId;
private String professionName;
private List<StudentCardVo> studentCardVos;
}
  • Controller
1
2
3
4
5
6
7
8
9
10
11
12
13
14
@RestController
@Api(tags = "专业控制器")
@RequestMapping("/edu/profession")
public class ProfessionController {
@Autowired
private ProfessionService professionService;

@GetMapping("getAllProfessionAndStudents")
@ApiOperation("查询全部专业及专业下的所有学生,一对多")
public Result getAllProfessionAndStudents() {
List<ProfessionStudentVo> professionStudentVos = professionService.getAllProfessionAndStudents();
return Result.ok().data("items",professionStudentVos);
}
}

由于Service层只是简单调用Mapper层的方法,所以我们直接忽略Service层的代码

  • Mapper接口
1
2
3
4
@Repository
public interface ProfessionMapper extends BaseMapper<Profession> {
List<ProfessionStudentVo> getAllProfessionAndStudents();
}
  • Mapper文件

在一对多映射中,“多”的一方使用 collection 标签表示,除此之外,还需要使用ofType指定集合中存放的实体类型,这里 ProfessionStudentVo 中的List存放的类型为 StudentCardVo,故 ofTypeStudentCardVo

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
<?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.five.wuhu.edu.mapper.ProfessionMapper">

<resultMap id="professionStudentVo" type="com.five.wuhu.edu.entity.vo.ProfessionStudentVo">
<id property="professionId" column="profession_id"/>
<id property="professionName" column="profession_name" />

<collection property="studentCardVos" ofType="com.five.wuhu.edu.entity.vo.StudentCardVo" >
<id property="studentId" column="student_id" />
<result property="name" column="name" />

<association property="card" javaType="com.five.wuhu.edu.entity.Card">
<id property="cardId" column="card_id" />
<result property="info" column="info" />
</association>
</collection>
</resultMap>
<select id="getAllProfessionAndStudents" resultMap="professionStudentVo">
SELECT
s.student_id,
s.name,
p.profession_id,
p.profession_name,
c.card_id,
c.info
from
`mybatis_exercise`.`edu_student` as s
inner join
`mybatis_exercise`.`edu_profession` as p
on
s.profession_id = p.profession_id
inner join
`mybatis_exercise`.`edu_card` as c
on
c.card_id = s.card_id
</select>
</mapper>
  • 测试

image-20210320154353217

  • 测试结果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
{
"success": true,
"code": 20000,
"message": "成功",
"data": {
"items": [
{
"professionId": "1",
"professionName": "信息与计算科学",
"studentCardVos": [
{
"studentId": "1",
"name": "A",
"card": {
"cardId": "1",
"info": "这是一号卡"
}
},
{
"studentId": "2",
"name": "B",
"card": {
"cardId": "2",
"info": "这是二号卡"
}
}
]
},
{
"professionId": "2",
"professionName": "数学与应用数学",
"studentCardVos": [
{
"studentId": "3",
"name": "C",
"card": {
"cardId": "3",
"info": "这是三号卡"
}
},
{
"studentId": "5",
"name": "E",
"card": {
"cardId": "5",
"info": "这是五号卡"
}
}
]
},
{
"professionId": "3",
"professionName": "应用统计学",
"studentCardVos": [
{
"studentId": "4",
"name": "D",
"card": {
"cardId": "4",
"info": "这是四号卡"
}
}
]
}
]
}
}

2.3、使用Mybatis进行多对多映射

1、查询所有学生和他选的所有课程信息

创建一个 vo 实体类

1
2
3
4
5
6
@Data
public class StudentCourseVo {
private String studentId;
private String name;
private List<Course> courses;
}
  • controller
1
2
3
4
5
6
7
8
9
10
11
12
13
14
@RestController
@Api(tags = "学生控制器")
@RequestMapping("/edu/student")
public class StudentController {
@Autowired
private StudentService studentService;

@GetMapping("getAllCourse")
@ApiOperation("查询所有学生和选择的课")
public Result getAllStudentAndCourse() {
List<StudentCourseVo> studentCourseVos = studentService.getAllStudentAndCourse();
return Result.ok().data("items",studentCourseVos);
}
}
  • Mapper接口
1
2
3
4
@Repository
public interface StudentMapper extends BaseMapper<Student> {
List<StudentCourseVo> getAllStudentAndCourse();
}
  • Mapper文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
<?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.five.wuhu.edu.mapper.StudentMapper">

<resultMap id="studentCourseVo" type="com.five.wuhu.edu.entity.vo.StudentCourseVo">
<id property="studentId" column="student_id" />
<result property="name" column="name" />
<collection property="courses" ofType="com.five.wuhu.edu.entity.Course" >
<id property="courseId" column="course_id" />
<result property="courseName" column="course_name" />
</collection>
</resultMap>

<select id="getAllStudentAndCourse" resultMap="studentCourseVo">
SELECT
s.student_id,
s.name,
c.course_id,
c.course_name
from
`mybatis_exercise`.edu_student as s
inner join
`mybatis_exercise`.edu_select_course as sc
on
s.student_id = sc.student_id
inner join
`mybatis_exercise`.edu_course as c
on
sc.course_id = c.course_id
</select>
</mapper>
  • 测试结果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
{
"success": true,
"code": 20000,
"message": "成功",
"data": {
"items": [
{
"studentId": "1",
"name": "A",
"courses": [
{
"courseId": "1",
"courseName": "运筹学"
},
{
"courseId": "2",
"courseName": "Java"
},
{
"courseId": "3",
"courseName": "数据库"
}
]
},
{
"studentId": "2",
"name": "B",
"courses": [
{
"courseId": "1",
"courseName": "运筹学"
},
{
"courseId": "2",
"courseName": "Java"
}
]
},
{
"studentId": "3",
"name": "C",
"courses": [
{
"courseId": "1",
"courseName": "运筹学"
},
{
"courseId": "2",
"courseName": "Java"
},
{
"courseId": "3",
"courseName": "数据库"
}
]
}
]
}
}

2、查询所有课程及选这门课程的所有学生信息

创建一个 vo 类

1
2
3
4
5
6
@Data
public class CourseStudentVo {
private String courseId;
private String courseName;
private List<Student> students;
}
  • controller
1
2
3
4
5
6
7
8
9
10
11
12
13
14
@RestController
@Api(tags = "课程控制器")
@RequestMapping("/edu/course")
public class CourseController {
@Autowired
private CourseService courseService;

@ApiOperation("获取全部课程及选择此课程的学生")
@GetMapping("getAllCoursesAndStudents")
public Result getAllCoursesAndStudents() {
List<CourseStudentVo> courseStudentVos = courseService.getAllCoursesAndStudents();
return Result.ok().data("items",courseStudentVos);
}
}
  • Mapper接口
1
2
3
4
@Repository
public interface CourseMapper extends BaseMapper<Course> {
List<CourseStudentVo> getAllCoursesAndStudents();
}
  • Mapper文件
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
<?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.five.wuhu.edu.mapper.CourseMapper">
<resultMap id="courseStudentVo" type="com.five.wuhu.edu.entity.vo.CourseStudentVo">
<id property="courseId" column="course_id" />
<result property="courseName" column="course_name" />
<collection property="students" ofType="com.five.wuhu.edu.entity.Student">
<id property="studentId" column="student_id" />
<result property="name" column="name" />
</collection>
</resultMap>
<select id="getAllCoursesAndStudents" resultMap="courseStudentVo">
SELECT
s.student_id,
s.name,
c.course_id,
c.course_name
from
`mybatis_exercise`.edu_student as s
inner join
`mybatis_exercise`.edu_select_course as sc
on
s.student_id = sc.student_id
inner join
`mybatis_exercise`.edu_course as c
on
sc.course_id = c.course_id
</select>
</mapper>
  • 测试结果
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
{
"success": true,
"code": 20000,
"message": "成功",
"data": {
"items": [
{
"courseId": "1",
"courseName": "运筹学",
"students": [
{
"studentId": "1",
"name": "A",
"cardId": null,
"professionId": null
},
{
"studentId": "2",
"name": "B",
"cardId": null,
"professionId": null
},
{
"studentId": "3",
"name": "C",
"cardId": null,
"professionId": null
}
]
},
{
"courseId": "2",
"courseName": "Java",
"students": [
{
"studentId": "1",
"name": "A",
"cardId": null,
"professionId": null
},
{
"studentId": "2",
"name": "B",
"cardId": null,
"professionId": null
},
{
"studentId": "3",
"name": "C",
"cardId": null,
"professionId": null
}
]
},
{
"courseId": "3",
"courseName": "数据库",
"students": [
{
"studentId": "1",
"name": "A",
"cardId": null,
"professionId": null
},
{
"studentId": "3",
"name": "C",
"cardId": null,
"professionId": null
}
]
}
]
}
}

3、综合测试

查询所有学生的详细信息,包括 id 卡信息、所在专业和选修的所有课程信息

  • 创建一个 vo 类,用于展示所有信息
1
2
3
4
5
6
7
8
9
@Data
public class StudentInfoVo implements Serializable {
private static final long serialVersionUID=1L;
private String studentId;
private String name;
private String cardInfo;
private String professionName;
private List<Course> courses;
}
  • controller
1
2
3
4
5
6
7
8
9
10
11
12
13
@RestController
@Api(tags = "学生控制器")
@RequestMapping("/edu/student")
public class StudentController {
@Autowired
private StudentService studentService;
@GetMapping("getInfo")
@ApiOperation("获取全部学生的详细信息")
public Result getInfo() {
List<StudentInfoVo> studentInfoVos = studentService.getInfo();
return Result.ok().data("items",studentInfoVos);
}
}
  • mapper接口
1
2
3
4
@Repository
public interface StudentMapper extends BaseMapper<Student> {
List<StudentInfoVo> getInfo();
}
  • mapper文件

对于多对一中的一,也是使用 association 标签进行关联。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
<?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.five.wuhu.edu.mapper.StudentMapper">
<resultMap id="studentInfoVo" type="com.five.wuhu.edu.entity.vo.StudentInfoVo">
<id property="studentId" column="student_id" />
<result property="name" column="name" />
<association property="card" javaType="com.five.wuhu.edu.entity.Card">
<id property="cardId" column="card_id" />
<result property="info" column="info" />
</association>
<association property="profession" javaType="com.five.wuhu.edu.entity.Profession">
<id property="professionId" column="profession_id" />
<result property="professionName" column="profession_name"/>
</association>
<collection property="courses" ofType="com.five.wuhu.edu.entity.Course">
<id property="courseId" column="course_id" />
<result property="courseName" column="course_name" />
</collection>
</resultMap>
<select id="getInfo" resultMap="studentInfoVo">
SELECT
s.student_id,
s.name,
p.profession_id,
p.profession_name,
card.card_id,
card.info,
c.course_id,
c.course_name
from
`mybatis_exercise`.edu_student as s
left join
`mybatis_exercise`.edu_card as card
on
s.card_id = card.card_id
left join
`mybatis_exercise`.edu_profession as p
on
p.profession_id = s.profession_id
left join
`mybatis_exercise`.edu_select_course as sc
on
sc.student_id = s.student_id
left join
`mybatis_exercise`.edu_course as c
on
sc.course_id = c.course_id
</select>
</mapper>
  • 测试结果

上面使用 left join 连接表,可以查询到所有学生。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
{
"success": true,
"code": 20000,
"message": "成功",
"data": {
"items": [
{
"studentId": "1",
"name": "A",
"card": {
"cardId": "1",
"info": "这是一号卡"
},
"profession": {
"professionId": "1",
"professionName": "信息与计算科学"
},
"courses": [
{
"courseId": "1",
"courseName": "运筹学"
},
{
"courseId": "2",
"courseName": "Java"
},
{
"courseId": "3",
"courseName": "数据库"
}
]
},
{
"studentId": "2",
"name": "B",
"card": {
"cardId": "2",
"info": "这是二号卡"
},
"profession": {
"professionId": "1",
"professionName": "信息与计算科学"
},
"courses": [
{
"courseId": "1",
"courseName": "运筹学"
},
{
"courseId": "2",
"courseName": "Java"
}
]
},
{
"studentId": "3",
"name": "C",
"card": {
"cardId": "3",
"info": "这是三号卡"
},
"profession": {
"professionId": "2",
"professionName": "数学与应用数学"
},
"courses": [
{
"courseId": "1",
"courseName": "运筹学"
},
{
"courseId": "2",
"courseName": "Java"
},
{
"courseId": "3",
"courseName": "数据库"
}
]
},
{
"studentId": "4",
"name": "D",
"card": {
"cardId": "4",
"info": "这是四号卡"
},
"profession": {
"professionId": "3",
"professionName": "应用统计学"
},
"courses": []
},
{
"studentId": "5",
"name": "E",
"card": {
"cardId": "5",
"info": "这是五号卡"
},
"profession": {
"professionId": "2",
"professionName": "数学与应用数学"
},
"courses": []
}
]
}
}

4、源码下载

密码:7hx7