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，故 ofType 为 StudentCardVo

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

资源下载