MyBatis多表关联查询

本文将向大家介绍 MyBatis 中的多表关联查询

主要通过两个示例来说明在 MyBatis 中如何实现一对多，多对一和多对多查询

第一个示例：老师和学生关联

老师和学生是一对多的关系，一名老师管教多名学生

学生和老师是多对一的关系，多名学生被一名老师管教

那么如何查询所有学生以及管教他们的老师呢？

又如何查询所有老师和他所管教的学生呢？

第二个示例：账户和课程关联

在网上选课系统中，账户和课程是多对多关系，

一个账户可以选择多门课程，而一门课程也可以被多个账户选择

那么如何查询账户以及此账户所选择的课程呢？

又如何查询课程以及选择这门课程的账户呢？

下面我们就来一步一步解决这两个问题吧

一、搭建测试环境（一对多和多对一）

（一）创建测试数据库

CREATE TABLE `t_teacher` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

CREATE TABLE `t_student` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  `tid` int(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8

mybatis多表查询-学生数据表

（二）根据测试数据库创建实体类

学生类

package com.jarreet.test.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public class Student {
    private Integer id;
    private String name;
    private Integer tid;

    // 学生关联一个老师
    private Teacher teacher;
}

老师类

package com.jarreet.test.pojo;

import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;

import java.util.ArrayList;
import java.util.List;

@Data
@AllArgsConstructor
@NoArgsConstructor
@ToString
public class Teacher {
    private Integer id;
    private String name;

    // 老师关联一群学生
    private List<Student> studentList = new ArrayList<>();
}

（三）文件配置

在 application.yml 配置 mybatis

mybatis:
  type-aliases-package: com.jarreet.test.pojo
  mapper-locations: classpath:mapper/*.xml
  configuration:
    map-underscore-to-camel-case: true

在 TestApplication 启动类上加上包扫描注解
```
@MapperScan("com.jarreet.test.dao")
```

二、多对一查询

我们根据上面搭建的环境，给出一个查询需求：查询所有学生的信息及他们的老师

（一）数据持久化层

StudentDao.java

package com.jarreet.test.dao;

import com.jarreet.test.pojo.Student;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface StudentDao {

    public List<Student> getAllStudents();
}

StudentMapper.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.jarreet.test.dao.StudentDao">

    <!-- 查询文章管理列表多对一配置 -->
    <resultMap id="studentAndTeacher" type="com.jarreet.test.pojo.Student">
        <id property="id" column="sid"/>
        <result property="name" column="sname"/>
        <result property="tid" column="stid"/>
        <association property="teacher" javaType="com.jarreet.test.pojo.Teacher">
            <id property="id" column="tid"/>
            <result property="name" column="tname"/>
        </association>
    </resultMap>
    <!--
        查询学生列表
        注意：要给属性取别名，
        因为老师和学生都有id和name属性，
        若不取别名则会冲突覆盖
    -->
    <select id="getAllStudents" resultMap="studentAndTeacher">
        select
               s.id sid,
               s.name sname,
               s.tid stid,
               t.id tid,
               t.name tname
        from
             t_student s, t_teacher t
        where
              s.tid = t.id
    </select>
</mapper>

（二）业务逻辑层

StudentService.java

package com.jarreet.test.service;

import com.jarreet.test.pojo.Student;

import java.util.List;

public interface StudentService {

    List<Student> getAllStudents();
}

StudentServiceImpl.java

package com.jarreet.test.service.impl;

import com.jarreet.test.dao.StudentDao;
import com.jarreet.test.pojo.Student;
import com.jarreet.test.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import java.util.List;

@Service
public class StudentServiceImpl implements StudentService {

    @Autowired
    private StudentDao studentDao;

    @Override
    public List<Student> getAllStudents() {
        return studentDao.getAllStudents();
    }
}

（三）访问控制层

StudentController.java

package com.jarreet.test.controller;

import com.jarreet.test.pojo.Student;
import com.jarreet.test.service.StudentService;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import java.util.List;

@Controller
public class StudentController {

    @Autowired
    private StudentService studentService;

    @RequestMapping("/student")
    @ResponseBody
    public String studentList() {
        List<Student> allStudents = studentService.getAllStudents();
        return allStudents.toString();
    }
}

（四）结果展示

三、一对多查询

我们根据上面搭建的环境，给出一个查询需求：查询所有老师的信息及他们的学生

（一）数据持久化层

TeacherDao.java

package com.jarreet.test.dao;

import com.jarreet.test.pojo.Teacher;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface TeacherDao {

    public List<Teacher> getAllTeachers();
}

TeacherMapper.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.jarreet.test.dao.TeacherDao">

    <!-- 查询老师列表一对多配置 -->
    <resultMap id="teacherAndStudent" type="com.jarreet.test.pojo.Teacher">
        <id property="id" column="tid"/>
        <result property="name" column="tname"/>
        <collection property="studentList" ofType="com.jarreet.test.pojo.Student">
            <id property="id" column="sid"/>
            <result property="name" column="sname"/>
            <result property="tid" column="stid"/>
        </collection>
    </resultMap>
    <!--  查询老师列表  -->
    <select id="getAllTeachers" resultMap="teacherAndStudent">
        select
               t.id tid,
               t.name tname,
               s.id sid,
               s.name sname,
               s.tid stid
        from
             t_teacher t, t_student s
        where
              t.id = s.tid
    </select>
</mapper>

（二）业务逻辑层

TeacherService.java

package com.jarreet.test.service;import com.jarreet.test.pojo.Teacher;import java.util.List;public interface TeacherService {        List<Teacher> getAllTeachers();}

TeacherServiceImpl.java

package com.jarreet.test.service.impl;import com.jarreet.test.dao.TeacherDao;import com.jarreet.test.pojo.Teacher;import com.jarreet.test.service.TeacherService;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;import java.util.List;@Servicepublic class TeacherServiceImpl implements TeacherService {        @Autowired    private TeacherDao teacherDao;        @Override    public List<Teacher> getAllTeachers() {        return teacherDao.getAllTeachers();    }}

（三）访问控制层

TeacherController.java

package com.jarreet.test.controller;import com.jarreet.test.pojo.Teacher;import com.jarreet.test.service.TeacherService;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.ResponseBody;import java.util.List;@Controllerpublic class TeacherController {    @Autowired    private TeacherService teacherService;    @RequestMapping("/teacher")    @ResponseBody    public String teacherList() {        List<Teacher> allTeachers = teacherService.getAllTeachers();        return allTeachers.toString();    }}

（四）结果展示

四、多对多查询

多对多查询其实就是双向的一对多查询

这里我们给出一个多对多的情境：网上选课，一个账号可以选择多个课程，而一个课程也有可能被多个账号选择

那么我们现在需要一个账号表 t_account ，一个课程表 t_course；但是这两张表就够了吗？

当然不够，开头说了，多对多查询是双向的一对多，那么我们就需要一张中间表 account_course 来实现

（一）创建数据库表

CREATE TABLE `t_account`(`id` INT(11) PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR(11) DEFAULT NULL);CREATE TABLE `t_course`(`id` INT(11) PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR(11) DEFAULT NULL);CREATE TABLE `account_course`(`id` INT(11) PRIMARY KEY AUTO_INCREMENT,`aid` INT(11) DEFAULT NULL,`cid` INT(11) DEFAULT NULL);

（二）根据数据库表创建实体类

账户类

package com.jarreet.test.pojo;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import lombok.ToString;import java.util.List;@Data@NoArgsConstructor@AllArgsConstructor@ToStringpublic class Account {    private Integer id;    private String name;    private List<Course> courseList;}

课程类

package com.jarreet.test.pojo;import lombok.AllArgsConstructor;import lombok.Data;import lombok.NoArgsConstructor;import lombok.ToString;import java.util.List;@Data@NoArgsConstructor@AllArgsConstructor@ToStringpublic class Course {        private Integer id;    private String name;    private List<Account> accountList;}

（三）数据持久化层

AccountDao.java

package com.jarreet.test.dao;import com.jarreet.test.pojo.Account;import org.springframework.stereotype.Repository;@Repositorypublic interface AccountDao {    public Account findById(Integer id);}

AccountMapper.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.jarreet.test.dao.AccountDao">        <resultMap id="accountMap" type="com.jarreet.test.pojo.Account">        <id property="id" column="aid"/>        <result property="name" column="aname"/>        <collection property="courseList" ofType="com.jarreet.test.pojo.Course">            <id property="id" column="cid"/>            <result property="name" column="cname"/>        </collection>    </resultMap>        <select id="findById" resultMap="accountMap">        select               a.id aid,               a.name aname,               c.id cid,               c.name cname        from             t_account a, t_course c, account_course ac        where              a.id = #{id}          and a.id = ac.aid          and c.id = ac.cid    </select></mapper>

CourseDao.java

package com.jarreet.test.dao;import com.jarreet.test.pojo.Course;import org.springframework.stereotype.Repository;@Repositorypublic interface CouseDao {    public Course findById(Integer id);}

CourseMapper.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.jarreet.test.dao.CouseDao">    <resultMap id="courseMap" type="com.jarreet.test.pojo.Course">        <id property="id" column="cid"/>        <result property="name" column="cname"/>        <collection property="accountList" ofType="com.jarreet.test.pojo.Account">            <id property="id" column="aid"/>            <result property="name" column="aname"/>        </collection>    </resultMap>    <select id="findById" resultMap="courseMap">        select            c.id cid,            c.name cname,            a.id aid,            a.name aname        from            t_account a, t_course c, account_course ac        where            c.id = #{id}          and a.id = ac.aid          and c.id = ac.cid    </select></mapper>

（四）业务逻辑层

AccountService.java

package com.jarreet.test.service;import com.jarreet.test.pojo.Account;public interface AccountService {    Account findAccountById(Integer id);}

AccountServiceImpl.java

package com.jarreet.test.service.impl;import com.jarreet.test.dao.AccountDao;import com.jarreet.test.pojo.Account;import com.jarreet.test.service.AccountService;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;@Servicepublic class AccountServiceImpl implements AccountService {        @Autowired    private AccountDao accountDao;        @Override    public Account findAccountById(Integer id) {        return accountDao.findById(id);    }}

CourseService.java

package com.jarreet.test.service;import com.jarreet.test.pojo.Course;public interface CourseService {        Course findCourseById(Integer id);}

CourseServiceImpl.java

package com.jarreet.test.service.impl;import com.jarreet.test.dao.CourseDao;import com.jarreet.test.pojo.Course;import com.jarreet.test.service.CourseService;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Service;@Servicepublic class CourseServiceImpl implements CourseService {        @Autowired    private CourseDao courseDao;    @Override    public Course findCourseById(Integer id) {        return courseDao.findById(id);    }}

（五）访问控制层

AccountController.java

package com.jarreet.test.controller;import com.jarreet.test.pojo.Account;import com.jarreet.test.service.AccountService;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.PathVariable;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.ResponseBody;@Controllerpublic class AccountController {    @Autowired    private AccountService accountService;    @RequestMapping("/account/{id}")    @ResponseBody    public String findAccountById(@PathVariable Integer id) {        Account account = accountService.findAccountById(id);        return account.toString();    }}

CourseController.java

package com.jarreet.test.controller;import com.jarreet.test.pojo.Course;import com.jarreet.test.service.CourseService;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.stereotype.Controller;import org.springframework.web.bind.annotation.PathVariable;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.ResponseBody;@Controllerpublic class CourseController {    @Autowired    private CourseService courseService;    @RequestMapping("/course/{id}")    @ResponseBody    public String findCourseById(@PathVariable Integer id) {        Course course = courseService.findCourseById(id);        return course.toString();    }}

（六）结果展示

至此，关于 Mybatis 的多表查询就讲解完毕了，下面我们简单做个总结。

Mybatis的多表查询无非就是做好实体类属性和mapper.xml文件的映射，其中：

association 是将结果集封装成⼀个实体类的对象（⼀个⽬标对象），并通过 JavaType 设置数类型；

collection 是将结果集封装成⼀个集合对象（多个⽬标对象），并通过 ofType 设置数据类型；

当多对一时，使用 association

当一对多时，使用 collection；

而当多对多时，无非就是进行一个双向的一对多绑定

特别要注意的几个点：

搭建环境时，要在配置文件中配置好 mybatis 信息，否则有可能报错

需要开启包扫描，要么在启动类上加上 @MapperScan 注解，要么在持久化接口上加上 @Mapper 注解

当需要级联的对象的属性名和自身的属性名相同时，需要起别名，防止冲突覆盖

原文链接：https://blog.csdn.net/IceTiger_/article/details/119566992

一、搭建测试环境（一对多和多对一）

（一）创建测试数据库

（二）根据测试数据库创建实体类

（三）文件配置

二、多对一查询

（一）数据持久化层

（二）业务逻辑层

（三）访问控制层

（四）结果展示

三、一对多查询

（一）数据持久化层

（二）业务逻辑层

（三）访问控制层

（四）结果展示

四、多对多查询

（一）创建数据库表

（二）根据数据库表创建实体类

（三）数据持久化层

（四）业务逻辑层

（五）访问控制层

（六）结果展示

你可能也喜欢