MyBatis 多表关联查询

刺骨的言语ヽ痛彻心扉 2021-10-01 00:52 534阅读 0赞

# MyBatis 多表关联查询 #

环境信息：  
OS：Mac OS  
Jdk：JavaSE 8  
Ide：Idea Ultimate  
MySQL：8.0.16  
MyBatis：3.5.2  
Spring Boot：2.1.7.RELEASE

## 1.数据库表信息 ##

### 1.1 数据库表关系图 ###

学生表：`t_student`  
成绩表：`t_score`  
关系图：

![在这里插入图片描述][20190809171236538.png]

### 1.2 实体类 ###

`Student`对应`t_student`表

@Data
    @AllArgsConstructor
    @NoArgsConstructor
    @Builder
    public class Student { 
        private Integer id;
        private String name;
        private Integer age;
        private String sex;
        private String hobbies;
        private String address;
    }

`Score`对应`t_score`表

@Data
    @Builder
    @AllArgsConstructor
    @NoArgsConstructor
    public class Score { 
    
        private Integer id;
        //学生编号
        private Integer studentId;
    
        private String studentName;
        //科目
        private String subject;
        //科目成绩
        private Integer subjectScore;
    }

> **注**：@Data，@Builder，@AllArgsConstructor，@NoArgsConstructor为**lombok**的注解

### 1.3 表数据 ###

`t_student`：  
![在这里插入图片描述][20190812135441440.png]  
`t_score`：  
![在这里插入图片描述][20190812135356960.png]

## 2.多表关联查询种类 ##

`Student`与`Score`是一对多的关系，他们连个关联查询有两种情况：

*  2.1 一个学生的所有成绩`StudentScores`

@Data
    @AllArgsConstructor
    @NoArgsConstructor
    @Builder
    public class StudentScores { 
        private Integer id;
        private String name;
        private Integer age;
        private String sex;
        private List<Score> scoreList; //t_score查询出的数据映射成一个List<Score>
    }

*  2.2 一个学生的某一门成绩`StudentScore`

@Data
    @AllArgsConstructor
    @NoArgsConstructor
    @Builder
    public class StudentScore { 
        private Integer id;
        private String name;
        private Integer age;
        private String sex;
        private Score score; //t_score查询出的数据映射成一个Score
    }

## 3.MyBatis多表关联查询实现 ##

在标签中使用和标签实现`#2`中的两种情况：

![在这里插入图片描述][2019081213480544.png]

*  **association** \- 一种复杂的结果; 许多结果将汇总到这个类型中, 可关联是resultMap自身，或者可以引用一个
 *  **collection** \- 复杂类型的集合, 可关联是resultMap自身，或者可以引用一个

意思就是：映射成的字段为一个复杂对象时用`association`，映射成的字段为一个集合,时用`collection`，而且他们都可以引用已经存在的resultMap;

> 详见 [http://www.mybatis.org/mybatis-3/sqlmap-xml.html\#Result\_Maps][http_www.mybatis.org_mybatis-3_sqlmap-xml.html_Result_Maps]

### 3.1 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.wxx.sbmm.mapper.StudentMapper">
    
        
        <resultMap id="studentScoreListResultMap" type="com.wxx.sbmm.domain.StudentScores">
            <id column="aid" jdbcType="INTEGER" property="id"/>
            <result column="name" jdbcType="VARCHAR" javaType="String" property="name"/>
            <result column="age" javaType="Integer" property="age"/>
            <result column="sex" javaType="String" property="sex"/>
            <collection property="scoreList" javaType="List" ofType="Score">
                <id column="bid" jdbcType="INTEGER" property="id"/>
                <result property="studentId" column="student_id"/>
                <result property="studentName" column="student_name"/>
                <result property="subject" column="subject"/>
                <result property="subjectScore" column="subject_score"/>
            </collection>
        </resultMap>
    
        
        <resultMap id="studentScoreResultMap" type="com.wxx.sbmm.domain.StudentScore">
            <id column="aid" jdbcType="BIGINT" javaType="Integer" property="id"/>
            <result property="name" jdbcType="VARCHAR" javaType="String" column="name"/>
            <result property="age" column="age" jdbcType="VARCHAR" javaType="Integer"/>
            <result property="sex" column="sex" jdbcType="VARCHAR" javaType="String"/>
            <association property="score" javaType="com.wxx.sbmm.domain.Score">
                <id property="id" column="bid"/>
                <result property="studentId" column="student_id"/>
                <result property="studentName" column="student_name"/>
                <result property="subject" column="subject"/>
                <result property="subjectScore" column="subject_score"/>
            </association>
        </resultMap>
    
        
        <select id="findStudentScoreList" parameterType="Integer" resultMap="studentScoreListResultMap">
            select
            a.id aid,
            a.name name,
            a.age age,
            a.sex sex,
            b.id bid,
            b.student_id student_id,
            b.student_name student_name,
            b.subject subject,
            b.subject_score subject_score
            from t_student a
            left join t_score b
            on a.id=b.student_id
        </select>
        
        <select id="findStudentScore" resultMap="studentScoreResultMap">
            select
            a.id aid,
            a.name name,
            a.age age,
            a.sex sex,
            b.id bid,
            b.student_id student_id,
            b.student_name student_name,
            b.subject subject,
            b.subject_score subject_score
            from t_student a
            left join t_score b
            on a.id=b.student_id
            where b.subject = 'English'
            and a.name = b.student_name
        </select>
    </mapper>

### 3.2 查询结果 ###

*  1.studentScoreList  
    ![在这里插入图片描述][20190812140848285.png]
 *  2.studentScoreList

![在这里插入图片描述][20190812140914269.png]

## 4.附录：数据库初始化信息 ##

数据库为：`studentService`  
建数据库语句：***CREATE SCHEMA \`studentService\` DEFAULT CHARACTER SET utf8 ;***  
包含两个表，针对它们进行关联查询：  
学生表：`t_student`  
成绩表：`t_score`

### 4.1 建表语句 ###

初始化库表SQL：

CREATE DATABASE IF NOT EXISTS `studentService`;
    
    CREATE TABLE IF NOT EXISTS `t_student` (
    `id` INT NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `name` VARCHAR(255)  DEFAULT "" COMMENT '姓名',
    `age` INT DEFAULT 10 COMMENT '年龄',
    `sex` VARCHAR(255)DEFAULT "Male" COMMENT '性别',
    `hobbies` VARCHAR(255) DEFAULT "" COMMENT '爱好',
    `address` VARCHAR(255) DEFAULT "" COMMENT '住址',
    PRIMARY KEY(`id`),
    UNIQUE INDEX  `idx_student_name_address` (`name`,`address`) USING BTREE
    )ENGINE = INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT = 1  COMMENT '学生表';
    
    CREATE TABLE IF NOT EXISTS `t_score` (
      `id` INT NOT NULL AUTO_INCREMENT COMMENT 'ID',
    `student_id` INT DEFAULT 0 COMMENT '学生ID',
    `student_name` VARCHAR(255) DEFAULT "" COMMENT '学生姓名',
    `subject` VARCHAR(255) DEFAULT "" COMMENT '学科',
    `subject_score` INT COMMENT '学科成绩',
    PRIMARY KEY(`id`),
    UNIQUE INDEX  `idx_score_studentname_subject` (`student_name`,`subject`) USING BTREE ,
    FOREIGN KEY (student_id) REFERENCES t_student (id)
    )ENGINE = INNODB DEFAULT CHARSET=utf8 AUTO_INCREMENT = 1  COMMENT '学生成绩表';

### 4.2 插入数据 ###

初始化数据SQL：

INSERT INTO t_student (name,age, sex, hobbies,  address)
      SELECT "Even",9,"Male","ShuXue,English","XiAn" FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM t_student WHERE name = "Even");
    INSERT INTO t_student (name,age, sex, hobbies,  address)
      SELECT "Weison",11,"Male","XuWen,WuLi","HeNan" FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM t_student WHERE name = "Weison");
    INSERT INTO t_student (name,age, sex, hobbies,  address)
      SELECT "Angule",13,"Female","XuWen,English","Chengdu" FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM t_student WHERE name = "Angule");
    
    
    INSERT INTO t_score (student_id,student_name, subject,  subject_score)
      SELECT 1,"Even","YuWen",90 FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM t_score WHERE student_name = "Even" and subject ="YuWen");
    
    INSERT INTO t_score (student_id,student_name, subject,  subject_score)
      SELECT 1,"Even","ShuXue",89 FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM t_score WHERE student_name = "Even" and subject ="ShuXue");
    
    INSERT INTO t_score (student_id,student_name, subject,  subject_score)
      SELECT 1,"Even","English",67 FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM t_score WHERE student_name = "Even" and subject ="English");
    
    INSERT INTO t_score (student_id,student_name, subject,  subject_score)
      SELECT 2,"Weison","YuWen",69 FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM t_score WHERE student_name = "Weison" and subject ="YuWen");
    INSERT INTO t_score (student_id,student_name, subject,  subject_score)
      SELECT 2,"Weison","ShuXue",94 FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM t_score WHERE student_name = "Weison" and subject ="ShuXue");
    INSERT INTO t_score (student_id,student_name, subject,  subject_score)
      SELECT 2,"Weison","English",82 FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM t_score WHERE student_name = "Weison" and subject ="English");
    
    
    INSERT INTO t_score (student_id,student_name, subject,  subject_score)
      SELECT 3,"Angule","YuWen",58 FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM t_score WHERE student_name = "Angule" and subject ="YuWen");
    INSERT INTO t_score (student_id,student_name, subject,  subject_score)
      SELECT 3,"Angule","ShuXue",73 FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM t_score WHERE student_name = "Angule" and subject ="ShuXue");
    INSERT INTO t_score (student_id,student_name, subject,  subject_score)
      SELECT 3,"Angule","English",91 FROM DUAL WHERE NOT EXISTS(SELECT 1 FROM t_score WHERE student_name = "Angule" and subject ="English");
    
    commit;

代码 --> [https://github.com/WeisonWei/springboot-aggregation/tree/master/springboot-mvc-mybatis][https_github.com_WeisonWei_springboot-aggregation_tree_master_springboot-mvc-mybatis]

[20190809171236538.png]: /images/20210911/c1fc48a68a2c43aabe2ca9f052fd3876.png
[20190812135441440.png]: /images/20210911/545fbc0c0fcf4e30a36565c71753c77b.png
[20190812135356960.png]: /images/20210911/a5caad4e9d5e4758a17b4290651a82de.png
[2019081213480544.png]: /images/20210911/7cd92dc5efe847c885692c1289d0464f.png
[http_www.mybatis.org_mybatis-3_sqlmap-xml.html_Result_Maps]: http://www.mybatis.org/mybatis-3/sqlmap-xml.html#Result_Maps
[20190812140848285.png]: /images/20210911/c8612423db4448079dda021edacf3e5f.png
[20190812140914269.png]: /images/20210911/543259ff991b43a8b747431d445e090a.png
[https_github.com_WeisonWei_springboot-aggregation_tree_master_springboot-mvc-mybatis]: https://github.com/WeisonWei/springboot-aggregation/tree/master/springboot-mvc-mybatis