MyBatis多表关联查询

2023-12-16 21:44:40

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

（一）创建测试数据库

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();
    }
}

（四）结果展示

MyBatis多表关联查询

三、一对多查询

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

（一）数据持久化层

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;
?
@Service
public 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;
?
@Controller
public class TeacherController {
?
    @Autowired
    private TeacherService teacherService;
?
    @RequestMapping("/teacher")
    @ResponseBody
    public String teacherList() {
        List<Teacher> allTeachers = teacherService.getAllTeachers();
        return allTeachers.toString();
    }
}

（四）结果展示

MyBatis多表关联查询

四、多对多查询

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

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

那么我们现在需要一个账号表 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
);

MyBatis多表关联查询

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

账户类

package com.jarreet.test.pojo;
?
import lombok.AllArgsConstructor;
import lombok.Data;
import lombok.NoArgsConstructor;
import lombok.ToString;
?
import java.util.List;
?
@Data
@NoArgsConstructor
@AllArgsConstructor
@ToString
public 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
@ToString
public 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;
?
@Repository
public 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;
?
@Repository
public 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;
?
@Service
public 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;
?
@Service
public 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;
?
@Controller
public 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;
?
@Controller
public 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 的多表查询就讲解完毕了，下面我们简单做个总结。

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

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

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

当多对一时，使用 association

当一对多时，使用 collection；

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

特别要注意的几个点：

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

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

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

MyBatis多表关联查询

码农公寓

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

（一）创建测试数据库

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

（三）文件配置

二、多对一查询

（一）数据持久化层

（二）业务逻辑层

（三）访问控制层

（四）结果展示

三、一对多查询

（一）数据持久化层

（二）业务逻辑层

（三）访问控制层

（四）结果展示

四、多对多查询

（一）创建数据库表

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

（三）数据持久化层

（四）业务逻辑层

（五）访问控制层

（六）结果展示

相关文章