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_subject` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `name` varchar(20)  NOT NULL DEFAULT '' COMMENT '课程名称',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB  COMMENT='课程表';
-- 测试数据 
insert into `micro_subject`(`name`) values ('语文'),('数学'),('英语');

-- 学生和课程关联的中间表
CREATE TABLE `micro_student_subject` (
  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
  `student_id` int unsigned NOT NULL DEFAULT '0' COMMENT '学生id',
  `subject_id` int unsigned NOT NULL DEFAULT '0' COMMENT '课程id',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=InnoDB COMMENT='学生课程中间表';
-- 测试数据
insert into `micro_student_subject`(`student_id`,`subject_id`) values (1,1),(1,2),(1,3),(2,1),(2,2),(3,1),(3,3);

1.2 建实体类

• 新建学生实体类: StudentModel.java

/**
 * @author : Mr.Liuqh
 * @date : 2020-09-27 12:05:00
 */
package com.hui.javalearn.model;
import java.io.Serializable;
import java.util.List;
import lombok.Data;

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

    /**
     *
     * 备注:名称
     *
     * 表字段: micro_student.name
     */
    private String name;
    
    @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("]");
        return sb.toString();
    }
}

• 新建课程实体类: SubjectModel.java

/**
 * @author : Mr.Liuqh
 * @date : 2020-09-27 17:30:49
 */
package com.hui.javalearn.model;
import java.io.Serializable;
import lombok.Data;

/**
 *
 * 课程表
 *
 * 表名: micro_subject
 */
@Data
public class SubjectModel implements Serializable {
    /**
     *
     * 备注:id
     *
     * 表字段: micro_subject.id
     */
    private Integer id;

    /**
     *
     * 备注:课程名称
     *
     * 表字段: micro_subject.name
     */
    private String name;
    
    @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("]");
        return sb.toString();
    }
}

• 新建课程表实体类: SubjectModel.java

/**
 * @author : Mr.Liuqh
 * @date : 2020-09-27 17:33:09
 */
package com.hui.javalearn.model;
import java.io.Serializable;
import lombok.Data;

/**
 *
 * 学生课程中间表
 *
 * 表名: micro_student_subject
 */
@Data
public class StudentSubjectModel implements Serializable {
    /**
     *
     * 备注:id
     *
     * 表字段: micro_student_subject.id
     */
    private Integer id;

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

    /**
     *
     * 备注:课程id
     *
     * 表字段: micro_student_subject.subject_id
     */
    private Integer subjectId;
    
    @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(", subjectId=").append(subjectId);
        sb.append("]");
        return sb.toString();
    }
}

2.方法一: 嵌套查询

2.1 修改实体类

• 修改学生实体类: StudentModel.java, 新增subjectModelList属性。

/**
 * 一个学生会有多个科目
 */
private List<SubjectModel> subjectModelList;

• 修改课程表实体类: SubjectModel.java, 新增studentModelList属性。

/**
 * 一个课程下对应多个学生
 */
private List<StudentModel> studentModelList;

2.2 创建Dao和Mapper

• 新建学生Dao类: StudentDao.java

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

• 新建学生Dao类 StudentDao.java对应的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="selectStudentSubjectListById" resultMap="linkSubjectResult" >
        select * from micro_student where id = #{id,jdbcType=INTEGER}
    </select>
    <resultMap id="linkSubjectResult" type="com.hui.javalearn.model.StudentModel">
        <id property="id" column="id"/>
        <result property="name" column="name"/>
        <collection property="subjectModelList" column="id" select="com.hui.javalearn.dao.SubjectDao.selectSubjectListByStudentId"/>
    </resultMap>
</mapper>

• 新建课程表Dao类: SubjectDao.java

/**
 * @author : Mr.Liuqh
 * @date : 2020-09-27 17:30:49
 */
package com.hui.javalearn.dao;
import com.hui.javalearn.model.SubjectModel;
public interface SubjectDao {
    SubjectModel selectSubjectListByStudentId(Integer id);
}

• 新建课程表Dao类SubjectDao.java对应的Mapper文件（SubjectDao,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.SubjectDao">
  <select id="selectSubjectListByStudentId" parameterType="java.lang.Integer" resultType="com.hui.javalearn.model.SubjectModel">
    select  id, `name` from micro_subject
    where id in
    (select subject_id from micro_student_subject where student_id = #{id,jdbcType=INTEGER})
  </select>
</mapper>

2.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.selectStudentSubjectListById(1);
        String s = JSON.toJSONString(studentModel);
        System.out.println(s);
    }
}

输出:

{
    "id":1,
    "name":"张三",
    "subjectModelList":[
        {
            "id":1,
            "name":"语文"
        },
        {
            "id":2,
            "name":"数学"
        },
        {
            "id":3,
            "name":"英语"
        }
    ]
}

执行SQL:

-- 查询学生表
select *
 FROM micro_student
 WHERE id = 1;
-- 根据学生id查询关联课程表
select id, `name`
 FROM micro_subject
 WHERE id in (select subject_id
 FROM micro_student_subject
 WHERE student_id = 1);

3. 方式二: 嵌套结果

3.1 修改Mapper

修改学生表对应的Mapper文件

<?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="selectStudentSubjectListById" resultMap="linkSubjectResult" >
        select A.id,A.name as studentName,C.name as subjectName,C.id as subjectId
        from micro_student as A, micro_student_subject as B, micro_subject as C
        where A.id = B.student_id AND B.subject_id=C.id
        AND  A.id = #{id,jdbcType=INTEGER}
    </select>
    <resultMap id="linkSubjectResult" type="com.hui.javalearn.model.StudentModel">
        <id property="id" column="id"/>
        <result property="name" column="studentName"/>
        <collection property="subjectModelList" column="id" ofType="com.hui.javalearn.model.SubjectModel">
            <id property="id" column="subjectId"/>
            <result property="name" column="subjectName"/>
        </collection>
    </resultMap>
</mapper>

3.2 测试

测试代码同[2.3 测试代码](#2.3 测试)

输出:

{
    "id":1,
    "name":"张三",
    "subjectModelList":[
        {
            "id":1,
            "name":"语文"
        },
        {
            "id":2,
            "name":"数学"
        },
        {
            "id":3,
            "name":"英语"
        }
    ]
}

执行SQL:

select A.id,A.name as studentName,C.name as subjectName,C.id as subjectId
 FROM micro_student as A, micro_student_subject as B, micro_subject as C
 WHERE A.id = B.student_id AND B.subject_id=C.id AND A.id = 1;