Z次元
分类 归档 留言 友链 关于
MyBatis多表联查
知识积累
最后更新：2024/9/28 | 创建时间：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
将Maven项目组件上传到Maven中心仓（借助sonatype）
2024/6/8
String.valueOf()踩坑记录
2024/5/23
评论区
发表评论
回复

1

rantrism2022年7月26日

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