Spring boot2基于Mybatis实现多表关联查询的方法

2024-11-24 技术教程

这篇文章主要讲解了Spring boot2基于Mybatis实现多表关联查询的方法，内容清晰明了，对此有兴趣的小伙伴可以学习一下，相信大家阅读完之后会有帮助。

模拟业务关系：

一个用户user有对应的一个公司company，每个用户有多个账户account。

spring boot 2的环境搭建见上文：spring boot 2整合mybatis

一、mysql创表和模拟数据sql

CREATE TABLE IF NOT EXISTS `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(50) NOT NULL, `company_id` int(11) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE IF NOT EXISTS `company` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(200) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;CREATE TABLE IF NOT EXISTS `account` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(200) NOT NULL, `user_id` int(11) NOT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `user`VALUES (1, 'aa', 1), (2, 'bb', 2);INSERT INTO `company`VALUES (1, 'xx公司'), (2, 'yy公司');INSERT INTO `account`VALUES (1, '中行', 1), (2, '工行', 1), (3, '中行', 2);

二、创建实体

public class User { private Integer id; private String name; private Company company; private List<Account> accounts; //getter/setter 这里省略...}public class Company { private Integer id; private String companyName; //getter/setter 这里省略...}public class Account { private Integer id; private String accountName; //getter/setter 这里省略...}

三、开发Mapper

方法一：使用注解

1、AccountMapper.java

package com.example.demo.mapper;import java.util.List;import org.apache.ibatis.annotations.Result;import org.apache.ibatis.annotations.Results;import org.apache.ibatis.annotations.Select;import com.example.demo.entity.Account;public interface AccountMapper { /* * 根据用户id查询账户信息 */ @Select("SELECT * FROM `account` WHERE user_id = #{userId}") @Results({ @Result(property = "accountName", column = "name") }) List<Account> getAccountByUserId(Long userId);}

2、CompanyMapper.java

package com.example.demo.mapper;import org.apache.ibatis.annotations.Result;import org.apache.ibatis.annotations.Results;import org.apache.ibatis.annotations.Select;import com.example.demo.entity.Company;public interface CompanyMapper { /* * 根据公司id查询公司信息 */ @Select("SELECT * FROM company WHERE id = #{id}") @Results({ @Result(property = "companyName", column = "name") }) Company getCompanyById(Long id);}

3、UserMapper.java

package com.example.demo.mapper;import org.apache.ibatis.annotations.Result;import org.apache.ibatis.annotations.Results;import org.apache.ibatis.annotations.Select;import org.apache.ibatis.annotations.One;import org.apache.ibatis.annotations.Many;import com.example.demo.entity.User;public interface UserMapper { /* * 一对一查询 * property：查询结果赋值给此实体属性 * column：对应数据库的表字段，做为下面@One(select方法的查询参数 * one：一对一的查询 * @One(select = 方法全路径) ：调用的方法 */ @Select("SELECT * FROM user WHERE id = #{id}") @Results({ @Result(property = "company", column = "company_id", one = @One(select = "com.example.demo.mapper.CompanyMapper.getCompanyById")) }) User getUserWithCompany(Long id); /* * 一对多查询 * property：查询结果赋值给此实体属性 * column：对应数据库的表字段，可做为下面@One(select方法)的查询参数 * many：一对多的查询 * @Many(select = 方法全路径) ：调用的方法 */ @Select("SELECT * FROM user WHERE id = #{id}") @Results({ @Result(property = "id", column = "id"),//加此行，否则id值为空 @Result(property = "accounts", column = "id", many = @Many(select = "com.example.demo.mapper.AccountMapper.getAccountByUserId")) }) User getUserWithAccount(Long id); /* * 同时用一对一、一对多查询 */ @Select("SELECT * FROM user") @Results({ @Result(property = "id", column = "id"), @Result(property = "company", column = "company_id", one = @One(select = "com.example.demo.mapper.CompanyMapper.getCompanyById")), @Result(property = "accounts", column = "id", many = @Many(select = "com.example.demo.mapper.AccountMapper.getAccountByUserId")) }) List<User> getAll(); }

方法二：使用XML

参考上文spring boot 2整合mybatis配置application.properties和mybatis-config.xml等后，
以上面的getAll()方法为例，UserMapper.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.example.demo.mapper.UserMapper" > <resultMap id="UserMap" type="com.example.demo.entity.User"> <id column="id" jdbcType="INTEGER" property="id" /> <result property="name" column="name" jdbcType="VARCHAR" />  <association property="company" javaType="com.example.demo.entity.Company" column="company_id"> <id property="id" column="companyid"></id> <result property="companyName" column="companyname"></result> </association>  <collection property="accounts" ofType="com.example.demo.entity.Account" column="user_id"> <id property="id" column="accountid"></id> <result property="accountName" column="accountname"></result> </collection> </resultMap> <select id="getAll" resultMap="UserMap" > SELECT u.id,u.name,c.id companyid, c.name companyname, a.id accountid,a.name accountname FROM user u LEFT JOIN company c on u.company_id=c.id LEFT JOIN account a on u.id=a.user_id </select></mapper>

四、控制层

package com.example.demo.web;import org.springframework.beans.factory.annotation.Autowired;import org.springframework.web.bind.annotation.PathVariable;import org.springframework.web.bind.annotation.RequestMapping;import org.springframework.web.bind.annotation.RestController;import com.example.demo.entity.User;import com.example.demo.mapper.UserMapper;@RestControllerpublic class UserController { @Autowired private UserMapper userMapper; //请求例子：http://localhost:9001/getUserWithCompany/1 /*请求结果：{"id":1,"name":"aa","company":{"id":1,"companyName":"xx公司"},"accounts":null}*/ @RequestMapping("/getUserWithCompany/{id}") public User getUserWithCompany(@PathVariable("id") Long id) { User user = userMapper.getUserWithCompany(id); return user; } //请求例子：http://localhost:9001/getUserWithAccount/1 /*请求结果：{"id":1,"name":"aa","company":null,"accounts":[{"id":1,"accountName":"中行"},{"id":2,"accountName":"工行"}]}*/ @RequestMapping("/getUserWithAccount/{id}") public User getUserWithAccount(@PathVariable("id") Long id) { User user = userMapper.getUserWithAccount(id); return user; } //请求例子：http://localhost:9001/getUserWithAccount/1 /*请求结果：[{"id":1,"name":"aa","company":{"id":1,"companyName":"xx公司"},"accounts":[{"id":1,"accountName":"中行"}, {"id":2,"accountName":"工行"}]},{"id":2,"name":"bb","company":{"id":2,"companyName":"yy公司"},"accounts":[{"id":3,"accountName":"中行"}]}]*/ @RequestMapping("/getUsers") public List<User> getUsers() { List<User> users=userMapper.getAll(); return users; } }

看完上述内容，是不是对Spring boot2基于Mybatis实现多表关联查询的方法有进一步的了解，如果还想学习更多内容，欢迎关注亿速云行业资讯频道。