歡迎訪問我的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