一、序言
在日常一线开发过程中，多表连接查询不可或缺，基于MybatisPlus多表连接查询究竟该如何实现，本文将带你找到答案。
在多表连接查询中，既有查询单条记录的情况，又有列表查询，还有分页查询，这些需求与多表连接是什么关系，又该如何实现，这是本文讨论的中心内容。
二、实战编码
1、两个关联DO
部门DO
@Data
@NoArgsConstructor
@AllArgsConstructor
@TableName(value = "tb_dept")
public class Dept {
private static final long serialVersionUID = 1L;
@TableId(type = IdType.AUTO)
private Long deptId;
private String deptName;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private LocalDateTime gmtCreate;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private LocalDateTime gmtModified;
private String tel;
public Dept(Dept dept) {
if (Objects.nonNull(dept)) {
this.deptId = dept.deptId;
this.deptName = dept.deptName;
this.gmtCreate = dept.gmtCreate;
this.gmtModified = dept.gmtModified;
this.tel = dept.tel;
}
}
}
用户DO
@Data
@NoArgsConstructor
@AllArgsConstructor
@TableName(value = "tb_user")
public class User {
private static final long serialVersionUID = 1L;
private Integer age;
private Long deptId;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private LocalDateTime gmtCreate;
@JsonFormat(pattern = "yyyy-MM-dd HH:mm:ss")
private LocalDateTime gmtModified;
@TableId(type = IdType.AUTO)
private Long userId;
private String userName;
public User(User user) {
if (Objects.nonNull(user)) {
this.age = user.age;
this.deptId = user.deptId;
this.gmtCreate = user.gmtCreate;
this.gmtModified = user.gmtModified;
this.userId = user.userId;
this.userName = user.userName;
}
}
}
2、部门VO
@Data
@NoArgsConstructor
@AllArgsConstructor
public class DeptVo extends Dept {
private List<User> userList;
/**
* 实现部门DO 转 部门VO
* @param dept
*/
public DeptVo(Dept dept) {
super(dept);
}
}
3、普通编码查询数据
public IPage<DeptVo> selectDeptPage3() {
LambdaQueryWrapper<Dept> wrapper = Wrappers.lambdaQuery(Dept.class);
Page<Dept> deptPage = this.page(new Page<>(1, 3), wrapper);
IPage<DeptVo> deptVoPage = EntityUtils.toPage(deptPage, DeptVo::new);
// 完成userList字段属性注入
Set<Long> deptIds = EntityUtils.toSet(deptVoPage.getRecords(), DeptVo::getDeptId);
if (deptIds.size() > 0) {
List<User> userList = userMapper.selectList(Wrappers.lambdaQuery(User.class)
.in(User::getDeptId, deptIds));
Map<Long, List<User>> map = EntityUtils.groupBy(userList, User::getDeptId);
for (DeptVo deptVo : deptVoPage.getRecords()) {
deptVo.setUserList(map.get(deptVo.getDeptId()));
}
}
return deptVoPage;
}
4、使用工具类查询数据
优化版 一行代码完成userList属性注入
/**
* 优化版 一行代码完成userList属性注入
*/
@Override
public IPage<DeptVo> selectDeptPage4() {
LambdaQueryWrapper<Dept> wrapper = Wrappers.lambdaQuery(Dept.class);
Page<Dept> deptPage = this.page(new Page<>(1, 3), wrapper);
IPage<DeptVo> deptVoPage = EntityUtils.toPage(deptPage, DeptVo::new);
// 一行代码完成userList属性注入
FieldInjectUtils.injectListField(deptVoPage, DeptVo::getDeptId, UserServiceImpl.class, User::getDeptId, DeptVo::getUserList);
return deptVoPage;
}
需要指出的是FieldInjectUtils在工具包下
<dependency>
<groupId>xin.altitude.cms</groupId>
<artifactId>ucode-cms-common</artifactId>
<version>1.5.9.2</version>
</dependency>
学习源码的朋友，源码直通车
5、演示数据
{
"code": 200,
"msg": "操作成功",
"data": {
"records": [
{
"deptId": "10",
"deptName": "Java",
"gmtCreate": "2020-10-30 11:48:19",
"gmtModified": "2021-05-24 15:11:17",
"tel": "88886666",
"userList": [
{
"age": 12,
"deptId": "10",
"gmtCreate": null,
"gmtModified": "2022-11-05 16:44:22",
"userId": "1",
"userName": "Jone"
}
]
},
{
"deptId": "11",
"deptName": "Mysql",
"gmtCreate": "2020-10-30 11:48:44",
"gmtModified": "2021-05-24 15:11:20",
"tel": "80802121",
"userList": [
{
"age": 23,
"deptId": "11",
"gmtCreate": null,
"gmtModified": "2022-11-05 16:44:24",
"userId": "2",
"userName": "Jack"
},
{
"age": 21,
"deptId": "11",
"gmtCreate": "2022-11-05 16:09:42",
"gmtModified": "2022-11-05 16:11:28",
"userId": "5",
"userName": "滴滴"
}
]
},
{
"deptId": "12",
"deptName": "Tomcat",
"gmtCreate": "2020-10-30 11:48:44",
"gmtModified": "2021-05-24 15:11:23",
"tel": "23231212",
"userList": [
{
"age": 22,
"deptId": "12",
"gmtCreate": null,
"gmtModified": "2022-11-05 16:44:27",
"userId": "3",
"userName": "Billie"
},
{
"age": 12,
"deptId": "12",
"gmtCreate": "2021-06-05 19:22:46",
"gmtModified": "2021-10-21 14:38:26",
"userId": "4",
"userName": "didi"
},
{
"age": 18,
"deptId": "12",
"gmtCreate": "2022-11-05 16:10:48",
"gmtModified": "2022-11-05 16:11:36",
"userId": "6",
"userName": "嗒嗒"
}
]
}
],
"total": 4,
"size": 3,
"current": 1,
"orders": [],
"optimizeCountSql": true,
"searchCount": true,
"countId": null,
"maxLimit": null,
"pages": 2
}
}
三、小结
本文完成了MybatisPlus一对多分页查询数据的开发需求，更多细节内容，视频直通车。
