MyBatis初級實戰之六：一對多關聯查詢

程式設計師欣宸發表於2021-01-22

MyBatis

歡迎訪問我的GitHub

https://github.com/zq2599/blog_demos

內容：所有原創文章分類彙總及配套原始碼，涉及Java、Docker、Kubernetes、DevOPS等；

本篇概覽

本文是《MyBatis初級實戰》系列的第六篇，繼續實踐從多表獲取資料；
回顧上一篇，我們們實戰了多表關聯的一對一關係，如下圖所示，查詢日誌記錄時，把對應的使用者資訊查出：
本篇要實踐的是一對多關係：查詢使用者記錄時，把該使用者的所有日誌記錄都查出來，邏輯關係如下圖：

在這裡插入圖片描述

在具體編碼實現一對多查詢時，分別使用聯表和巢狀兩種方式實現，每種方式都按照下圖的步驟執行：

在這裡插入圖片描述

原始碼下載

如果您不想編碼，可以在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協議

這個git專案中有多個資料夾，本章的應用在mybatis資料夾下，如下圖紅框所示：

在這裡插入圖片描述
3. mybatis是個父工程，裡面有數個子工程，本篇的原始碼在relatedoperation子工程中，如下圖紅框所示：

在這裡插入圖片描述

準備資料

本次實戰，在名為mybatis的資料庫中建立兩個表(和前面幾篇文章中的表結構一模一樣)：user和log表；
user表記錄使用者資訊，非常簡單，只有三個欄位：主鍵、名稱、年齡
log表記錄使用者行為，四個欄位：主鍵、使用者id、行為描述、行為時間
user和log的關係如下圖：

在這裡插入圖片描述
5. 建表和新增資料的語句如下：

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');

關於多表關聯查詢的兩種方式

多表關聯查詢的實現有聯表和巢狀查詢兩種，它們的差異在Mybatis中體現在resultMap的定義上：

聯表時，resultMap內使用collection子節點，將聯表查詢的結果對映到關聯物件集合；
巢狀時，resultMap內使用association子節點，association的select屬性觸發一次新的查詢；

上述兩種方式都能成功得到查詢結果，接下來逐一嘗試；

聯表查詢

本篇繼續使用上一篇中建立的子工程relatedoperation；
實體類UserWithLogs.java如下，可見成員變數logs是用來儲存該使用者所有日誌的集合：

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

儲存SQL的UserMapper.xml如下，先把聯表查詢的SQL寫出來，結果在名為
leftJoinResultMap的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>

leftJoinResultMap這個resultMap是一對多的關鍵，裡面的collection將log的所有記錄對映到logs集合中：

    <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>

介面定義UserMapper.java ：

@Repository
public interface UserMapper {
    UserWithLogs leftJoinSel(int id);
}

service層：

@Service
public class UserService {
    @Autowired
    UserMapper userMapper;

    public UserWithLogs leftJoinSel(int id) {
        return userMapper.leftJoinSel(id);
    }
}

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

最後是單元測試，在前文建立的ControllerTest.java中新建內部類User用於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);
        }
    }

執行上述單元測試方法leftJoinSel，得到結果如下：

在這裡插入圖片描述

為了便於觀察，我將上圖紅框中的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"
        }
    ]
}

以上就是通過聯表的方式獲取一對多關聯結果，接下來我們們嘗試巢狀查詢；

巢狀查詢

巢狀查詢的基本思路是將多次查詢將結果合併，關鍵點還是在SQL和resultMap的配置上，先看巢狀查詢的SQL，在UserMapper.xml檔案中，如下，可見僅查詢了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>

上面的SQL顯示結果儲存在名為nestedResultMap的resultMap中，來看這個resultMap，如下，可見實體類的logs欄位對應的是一個association節點，該節點的select屬性代表這是個子查詢，查詢條件是user_id：

    <!-- 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>

名為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>

以上就是巢狀查詢的關鍵點了，接下來按部就班的在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);
    }

單元測試的程式碼很簡單，呼叫前面封裝好的queryAndCheck方法即可：

        @Test
        @DisplayName("通過使用者ID獲取使用者資訊(包含行為日誌)，巢狀查詢")
        @Order(2)
        void nestedSel() throws Exception {
            queryAndCheck(SEARCH_TYPE_NESTED);
        }

執行單元測試的結果如下圖紅框所示，和前面的聯表查詢一樣：

在這裡插入圖片描述

兩種方式的一對多關聯查詢都試過了，接下來看看兩者的區別；

聯表和巢狀的區別

首先是聯表查詢的日誌，如下，只有一次查詢：

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

再來看看巢狀查詢的日誌，兩次：

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帶給我們的各種特性。

你不孤單，欣宸原創一路相伴

歡迎關注公眾號：程式設計師欣宸

微信搜尋「程式設計師欣宸」，我是欣宸，期待與您一同暢遊Java世界...
https://github.com/zq2599/blog_demos

MyBatis初級實戰之五：一對一關聯查詢
2021-01-21
MyBatis
Mybatis【15】-- Mybatis一對一多表關聯查詢
2021-01-26
MyBatis
MyBatis(六) sql片段定義、級聯查詢、巢狀查詢
2018-07-09
MyBatisSQL巢狀
MyBatis表關聯一對多多對一多對多
2018-09-11
MyBatis
MyBatis關聯查詢
2016-08-28
MyBatis
spring data jpa關聯查詢（一對一、一對多、多對多）
2018-08-17
Spring
解決 mybatis一對多分頁問題聯級查詢
2020-09-25
MyBatis
Mybatis plus 一對多關聯查詢分頁不準確的問題
2023-12-29
MyBatis
MyBatis初級實戰之四：druid多資料來源
2021-01-20
MyBatisUI
#MyBatis多表查詢 #多對一、一對多的兩種實現方式 @FDDLC
2020-10-14
MyBatis
MyBatis初級實戰之二：增刪改查
2021-01-18
MyBatis
MyBatis基礎：MyBatis關聯查詢（4）
2017-09-13
MyBatis
gorm 關係一對一,一對多,多對多查詢
2019-12-30
GoORM
MyBatis實現一對一關聯對映
2022-08-02
MyBatis
兩表關聯查詢：sql、mybatis
2024-04-12
SQLMyBatis
mybatis 一對一關聯
2016-01-05
MyBatis
MyBatis初級實戰之一：Spring Boot整合
2021-01-15
MyBatisSpring Boot
Laravel 關聯查詢 ——一對一簡單例子
2018-11-06
Laravel單例
spring data jpa 多對一聯表查詢
2019-03-16
Spring
Mybatis實體關聯對映
2019-01-16
MyBatis
mybatis一對多查詢resultMap只返回了一條記錄
2021-11-27
MyBatis
Spring Boot整合Mybatis完成級聯一對多CRUD操作
2019-06-06
Spring BootMyBatis
Mybatis 多表關聯查詢（1） one-to-one關係
2017-04-27
MyBatis
mybatis入門基礎(六)----高階對映(一對一，一對多，多對多)
2015-07-07
MyBatis
mybatis基礎系列（四）——關聯查詢、延遲載入、一級快取與二級快取
2018-11-17
MyBatis快取
JPA(3) 表關聯關係(多對一、一對多、多對多、一對一)
2018-11-15
jpa一對多查詢
2020-05-21
springDataJpa聯表查詢之多對多
2019-08-21
Spring
hibernate之關於使用連線表實現多對一關聯對映
2015-06-04
Laravel 5 關聯查詢 ——一對 N 簡單例子
2018-11-06
Laravel單例
多對多關係自行維護單項關聯數量,加快分頁查詢
2021-01-08
模型關聯一對多
2021-03-31
模型
MyBatis加強（1）~myBatis物件關係對映（多對一關係、一對多關係）、延遲/懶載入
2022-02-06
MyBatis物件
好程式設計師Java學習路線分享MyBatis之關聯查詢
2019-09-23
程式設計師JavaMyBatis
mybatis關聯關係對映
2018-12-21
MyBatis
如何實現引數級聯查詢
2019-12-04
關於Hibernate多層1對多關係查詢
2009-04-23
資料庫AR之關聯查詢
2015-02-10
資料庫

MyBatis初級實戰之六：一對多關聯查詢

歡迎訪問我的GitHub

本篇概覽

原始碼下載

準備資料

關於多表關聯查詢的兩種方式

聯表查詢

巢狀查詢

聯表和巢狀的區別

你不孤單，欣宸原創一路相伴

歡迎關注公眾號：程式設計師欣宸

相關文章