- 多表关联查询的实现有联表和嵌套查询两种,它们的差异在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 logs;
}
- 保存SQL的UserMapper.xml如下,先把联表查询的SQL写出来,结果在名为
leftJoinResultMap的resultMap中处理:
<?xml version="1.0" encoding="UTF-8"?>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}
- leftJoinResultMap这个resultMap是一对多的关键,里面的collection将log的所有记录映射到logs集合中:
- 接口定义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
u.id as user_id,
u.name as user_name,
u.age as user_age
from mybatis.user as u
where u.id = #{id}
- 上面的SQL显示结果保存在名为nestedResultMap的resultMap中,来看这个resultMap,如下,可见实体类的logs字段对应的是一个association节点,该节点的select属性代表这是个子查询,查询条件是user_id:
- 名为selectLogByUserId的SQL和resultMap如下,即查询log表:
select
l.id,
l.user_id,
l.action,
l.create_time
from mybatis.log as l
where l.user_id = #{user_id}
- 以上就是嵌套查询的关键点了,接下来按部就班的在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);
}