MyBatis初级实战之六：一对多关联查询

欢迎访问我的GitHub

https://github.com/zq2599/blog_demos

内容：所有原创文章分类汇总及配套源码，涉及Java、Docker、Kubernetes、DevOPS等；

本篇概览

  • 本文是《MyBatis初级实战》系列的第六篇，继续实践从多表获取数据；

  • 回顾上一篇，咱们实战了多表关联的一对一关系，如下图所示，查找日志记录时，把对应的用户信息查出：


    在这里插入图片描述

  • 本篇要实践的是<font color="blue">一对多关系</font>：查询用户记录时，把该用户的所有日志记录都查出来，逻辑关系如下图：

在这里插入图片描述
  • 在具体编码实现一对多查询时，分别使用联表和嵌套两种方式实现，每种方式都按照下图的步骤执行：
在这里插入图片描述

源码下载

  1. 如果您不想编码，可以在GitHub下载所有源码，地址和链接信息如下表所示(https://github.com/zq2599/blog_demos)：
名称 链接 备注
项目主页 https://github.com/zq2599/blog_demos 该项目在GitHub上的主页
git仓库地址(https) https://github.com/zq2599/blog_demos.git 该项目源码的仓库地址，https协议
git仓库地址(ssh) git@github.com:zq2599/blog_demos.git 该项目源码的仓库地址，ssh协议
  1. 这个git项目中有多个文件夹，本章的应用在<font color="blue">mybatis</font>文件夹下，如下图红框所示：
在这里插入图片描述
  1. <font color="blue">mybatis</font>是个父工程，里面有数个子工程，本篇的源码在<font color="blue">relatedoperation</font>子工程中，如下图红框所示：
在这里插入图片描述

准备数据

  1. 本次实战，在名为<font color="blue">mybatis</font>的数据库中建立两个表(和前面几篇文章中的表结构一模一样)：user和log表；
  2. user表记录用户信息，非常简单，只有三个字段：主键、名称、年龄
  3. log表记录用户行为，四个字段：主键、用户id、行为描述、行为时间
  4. user和log的关系如下图：
在这里插入图片描述
  1. 建表和添加数据的语句如下：
use mybatis;

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
  `id` int(32) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `age` int(32) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

DROP TABLE IF EXISTS `log`;

CREATE TABLE `log` (
  `id` int(32) NOT NULL AUTO_INCREMENT,
  `user_id` int(32),
  `action` varchar(255) NOT NULL,
  `create_time` datetime not null,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

INSERT INTO mybatis.user (id, name, age) VALUES (3, 'tom', 11);

INSERT INTO mybatis.log (id, user_id, action, create_time) VALUES (3, 3, 'read book', '2020-08-07 08:18:16');
INSERT INTO mybatis.log (id, user_id, action, create_time) VALUES (4, 3, 'go to the cinema', '2020-09-02 20:00:00');
INSERT INTO mybatis.log (id, user_id, action, create_time) VALUES (5, 3, 'have a meal', '2020-10-05 12:03:36');
INSERT INTO mybatis.log (id, user_id, action, create_time) VALUES (6, 3, 'have a sleep', '2020-10-06 13:00:12');
INSERT INTO mybatis.log (id, user_id, action, create_time) VALUES (7, 3, 'write', '2020-10-08 09:21:11');

关于多表关联查询的两种方式

  • 多表关联查询的实现有<font color="blue">联表</font>和<font color="blue">嵌套查询</font>两种，它们的差异在Mybatis中体现在resultMap的定义上：
  1. 联表时，resultMap内使用<font color="red">collection</font>子节点，将联表查询的结果映射到关联对象集合；
  2. 嵌套时，resultMap内使用<font color="red">association</font>子节点，association的<font color="red">select</font>属性触发一次新的查询；
  • 上述两种方式都能成功得到查询结果，接下来逐一尝试；

联表查询

  1. 本篇继续使用上一篇中创建的子工程<font color="blue">relatedoperation</font>；
  2. 实体类<font color="blue">UserWithLogs.java</font>如下，可见成员变量<font color="blue">logs</font>是用来保存该用户所有日志的集合：
package com.bolingcavalry.relatedoperation.entity;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.NoArgsConstructor;
import java.util.List;

@Data
@NoArgsConstructor
@ApiModel(description = "用户实体类（含行为日志集合）")
public class UserWithLogs {

    @ApiModelProperty(value = "用户ID")
    private Integer id;

    @ApiModelProperty(value = "用户名", required = true)
    private String name;

    @ApiModelProperty(value = "用户地址", required = false)
    private Integer age;

    @ApiModelProperty(value = "行为日志", required = false)
    private List<Log> logs;
}
  1. 保存SQL的<font color="blue">UserMapper.xml</font>如下，先把联表查询的SQL写出来，结果在名为
    <font color="blue">leftJoinResultMap</font>的resultMap中处理：
<?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.bolingcavalry.relatedoperation.mapper.UserMapper">

    <select id="leftJoinSel" parameterType="int" resultMap="leftJoinResultMap">
        select
            u.id as user_id,
            u.name as user_name,
            u.age as user_age,
            l.id as log_id,
            l.user_id as log_user_id,
            l.action as log_action,
            l.create_time as log_create_time

        from mybatis.user as u
                 left join mybatis.log as l
                           on u.id = l.user_id
        where u.id = #{id}
    </select>
</mapper>
  1. <font color="blue">leftJoinResultMap</font>这个resultMap是一对多的关键，里面的collection将log的所有记录映射到<font color="red">logs</font>集合中：
    <resultMap id="leftJoinResultMap" type="UserWithLogs">
        <id property="id" column="user_id"/>
        <result  property="name" column="user_name" jdbcType="VARCHAR"/>
        <result property="age" column="user_age" jdbcType="INTEGER" />
        <collection property="logs" ofType="Log">
            <id property="id" column="log_id"/>
            <result property="userId" column="log_user_id" jdbcType="INTEGER" />
            <result property="action" column="log_action" jdbcType="VARCHAR" />
            <result property="createTime" column="log_create_time" jdbcType="TIMESTAMP" />
        </collection>
    </resultMap>
  1. 接口定义UserMapper.java ：
@Repository
public interface UserMapper {
    UserWithLogs leftJoinSel(int id);
}
  1. service层：
@Service
public class UserService {
    @Autowired
    UserMapper userMapper;

    public UserWithLogs leftJoinSel(int id) {
        return userMapper.leftJoinSel(id);
    }
}
  1. controller层的代码略多，是因为想把swagger信息做得尽量完整：
@RestController
@RequestMapping("/user")
@Api(tags = {"UserController"})
public class UserController {
    @Autowired
    private UserService userService;

    @ApiOperation(value = "根据ID查找user记录（包含行为日志），联表查询", notes="根据ID查找user记录（包含行为日志），联表查询")
    @ApiImplicitParam(name = "id", value = "用户ID", paramType = "path", required = true, dataType = "Integer")
    @RequestMapping(value = "/leftjoin/{id}", method = RequestMethod.GET)
    public UserWithLogs leftJoinSel(@PathVariable int id){
        return userService.leftJoinSel(id);
    }
}
  1. 最后是单元测试，在前文创建的ControllerTest.java中新建内部类<font color="blue">User</font>用于user表相关的单元测试，可见封装了一个私有方法queryAndCheck负责请求和验证结果，后面的嵌套查询也会用到：
    @Nested
    @TestMethodOrder(MethodOrderer.OrderAnnotation.class)
    @DisplayName("用户服务")
    class User {

        /**
         * 通过用户ID获取用户信息有两种方式：left join和嵌套查询，
         * 从客户端来看，仅一部分path不同，因此将请求和检查封装到一个通用方法中，
         * 调用方法只需要指定不同的那一段path
         * @param subPath
         * @throws Exception
         */
        private void queryAndCheck(String subPath) throws Exception {
            String queryPath = "/user/" + subPath + "/" + TEST_USER_ID;

            log.info("query path [{}]", queryPath);

            mvc.perform(MockMvcRequestBuilders.get(queryPath).accept(MediaType.APPLICATION_JSON))
                    .andExpect(status().isOk())
                    .andExpect(jsonPath("$.id").value(TEST_USER_ID))
                    .andExpect(jsonPath("$..logs.length()").value(5))
                    .andDo(print());
        }

        @Test
        @DisplayName("通过用户ID获取用户信息(包含行为日志)，联表查询")
        @Order(1)
        void leftJoinSel() throws Exception {
            queryAndCheck(SEARCH_TYPE_LEFT_JOIN);
        }
    }
  1. 执行上述单元测试方法<font color="blue">leftJoinSel</font>，得到结果如下：
在这里插入图片描述
  1. 为了便于观察，我将上图红框中的JSON数据做了格式化，如下所示，可见log表中的五条记录都被关联出来了，作为整个user对象的一个字段：
{
    "id": 3,
    "name": "tom",
    "age": 11,
    "logs": [
        {
            "id": 3,
            "userId": 3,
            "action": "read book",
            "createTime": "2020-08-07"
        },
        {
            "id": 4,
            "userId": 3,
            "action": "go to the cinema",
            "createTime": "2020-09-02"
        },
        {
            "id": 5,
            "userId": 3,
            "action": "have a meal",
            "createTime": "2020-10-05"
        },
        {
            "id": 6,
            "userId": 3,
            "action": "have a sleep",
            "createTime": "2020-10-06"
        },
        {
            "id": 7,
            "userId": 3,
            "action": "write",
            "createTime": "2020-10-08"
        }
    ]
}
  1. 以上就是通过联表的方式获取一对多关联结果，接下来咱们尝试嵌套查询；

嵌套查询

  1. 嵌套查询的基本思路是将多次查询将结果合并，关键点还是在SQL和resultMap的配置上，先看嵌套查询的SQL，在<font color="blue">UserMapper.xml</font>文件中，如下，可见仅查询了user表，并未涉及log表：
    <select id="nestedSel" parameterType="int" resultMap="nestedResultMap">
        select
            u.id as user_id,
            u.name as user_name,
            u.age as user_age
        from mybatis.user as u
        where u.id = #{id}
    </select>
  1. 上面的SQL显示结果保存在名为<font color="blue">nestedResultMap</font>的resultMap中，来看这个resultMap，如下，可见实体类的<font color="blue">logs</font>字段对应的是一个<font color="blue">association</font>节点，该节点的<font color="blue">select</font>属性代表这是个子查询，查询条件是<font color="red">user_id</font>：
    <!-- association节点的select属性会触发嵌套查询-->
    <resultMap id="nestedResultMap" type="UserWithLogs">
        <!-- column属性中的user_id，来自前面查询时的"u.id as user_id" -->
        <id property="id" column="user_id"/>
        <!-- column属性中的user_name，来自前面查询时的"u.name as user_name" -->
        <result  property="name" column="user_name" jdbcType="VARCHAR"/>
        <!-- column属性中的user_age，来自前面查询时的"u.age as user_age" -->
        <result property="age" column="user_age" jdbcType="INTEGER" />
        <!-- select属性，表示这里要执行嵌套查询，将user_id传给嵌套的查询 -->
        <association property="logs" column="user_id" select="selectLogByUserId"></association>
    </resultMap>
  1. 名为selectLogByUserId的SQL和resultMap如下，即查询log表：
    <select id="selectLogByUserId" parameterType="int" resultMap="log">
        select
            l.id,
            l.user_id,
            l.action,
            l.create_time
        from mybatis.log as l
        where l.user_id = #{user_id}
    </select>

    <resultMap id="log" type="log">
        <id property="id" column="id"/>
        <result column="user_id" jdbcType="INTEGER" property="userId"/>
        <result column="action" jdbcType="VARCHAR" property="action"/>
        <result column="create_time" jdbcType="TIMESTAMP" property="createTime"/>
        <result column="user_name" jdbcType="VARCHAR" property="userName"/>
    </resultMap>
  1. 以上就是嵌套查询的关键点了，接下来按部就班的在LogMapper、LogService、LogController中添加方法即可，下面是LogController中对应的web接口，稍后会在单元测试中调用这个接口进行验证：
    @ApiOperation(value = "根据ID查找user记录（包含行为日志），嵌套查询", notes="根据ID查找user记录（包含行为日志），嵌套查询")
    @ApiImplicitParam(name = "id", value = "用户ID", paramType = "path", required = true, dataType = "Integer")
    @RequestMapping(value = "/nested/{id}", method = RequestMethod.GET)
    public UserWithLogs nestedSel(@PathVariable int id){
        return userService.nestedSel(id);
    }
  1. 单元测试的代码很简单，调用前面封装好的queryAndCheck方法即可：
        @Test
        @DisplayName("通过用户ID获取用户信息(包含行为日志)，嵌套查询")
        @Order(2)
        void nestedSel() throws Exception {
            queryAndCheck(SEARCH_TYPE_NESTED);
        }
  1. 执行单元测试的结果如下图红框所示，和前面的联表查询一样：
在这里插入图片描述
  • 两种方式的一对多关联查询都试过了，接下来看看两者的区别；

联表和嵌套的区别

  1. 首先是联表查询的日志，如下，只有一次查询：
2020-10-21 20:25:05.754  INFO 15408 --- [           main] c.b.r.controller.ControllerTest          : query path [/user/leftjoin/3]
2020-10-21 20:25:09.910  INFO 15408 --- [           main] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} inited
2020-10-21 20:25:09.925 DEBUG 15408 --- [           main] c.b.r.mapper.UserMapper.leftJoinSel      : ==>  Preparing: select u.id as user_id, u.name as user_name, u.age as user_age, l.id as log_id, l.user_id as log_user_id, l.action as log_action, l.create_time as log_create_time from mybatis.user as u left join mybatis.log as l on u.id = l.user_id where u.id = ?
2020-10-21 20:25:10.066 DEBUG 15408 --- [           main] c.b.r.mapper.UserMapper.leftJoinSel      : ==> Parameters: 3(Integer)
2020-10-21 20:25:10.092 DEBUG 15408 --- [           main] c.b.r.mapper.UserMapper.leftJoinSel      : <==      Total: 5
  1. 再来看看嵌套查询的日志，两次：
2020-10-21 20:37:29.648  INFO 24384 --- [           main] c.b.r.controller.ControllerTest          : query path [/user/nested/3]
2020-10-21 20:37:33.867  INFO 24384 --- [           main] com.alibaba.druid.pool.DruidDataSource   : {dataSource-1} inited
2020-10-21 20:37:33.880 DEBUG 24384 --- [           main] c.b.r.mapper.UserMapper.nestedSel        : ==>  Preparing: select u.id as user_id, u.name as user_name, u.age as user_age from mybatis.user as u where u.id = ?
2020-10-21 20:37:34.018 DEBUG 24384 --- [           main] c.b.r.mapper.UserMapper.nestedSel        : ==> Parameters: 3(Integer)
2020-10-21 20:37:34.041 DEBUG 24384 --- [           main] c.b.r.m.UserMapper.selectLogByUserId     : ====>  Preparing: select l.id, l.user_id, l.action, l.create_time from mybatis.log as l where l.user_id = ?
2020-10-21 20:37:34.043 DEBUG 24384 --- [           main] c.b.r.m.UserMapper.selectLogByUserId     : ====> Parameters: 3(Integer)
2020-10-21 20:37:34.046 DEBUG 24384 --- [           main] c.b.r.m.UserMapper.selectLogByUserId     : <====      Total: 5
2020-10-21 20:37:34.047 DEBUG 24384 --- [           main] c.b.r.mapper.UserMapper.nestedSel        : <==      Total: 1
  • 至此，MyBatis常用的多表关联查询实战就完成了，希望能给您一些参考，接下来的文章，咱们继续体验MyBatis带给我们的各种特性。

你不孤单，欣宸原创一路相伴

  1. Java系列
  2. Spring系列
  3. Docker系列
  4. kubernetes系列
  5. 数据库+中间件系列
  6. DevOps系列

欢迎关注公众号：程序员欣宸

微信搜索「程序员欣宸」，我是欣宸，期待与您一同畅游Java世界...
https://github.com/zq2599/blog_demos

©著作权归作者所有,转载或内容合作请联系作者
  • 人面猴
    序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
    沈念sama阅读 198,322评论 5赞 465
  • 死咒
    序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
    沈念sama阅读 83,288评论 2赞 375
  • 救了他两次的神仙让他今天三更去死
    文/潘晓璐 我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
    开封第一讲书人阅读 145,227评论 0赞 327
  • 道士缉凶录：失踪的卖姜人
    文/不坏的土叔 我叫张陵，是天一观的道长。 经常有香客问我，道长，这世上最难降的妖魔是什么？ 我笑而不...
    开封第一讲书人阅读 53,015评论 1赞 268
  • 港岛之恋（遗憾婚礼）
    正文 为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
    茶点故事阅读 61,936评论 5赞 359
  • 恶毒庶女顶嫁案：这布局不是一般人想出来的
    文/花漫 我一把揭开白布。 她就那样静静地躺着，像睡着了一般。 火红的嫁衣衬着肌肤如雪。 梳的纹丝不乱的头发上，一...
    开封第一讲书人阅读 47,534评论 1赞 275
  • 城市分裂传说
    那天，我揣着相机与录音，去河边找鬼。 笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。 我是一名探鬼主播，决...
    沈念sama阅读 36,995评论 3赞 389
  • 双鸳鸯连环套：你想象不到人心有多黑
    文/苍兰香墨 我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
    开封第一讲书人阅读 35,616评论 0赞 254
  • 万荣杀人案实录
    序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
    沈念sama阅读 39,907评论 1赞 294
  • 护林员之死
    正文 独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋 以下内容为张勋视角 年9月15日...
    茶点故事阅读 34,923评论 2赞 315
  • 白月光启示录
    正文 我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。 大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
    茶点故事阅读 36,741评论 1赞 328
  • 活死人
    序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
    沈念sama阅读 32,525评论 3赞 316
  • 日本核电站爆炸内幕
    正文 年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
    茶点故事阅读 38,016评论 3赞 301
  • 男人毒药：我在死后第九天来索命
    文/蒙蒙 一、第九天 我趴在偏房一处隐蔽的房顶上张望。 院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
    开封第一讲书人阅读 29,141评论 0赞 19
  • 一桩弑父案，背后竟有这般阴谋
    文/苍兰香墨 我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。 一阵脚步声响...
    开封第一讲书人阅读 30,453评论 1赞 255
  • 情欲美人皮
    我被黑心中介骗来泰国打工， 没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。 一个月前我还...
    沈念sama阅读 42,054评论 2赞 343
  • 代替公主和亲
    正文 我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。 传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
    茶点故事阅读 41,249评论 2赞 339

推荐阅读更多精彩内容