Z次元
文章 笔记 日志
专题
专栏分类 文章归档
友链
友情链接 朋友圈
留言
头像
系列文章
MyBatis多表联查
系列文章
知识积累
最后更新:2024/12/15|创建时间:2022/6/26
文章摘要
文章介绍了在Spring Boot项目中使用MyBatis进行多表联查的方法,并通过MyBatis的映射文件(XML)配置和实体类(Entity)的关联属性实现复杂的查询。示例包括查询信息对应的用户、用户下的所有信息、设置集合排序规则、以及用户与角色的多对多关系查询。

前言

创建数据库表(user和message),并插入数据:

CREATE TABLE `user` (
`id`  varchar(64) NOT NULL ,
`name`  varchar(64) NULL ,
PRIMARY KEY (`id`)
);

CREATE TABLE `info` (
`id`  varchar(64) NOT NULL ,
`uid`  varchar(64) NULL ,
`msg`  varchar(255) NULL ,
PRIMARY KEY (`id`)
);


INSERT INTO `user` (`id`, `name`) VALUES ('1001', 'ahzoo');
INSERT INTO `user` (`id`, `name`) VALUES ('1002', 'ouo');
INSERT INTO `info` (`id`, `uid`, `msg`) VALUES ('2001', '1001', '测试信息');
INSERT INTO `info` (`id`, `uid`, `msg`) VALUES ('2002', '1001', '第二条信息');

再创建两个数据库表(role和user_role),并插入数据,用于多对多操作:

CREATE TABLE `role` (
`id`  varchar(64) NOT NULL ,
`name`  varchar(64) NULL ,
PRIMARY KEY (`id`)
);


CREATE TABLE `user_role` (
`user_id`  varchar(64) NOT NULL ,
`role_id`  varchar(64) NOT NULL 
);


INSERT INTO `role` (`id`, `name`) VALUES ('3001', '用户');
INSERT INTO `role` (`id`, `name`) VALUES ('3002', '管理员');

INSERT INTO `user_role` (`user_id`, `role_id`) VALUES ('1001', '3001');
INSERT INTO `user_role` (`user_id`, `role_id`) VALUES ('1001', '3002');
INSERT INTO `user_role` (`user_id`, `role_id`) VALUES ('1002', '3002');

创建一个springboot项目,并生成增删改查,快速开始。
项目结构:
图片
依赖:

    <dependencies>
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
        </dependency>
        <dependency>
            <groupId>org.mybatis.spring.boot</groupId>
            <artifactId>mybatis-spring-boot-starter</artifactId>
            <version>2.2.2</version>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        <dependency>
            <groupId>org.projectlombok</groupId>
            <artifactId>lombok</artifactId>
            <optional>true</optional>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency>
    </dependencies>

一对一

一条信息对应一个发送者,查询信息的发送者。
在Info实体类中增加User对象,这样在对user和info进行联查时,将User对象一起返回:
entity/Info

import lombok.Data;
@Data
public class Info {
    private String id;
    private String uid;
    private String msg;
//    接收一对一查询到的User对象
    private User user;
}

映射文件:
mapper/InfoDao.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.InfoMapper">

<!--    使用Map对象作为返回结果-->
    <select id="selectListToMap" resultType="Map">
        SELECT u.id,u.name,i.id,i.uid,i.msg 
        FROM user u,info i 
        WHERE u.id=i.uid
    </select>


    <resultMap id="BaseResultMap" type="com.example.demo.entity.Info">
        <id property="id" column="id" jdbcType="VARCHAR"/>
        <result property="uid" column="uid" jdbcType="VARCHAR"/>
        <result property="msg" column="msg" jdbcType="VARCHAR"/>
        <association property="user" javaType="com.example.demo.entity.User">
<!--            这里为id字段取个别名(user_id),方便与上面的表的id字段进行区分,然后在下面对别名使用AS进行映射即可-->
            <id property="id" column="user_id" jdbcType="VARCHAR"/>
            <result property="name" column="name" jdbcType="VARCHAR"/>
        </association>
    </resultMap>


<!--    使用Info对象作为返回结果-->
    <select id="selectListByMap" resultMap="BaseResultMap">
        SELECT u.id AS user_id,u.name,i.id,i.uid,i.msg 
        FROM user u,info i 
        WHERE u.id=i.uid
    </select>

</mapper>

持久层:
mapper/InfoMapper

import com.example.demo.entity.Info;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
import java.util.Map;

@Mapper
public interface InfoMapper {
    List<Map<String,String>> selectListToMap();
    List<Info> selectListByMap();
}

测试:
查询目标信息对应的用户信息

import com.example.demo.entity.Info;
import com.example.demo.mapper.InfoMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;
import java.util.Map;


@SpringBootTest
class InfoTests {

    @Autowired
    InfoMapper infoMapper;

    /**
     * 使用Map对象作为返回结果
     */
    @Test
    public void toGetMapResult(){
        List<Map<String, String>> infos = infoMapper.selectListToMap();
        infos.forEach(System.out::println); 
        /*
        {msg=测试信息, uid=1001, name=ahzoo, id=1001}
        {msg=第二条信息, uid=1001, name=ahzoo, id=1001}
         */
    }

    /**
     * 使用Info对象作为返回结果
     */
    @Test
    public void toGetInfoResult(){
        List<Info> infos = infoMapper.selectListByMap();
        infos.forEach(System.out::println); 
        /*
        Info(id=2001, uid=1001, msg=测试信息, user=User(id=1001, name=ahzoo))
        Info(id=2002, uid=1001, msg=第二条信息, user=User(id=1001, name=ahzoo))
         */
    }


}

图片

一对多

一个用户可以发送多条信息,查询用户下的所有信息。
在User实体类中增加Info的List对象,用于返回查询到的多个Info对象:
entity/User

import lombok.Data;
import java.util.List;

@Data
public class User{
    private String id;
    private String name;
//    接收一对多查询到的Info对象集合
    private List<Info> infos;
}

持久层:
mapper/UserMapper

import com.example.demo.entity.User;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface UserMapper {
    List<User> findAllByMap();
}

映射文件:
mapper/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="BaseResultMap" type="com.example.demo.entity.User">
        <id property="id" column="id" jdbcType="VARCHAR"/>
        <result property="name" column="name" jdbcType="VARCHAR"/>
        <!--配置集合信息
        property:自定义集合的名称
        ofType:集合的数据类型
        -->
        <collection property="infos" ofType="com.example.demo.entity.Info">
            <result property="id" column="info_id" jdbcType="VARCHAR"/>
            <result property="uid" column="uid" jdbcType="VARCHAR"/>
            <result property="msg" column="msg" jdbcType="VARCHAR"/>
        </collection>
    </resultMap>


    <!-- 查询目标用户所有发布的信息(这里使用左外连接查询) -->
    <select id="findAllByMap" resultMap="BaseResultMap">
        SELECT u.id,u.name,i.id AS info_id,i.uid,i.msg
        FROM user u
        LEFT OUTER JOIN info i
             ON u.id = i.uid;
    </select>

</mapper>

测试:

import com.example.demo.entity.User;
import com.example.demo.mapper.UserMapper;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;

import java.util.List;
@SpringBootTest
public class UserTests {

    @Autowired
    UserMapper userMapper;

    @Test
    public void toGetAll(){
        List<User> allByMap = userMapper.findAllByMap();
        allByMap.forEach(System.out::println);
        /*
        User(id=1001, name=ahzoo, infos=[Info(id=2001, uid=1001, msg=测试信息, user=null), Info(id=2002, uid=1001, msg=第二条信息, user=null)])
         */
    }

}

可以看到所有用户下的所有信息都被查询到

图片

集合中元素的复杂规则设置

在一对多的查询中,有时可能会需要对集合中的数据做额外的限制。此时可以在连接查询时对集合对象对应的表构建为临时表,在临时表中添加限制条件实现。

示例:

在一对多中的集合数据(List<User>对象内嵌套的List<Info>对象)设置排序规则:

    <resultMap id="BaseResultMap" type="com.example.demo.entity.User">
        <id property="id" column="id" jdbcType="VARCHAR"/>
        <result property="name" column="name" jdbcType="VARCHAR"/>
        <collection property="infos" ofType="com.example.demo.entity.Info">
            <result property="id" column="info_id" jdbcType="VARCHAR"/>
            <result property="uid" column="uid" jdbcType="VARCHAR"/>
            <result property="msg" column="msg" jdbcType="VARCHAR"/>
        </collection>
    </resultMap>

    <select id="findAllByMap" resultMap="BaseResultMap">
        SELECT u.id, u.name, i.id AS info_id, i.uid, i.msg
        FROM user u
                 LEFT OUTER JOIN 
        						# 这里将连接查询的info表构建为自定义的临时表,来实现限制查询
        						(SELECT id AS info_id, i.uid, i.msg
                                  FROM info
                                  ORDER BY id ASC) i
                                 ON u.id = i.uid;
    </select>

多对多

一个用户可以有多个角色,一个角色可以属于多个用户,查询用户对应的角色,及角色对应的用户。
在User实体类中增加Role的List对象,用于返回查询到的多个Role对象:
entity/User

import lombok.Data;
import java.util.List;

@Data
public class User{
    private String id;
    private String name;
    private List<Role> roles;
}

同理:
entity/Role

import lombok.Data;
import java.util.List;

@Data
public class Role {

    private String id;
    private String name;
    private List<User> users;
}

持久层:
mapper/RoleMapper

import com.example.demo.entity.Role;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface RoleMapper {
    List<Role> getAllUser();
}

mapper/UserMapper

import com.example.demo.entity.User;
import org.apache.ibatis.annotations.Mapper;
import java.util.List;
@Mapper
public interface UserMapper {
    List<User> getAllRole();
}

映射文件:
mapper/RoleMapper

    <resultMap id="roleMap" type="com.example.demo.entity.Role">
        <id property="id" column="id" jdbcType="VARCHAR"/>
        <result property="name" column="name" jdbcType="VARCHAR"/>
        <collection property="users" ofType="com.example.demo.entity.User">
            <result property="id" column="role_id" jdbcType="VARCHAR"/>
            <result property="name" column="name" jdbcType="VARCHAR"/>
        </collection>
    </resultMap>

    <select id="getAllUser" resultMap="roleMap">
        SELECT u.id,u.name,r.id AS role_id,r.name
        FROM user u
        LEFT OUTER JOIN user_role ur ON u.id = ur.user_id
        LEFT OUTER JOIN role r ON r.id = ur.role_id
    </select>

mapper/UserMapper

    <resultMap id="userMap" type="com.example.demo.entity.User">
        <id property="id" column="id" jdbcType="VARCHAR"/>
        <result property="name" column="name" jdbcType="VARCHAR"/>
        <collection property="roles" ofType="com.example.demo.entity.Role">
            <result property="id" column="role_id" jdbcType="VARCHAR"/>
            <result property="name" column="name" jdbcType="VARCHAR"/>
        </collection>
    </resultMap>

    <select id="getAllRole" resultMap="userMap">
        SELECT u.id,u.name,r.id AS role_id,r.name
        FROM user u
        LEFT OUTER JOIN user_role ur ON u.id = ur.user_id
        LEFT OUTER JOIN role r ON r.id = ur.role_id
    </select>

测试:

    @Autowired
    RoleMapper roleMapper;
    @Autowired
    UserMapper userMapper;

    @Test
    public void toGetAllUser(){
        List<Role> allUser = roleMapper.getAllUser();
        allUser.forEach(System.out::println);
/*
        Role(id=1001, name=ahzoo, users=[User(id=3001, name=ahzoo, roles=null), User(id=3002, name=ahzoo, roles=null)])
        Role(id=1002, name=ouo, users=[User(id=null, name=ouo, roles=null)])
*/

    }
    @Test
    public void toGetAllRole(){
        List<User> allRole = userMapper.getAllRole();
        allRole.forEach(System.out::println);

/*
        User(id=1001, name=ahzoo, roles=[Role(id=3001, name=ahzoo, users=null), Role(id=3002, name=ahzoo, users=null)])
        User(id=1002, name=ouo, roles=[Role(id=null, name=ouo, users=null)])

 */
    }

图片
图片**

版权声明
本文依据 CC-BY-NC-SA 4.0 许可协议授权,请您在转载时注明文章来源为 Z次元 ,若本文涉及转载第三方内容,请您一同注明。
更多专栏文章推荐
知识积累
人非生而知之者,孰能无惑?惑而不从师,其为惑也,终不解矣。
轮询、长轮询、长连接、WebSocket
2022/5/4
WebSocket
2022/4/30
SpringBoot集成轻量级搜索引擎——Meilisearch
2025/8/7
JDK新版特性(18-21)
2025/2/7
评论区

删除确认

评论删除后无法恢复,请确认是否继续?
发表评论
删除 编辑 回复
r

1

rantrism2022年7月26日

您好~我是腾讯云开发者社区运营,关注了您分享的技术文章,觉得内容很棒,我们诚挚邀请您加入腾讯云自媒体分享计划。完整福利和申请地址请见:https://cloud.tencent.com/developer/support-plan
作者申请此计划后将作者的文章进行搬迁同步到社区的专栏下,你只需要简单填写一下表单申请即可,我们会给作者提供包括流量、云服务器等,另外还有些周边礼物。

前言
一对一
一对多
集合中元素的复杂规则设置
多对多
目录
前言
一对一
一对多
集合中元素的复杂规则设置
多对多
博客
文章 笔记 日志
专题
专栏分类 文章归档
友链
友情链接 朋友圈
交流
留言 关于我
主页
菜单
置顶
主题
我的
十玖八柒
每天进步多一点
欢迎到访φ(゜▽゜*)♪
最新评论
个人占星:
DeepSeek没有想象中的好用
个人占星:
想给自己的网站弄个统计功能,但不会弄,头疼
永恒末匕:
好哇塞,这个厉害
Corwin: @十玖八柒
哎 主要是我的个人网站用的是静态的cos 实现评论框还是有点困难
我的
关于我
个人主页
站点地图
RSS订阅
导航
十年之约
虫洞穿梭
开源博客
前端开源仓库
后端开源仓库
©2020 - 2025 By 十玖八柒 版权所有
豫ICP备20021466号