SpringBoot学习(十三):Mybatis连表(一对多)使用实践

1. 前置准备

1.1 创建表

-- 学生表
CREATE TABLE `micro_student` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT '用户id',
  `name` varchar(10) COLLATE utf8mb4_bin NOT NULL DEFAULT '' COMMENT '名称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin COMMENT='学生表';
-- 测试数据
INSERT INTO `micro_serve`.`micro_student`(`id`, `name`) VALUES (1, '张三');
INSERT INTO `micro_serve`.`micro_student`(`id`, `name`) VALUES (2, '李四');
INSERT INTO `micro_serve`.`micro_student`(`id`, `name`) VALUES (3, '赵五');

-- 学生成绩表
CREATE TABLE `micro_student_score` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `student_id` int unsigned NOT NULL DEFAULT '0' COMMENT '学生id',
  `subject` varchar(20)  NOT NULL DEFAULT '' COMMENT '科目名称',
  `score` tinyint(4) unsigned COMMENT '分数',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB COMMENT='学生成绩表';
-- 测试数据 

INSERT into `micro_student_score`(`student_id`,`subject`,`score`) VALUES (1,'语文',80),(1,'数学',75),(1,'英语',55);
INSERT into `micro_student_score`(`student_id`,`subject`,`score`) VALUES (2,'语文',60),(2,'数学',65),(2,'英语',68);
INSERT into `micro_student_score`(`student_id`,`subject`,`score`) VALUES (3,'语文',90),(2,'数学',88),(2,'英语',85);

1.2 创建实体类

新建:StudentModel.java

/**
 * @author : Mr.Liuqh
 * @date : 2020-09-27 12:05:00
 */
package com.hui.javalearn.model;

import java.io.Serializable;

import lombok.Data;

/**
 *
 * 学生表
 *
 * 表名: micro_student
 */
@Data
public class StudentModel implements Serializable {
    /**
     *
     * 备注:用户id
     *
     * 表字段: micro_student.id
     */
    private Integer id;

    /**
     *
     * 备注:名称
     *
     * 表字段: micro_student.name
     */
    private String name;
    
    /**
     * @注意！！！！！！！  
     * 学生成绩信息
     */
    private List<StudentScoreModel> studentScoreModelList;
    
    private static final long serialVersionUID = 1L;

    public StudentModel(Integer id, String name) {
        this.id = id;
        this.name = name;
    }

    public StudentModel() {
        super();
    }

    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append(getClass().getSimpleName());
        sb.append(" [");
        sb.append("Hash = ").append(hashCode());
        sb.append(", id=").append(id);
        sb.append(", name=").append(name);
        sb.append(", serialVersionUID=").append(serialVersionUID);
        sb.append("]");
        return sb.toString();
    }
}

新建:StudentScoreModel.java

/**
 * @author : Mr.Liuqh
 * @date : 2020-09-27 16:02:45
 */
package com.hui.javalearn.model;

import java.io.Serializable;

import lombok.Data;

/**
 *
 * 学生成绩表
 *
 * 表名: micro_student_score
 */
@Data
public class StudentScoreModel implements Serializable {
    /**
     *
     * 备注:id
     *
     * 表字段: micro_student_score.id
     */
    private Integer id;

    /**
     *
     * 备注:学生id
     *
     * 表字段: micro_student_score.student_id
     */
    private Integer studentId;

    /**
     *
     * 备注:科目名称
     *
     * 表字段: micro_student_score.subject
     */
    private String subject;

    /**
     *
     * 备注:分数
     *
     * 表字段: micro_student_score.score
     */
    private Byte score;

    private static final long serialVersionUID = 1L;

    public StudentScoreModel(Integer id, Integer studentId, String subject, Byte score) {
        this.id = id;
        this.studentId = studentId;
        this.subject = subject;
        this.score = score;
    }

    public StudentScoreModel() {
        super();
    }

    @Override
    public String toString() {
        StringBuilder sb = new StringBuilder();
        sb.append(getClass().getSimpleName());
        sb.append(" [");
        sb.append("Hash = ").append(hashCode());
        sb.append(", id=").append(id);
        sb.append(", studentId=").append(studentId);
        sb.append(", subject=").append(subject);
        sb.append(", score=").append(score);
        sb.append(", serialVersionUID=").append(serialVersionUID);
        sb.append("]");
        return sb.toString();
    }
}

2. 实现方式

Mybatis通过使用<resultMap>元素中的子元素<collection>来处理一对多的关联关系。<collection>子元素的属性大部分与<association>元素相同，但其还包含一个特殊属性—ofType。ofType属性与javaType属性对应，用于指定实体对象中集合类属性所包含的元素类型。

3. 方式一: 嵌套查询

3.1 创建Dao

新建StudentDao.java

/**
 * @author : Mr.Liuqh
 * @date : 2020-09-27 12:05:01
 */
package com.hui.javalearn.dao;
import com.hui.javalearn.model.StudentModel;
public interface StudentDao {
    StudentModel selectStudentById(Integer id);
}

新建StudentScoreDao.java

/**
 * @author : Mr.Liuqh
 * @date : 2020-09-27 16:02:45
 */
package com.hui.javalearn.dao;
import com.hui.javalearn.model.StudentScoreModel;
public interface StudentScoreDao {
    StudentScoreModel selectScoreByStudentId(Integer studentId);
}

3.2 创建Mapper

新建StudentDao.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.hui.javalearn.dao.StudentDao">
  <!-- 一对多:嵌套查询 -->
  <select id="selectStudentById" parameterType="java.lang.Integer" resultMap="linkResult">
    select id,name
    from micro_student
    where id = #{id,jdbcType=INTEGER}
  </select>
  <resultMap id="linkResult" type="com.hui.javalearn.model.StudentModel">
    <id property="id" column="id"/>
    <!-- 使用 select 属性引入另一条SQL语句 -->
    <collection property="studentScoreModelList" column="id"
                select="com.hui.javalearn.dao.StudentScoreDao.selectScoreByStudentId"/>
  </resultMap>
</mapper>

新建StudentScoreDao.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.hui.javalearn.dao.StudentScoreDao">
  <select id="selectScoreByStudentId" parameterType="java.lang.Integer"
          resultType="com.hui.javalearn.model.StudentScoreModel" >
    select  id, student_id, subject, score
    from micro_student_score
    where student_id = #{studentId,jdbcType=INTEGER}
  </select>
</mapper>

3.3 测试

package com.hui.javalearn;

import com.alibaba.fastjson.JSON;
import com.hui.javalearn.dao.StudentDao;
import com.hui.javalearn.model.StudentModel;
import org.junit.jupiter.api.Test;
import org.springframework.boot.test.context.SpringBootTest;

import javax.annotation.Resource;

@SpringBootTest
public class TestMybatisLinkSearch {
    @Resource
    private StudentDao studentDao;
    @Test
    void testOneLinkOneSearch(){
        StudentModel studentModel = studentDao.selectStudentById(1);
        String s = JSON.toJSONString(studentModel);
        System.out.println(s);
    }
}

输出:

{
    "id":1,
    "name":"张三",
    "studentScoreModelList":[
        {
            "id":1,
            "score":80,
            "subject":"语文"
        },
        {
            "id":2,
            "score":75,
            "subject":"数学"
        },
        {
            "id":3,
            "score":55,
            "subject":"英语"
        }
    ]
}

执行两次SQL:

-- 先查的学生表
select id,name
 FROM micro_student
 WHERE id = 1;
-- 再查的学生成绩表
select id, student_id, subject, score
 FROM micro_student_score
 WHERE student_id = 1;

4. 方式二: 嵌套结果

4.1 修改Mapper文件

修改 3.2 创建Mapper的文件StudentDao.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.hui.javalearn.dao.StudentDao">
<!-- 一对多:嵌套结果 -->
  <select id="selectStudentById" parameterType="java.lang.Integer" resultMap="linkResult">
    select A.id,A.`name`,B.subject,B.score
    from micro_student AS A, micro_student_score as B
    where A.id = B.student_id AND A.id = #{id,jdbcType=INTEGER}
  </select>
  <resultMap id="linkResult" type="com.hui.javalearn.model.StudentModel">
    <id property="id" column="id"/>
    <result property="name" column="name"/>
    <collection property="studentScoreModelList" column="id" ofType="com.hui.javalearn.model.StudentScoreModel">
      <result property="subject" column="subject"/>
      <result property="score" column="score"/>
    </collection>
  </resultMap>
</mapper>

4.2 测试

测试代码同 3.3 测试

输出:

{
    "id":1,
    "name":"张三",
    "studentScoreModelList":[
        {
            "score":80,
            "subject":"语文"
        },
        {
            "score":75,
            "subject":"数学"
        },
        {
            "score":55,
            "subject":"英语"
        }
    ]
}