一挣菲、序言
在日常一線開發(fā)過(guò)程中，多表連接查詢不可或缺掷邦，基于MybatisPlus多表連接查詢究竟該如何實(shí)現(xiàn)白胀，本文將帶你找到答案。
在多表連接查詢中抚岗，既有查詢單條記錄的情況或杠，又有列表查詢，還有分頁(yè)查詢宣蔚，這些需求與多表連接是什么關(guān)系廷痘，又該如何實(shí)現(xiàn)，這是本文討論的中心內(nèi)容件已。
二笋额、實(shí)戰(zhàn)編碼
1、兩個(gè)關(guān)聯(lián)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;
/**
* 實(shí)現(xiàn)部門DO 轉(zhuǎn) 部門VO
* @param dept
*/
public DeptVo(Dept dept) {
super(dept);
}
}
3兄猩、普通編碼查詢數(shù)據(jù)
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、使用工具類查詢數(shù)據(jù)
優(yōu)化版 一行代碼完成userList屬性注入
/**
* 優(yōu)化版 一行代碼完成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>
學(xué)習(xí)源碼的朋友鉴未，源碼直通車
5枢冤、演示數(shù)據(jù)
{
"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
}
}
三、小結(jié)
本文完成了MybatisPlus一對(duì)多分頁(yè)查詢數(shù)據(jù)的開發(fā)需求铜秆，更多細(xì)節(jié)內(nèi)容淹真，視頻直通車。
