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
@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一對多分頁查詢數據的開發需求，更多細節內容，視頻直通車。

喜歡本文就【♥️推薦♥️】一下，激勵我持續創作。這個Github同樣精彩，收到您的star我會很激動。本文歸檔在專題博客，視頻講解在B站。