MybatisPlus多表连接查询一对多分页查询数据

一、序言

在日常一线开发过程中，多表连接查询不可或缺，基于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@AllArgsConstructorpublic class DeptVo extends Dept {    private List userList;    /**     * 实现部门DO 转 部门VO     * @param dept     */    public DeptVo(Dept dept) {        super(dept);    }}

3、普通编码查询数据

public IPage selectDeptPage3() {    LambdaQueryWrapper wrapper = Wrappers.lambdaQuery(Dept.class);    Page deptPage = this.page(new Page(1, 3), wrapper);    IPage deptVoPage = EntityUtils.toPage(deptPage, DeptVo::new);    // 完成userList字段属性注入    Set deptIds = EntityUtils.toSet(deptVoPage.getRecords(), DeptVo::getDeptId);    if (deptIds.size() > 0) {        List userList = userMapper.selectList(Wrappers.lambdaQuery(User.class)                                                    .in(User::getDeptId, deptIds));        Map<Long, List> map = EntityUtils.groupBy(userList, User::getDeptId);        for (DeptVo deptVo : deptVoPage.getRecords()) {            deptVo.setUserList(map.get(deptVo.getDeptId()));        }    }    return deptVoPage;}

4、使用工具类查询数据

优化版 一行代码完成userList属性注入

/** * 优化版 一行代码完成userList属性注入 */@Overridepublic IPage selectDeptPage4() {    LambdaQueryWrapper wrapper = Wrappers.lambdaQuery(Dept.class);    Page deptPage = this.page(new Page(1, 3), wrapper);    IPage deptVoPage = EntityUtils.toPage(deptPage, DeptVo::new);    //  一行代码完成userList属性注入    FieldInjectUtils.injectListField(deptVoPage, DeptVo::getDeptId, UserServiceImpl.class, User::getDeptId, DeptVo::getUserList);    return deptVoPage;}

需要指出的是FieldInjectUtils在工具包下

    xin.altitude.cms    ucode-cms-common    1.5.9.2

学习源码的朋友，源码直通车

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一对多分页查询数据的开发需求，更多细节内容，视频直通车。

喜欢本文就【♥️推荐♥️】一下，激励我持续创作。这个Github同样精彩，收到您的star我会很激动。本文归档在专题博客，视频讲解在B站。