MybatisPlus Lambda表达式聚合查询分组查询 COUNT SUM AVG MIN MAX GroupBy

梦里梦外; 2024-04-01 16:00 51阅读 0赞

#### 一、序言 ####

众所周知，MybatisPlus在处理单表DAO操作时非常的方便。在处理[多表连接连接][Link 1]查询也有优雅的解决方案。今天分享MybatisPlus基于Lambda表达式优雅实现聚合分组查询。

下面的内容是博客文字版。

#### 二、代码实现 ####

1、用户实体类

@TableName(value = "tb_user")
    public class User {
        private static final long serialVersionUID = 1L;
        private Integer age;
        private Long deptId;
        @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.userId = user.userId;
                this.userName = user.userName;
            }
        }
    }

2、用户聚合类

本类属于核心代码

@TableName(value = "tb_user")
    public class UserAggr {
        
        private Long deptId;
        /**
         * 新增的一列数据 用于存储count字段
         * 注解非常关键
         */
        @TableField(value = "count(*)", insertStrategy = FieldStrategy.NEVER, updateStrategy = FieldStrategy.NEVER)
        private Long count;
        
        public UserAggr(UserAggr user) {
            if (Objects.nonNull(user)) {
                this.deptId = user.deptId;
                this.count = user.count;
            }
        }
    }

3、用户聚合类VO

本类属于核心代码

public class UserAggrVo extends UserAggr {
        private String deptName;
        /**
         * 很重要构造器
         *
         * @param user
         */
        public UserAggrVo(UserAggr user) {
            super(user);
        }
    }

4、服务层调用

服务层完成调用

public List<UserAggrVo> selectList3() {
        LambdaQueryWrapper<UserAggr> wrapper = Wrappers.lambdaQuery(UserAggr.class)
                .select(UserAggr::getDeptId, UserAggr::getCount)
                .groupBy(UserAggr::getDeptId);
        List<UserAggr> userAggrList = userAggrMapper.selectList(wrapper);
        List<UserAggrVo> userAggrVoList = EntityUtils.toList(userAggrList, UserAggrVo::new);
        // 给deptName完成属性赋值
        Set<Long> deptIds = EntityUtils.toSet(userAggrVoList, UserAggrVo::getDeptId);
        if (deptIds.size() > 0) {
            List<Dept> deptList = deptMapper.selectList(Wrappers.lambdaQuery(Dept.class)
                                                        .in(Dept::getDeptId, deptIds));
            Map<Long, String> map = EntityUtils.toMap(deptList, Dept::getDeptId, Dept::getDeptName);
            for (UserAggrVo userAggrVo : userAggrVoList) {
                userAggrVo.setDeptName(map.get(userAggrVo.getDeptId()));
            }
        }
        return userAggrVoList;
    }

5、效果展示

{
      "code": 200,
      "msg": "操作成功",
      "data": [
        {
          "deptId": "10",
          "count": "1",
          "deptName": "Java"
        },
        {
          "deptId": "11",
          "count": "2",
          "deptName": "Mysql"
        },
        {
          "deptId": "12",
          "count": "3",
          "deptName": "Tomcat"
        }
      ]
    }

#### 三、总结 ####

本方案很好的实现了MybatisPlus基于Lambda表达式实现聚合操作，代码在形式上与单表保持统一，代码优雅。各位看官朋友点赞支持一波。

[Link 1]: https://www.bilibili.com/video/BV1Pe4y117uu