SpringBoot 2.X MongoTemplate 分组聚合查询一网打尽

最新推荐文章于 2024-05-24 11:15:53 发布

Flying9001

最新推荐文章于 2024-05-24 11:15:53 发布

阅读量5.4k

点赞数 1

分类专栏： Java 文章标签： spring boot 后端 java mongodb nosql

本文链接：https://blog.csdn.net/Mrqiang9001/article/details/121424300

版权

Java 专栏收录该内容

119 篇文章 12 订阅

订阅专栏

文章目录

1 摘要

MongoDb 如何实现聚合查询？分组查询？分组分页查询？自定义时间区间查询？时间格式转换查询？不要慌，本文将介绍基于 SpringBoot 2.X MongoTemplate 实现各种维度的分组聚合查询，将 MongoDB 的复杂查询难点一网打尽。

2 按照固定字段分组查询

./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/service/impl/BlogServiceImpl.java

    /**
     * 按照作者分组查询博客数据
     *
     * @param queryGroupByAuthorParam
     * @return
     */
    @Override
    public ApiResult queryGroupByAuthor(BlogQueryGroupByAuthorParam queryGroupByAuthorParam) {
        // 查询条件
        Criteria criteria = new Criteria();
        if (StrUtil.isNotBlank(queryGroupByAuthorParam.getAuthor())) {
            criteria.and("author").is(queryGroupByAuthorParam.getAuthor());
        }
        MatchOperation matchOperation = Aggregation.match(criteria);
        // 查询字段
        ProjectionOperation projectionOperation = Aggregation.project("id", "author", "countRead", "countLike");
        // 分组统计
        GroupOperation groupOperation = Aggregation.group("author")
                .first("author").as("author")
                .count().as("totalBlog")
                .sum("countRead").as("totalRead")
                .avg("countRead").as("aveRead")
                .sum("countLike").as("totalLike")
                .avg("countLike").as("aveLike");
        // 查询结果
        AggregationResults<BlogSummaryVo> results = mongoTemplate.aggregate(Aggregation.newAggregation(
                BlogEntity.class,matchOperation, projectionOperation, groupOperation), BlogSummaryVo.class);
        log.info("查询结果: {}", results.getMappedResults());
        return ApiResult.success(results.getMappedResults());
    }

注意事项:

分组的字段必须要在 ProjectionOperation 中

3 分组分页查询

./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/service/impl/BlogServiceImpl.java

    /**
     * 按照作者分组分页查询你博客数据
     *
     * @param queryGroupByAuthorPageParam
     * @return
     */
    @Override
    public ApiResult queryGroupByAuthorPage(BlogQueryGroupByAuthorPageParam queryGroupByAuthorPageParam) {
        // 查询条件
        Criteria criteria = new Criteria();
        MatchOperation matchOperation = Aggregation.match(criteria);
        // 查询字段
        ProjectionOperation projectionOperation = Aggregation.project("id", "author","countRead", "countLike");
        // 分组统计
        GroupOperation groupOperation = Aggregation.group("author")
                .first("author").as("author")
                .count().as("totalBlog")
                .sum("countRead").as("totalRead")
                .avg("countRead").as("aveRead")
                .sum("countLike").as("totalLike")
                .avg("countLike").as("aveLike");
        // 查询总条数
        GroupOperation groupOperation2 = Aggregation.group().count().as("totalAuthor");
        AggregationResults<Map> totalAuthorResult = mongoTemplate.aggregate(Aggregation.newAggregation(BlogEntity.class,
                matchOperation, projectionOperation, groupOperation, groupOperation2), Map.class);
        int totalAuthor = (int) totalAuthorResult.getUniqueMappedResult().getOrDefault("totalAuthor", 0);
        // 设置分页信息
        SortOperation sortOperation = Aggregation.sort(Sort.by(Sort.Direction.DESC, "author"));
        SkipOperation skipOperation = Aggregation.skip((queryGroupByAuthorPageParam.getCurrentPage() - 1)
                * (long) queryGroupByAuthorPageParam.getPageSize());
        LimitOperation limitOperation = Aggregation.limit(queryGroupByAuthorPageParam.getPageSize());
        // 查询结果
        AggregationResults<BlogSummaryVo> results = mongoTemplate.aggregate(Aggregation.newAggregation(
                BlogEntity.class, matchOperation, projectionOperation, groupOperation, sortOperation,
                skipOperation, limitOperation), BlogSummaryVo.class);
        // 组装分页结果
        Page<BlogSummaryVo> page = PageableExecutionUtils.getPage(results.getMappedResults(),
                PageRequest.of(queryGroupByAuthorPageParam.getCurrentPage() -1,
                        queryGroupByAuthorPageParam.getPageSize()), () -> totalAuthor);
        return ApiResult.success(page);
    }

注意事项:

Spring 的分页类 org.springframework.data.domain.Pageable 是从 0 开始计算第一页的

4 按照日期分组查询(字段为精确到秒的时间戳)

./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/service/impl/BlogServiceImpl.java

    /**
     * 按照客户端时间分组查询博客数据
     *
     * @param queryGroupByClientTimestampParam
     * @return
     */
    @Override
    public ApiResult queryGroupByClientTimestamp(BlogQueryGroupByClientTimestampParam
                                                             queryGroupByClientTimestampParam) {
        // 获取日期分组信息
        BlogGroupDateVo groupDateVo = getGroupDate(queryGroupByClientTimestampParam.getDateType());
        // 查询条件
        Criteria criteria = Criteria.where("clientTimestamp").gte(queryGroupByClientTimestampParam
                        .getMinClientTimestamp()).lte(queryGroupByClientTimestampParam.getMaxClientTimestamp());
        MatchOperation matchOperation = Aggregation.match(criteria);
        // 查询字段
        ProjectionOperation projectionOperation = Aggregation.project("countRead","countLike","clientTimestamp")
                .and(DateOperators.DateToString.dateOf(aggregationOperationContext ->
                                new Document("$add", Arrays.asList(new Date(28800000),
                                        new Document("$multiply", Arrays.asList("$clientTimestamp", 1000)))))
                        .toString(groupDateVo.getDateFormat())).as(groupDateVo.getDateGroupField());
        // 分组统计
        GroupOperation groupOperation = Aggregation.group(groupDateVo.getDateGroupField())
                .first(groupDateVo.getDateGroupField()).as("date")
                .count().as("totalBlog")
                .sum("countRead").as("totalRead")
                .avg("countRead").as("aveRead")
                .sum("countLike").as("totalLike")
                .avg("countLike").as("aveLike");
        // 排序
        SortOperation sortOperation = Aggregation.sort(Sort.by(Sort.Direction.ASC, "date"));
        // 查询结果
        AggregationResults<BlogSummaryVo> results = mongoTemplate.aggregate(Aggregation.newAggregation(
                BlogEntity.class, matchOperation, projectionOperation, groupOperation, sortOperation),
                BlogSummaryVo.class);
        return ApiResult.success(results.getMappedResults());
    }

注意事项:

MongoDB 的时间是按照 UTC 的时区进行计算的，基本单位为毫秒，国内的时区相对于 UTC 时间要早 8小时，所以在计算时间的时候需要先乘以 1000，再加 8 小时。28800000 = 8 * 60 * 60 * 1000 (毫秒)

5 按照日期分组查询(字段为精确到毫秒的时间戳)

./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/service/impl/BlogServiceImpl.java

    /**
     * 按照创建时间分组查询博客数据
     *
     * @param queryGroupByCreateTimeParam
     * @return
     */
    @Override
    public ApiResult queryGroupByCreateTime(BlogQueryGroupByCreateTimeParam queryGroupByCreateTimeParam) {
        // 获取日期分组信息
        BlogGroupDateVo groupDateVo = getGroupDate(queryGroupByCreateTimeParam.getDateType());
        // 查询条件
        Criteria criteria = Criteria.where("createTime").gte(queryGroupByCreateTimeParam.getMinCreateTime())
                .lte(queryGroupByCreateTimeParam.getMaxCreateTime());
        MatchOperation matchOperation = Aggregation.match(criteria);
        // 查询字段
        ProjectionOperation projectionOperation = Aggregation.project("countRead", "countLike","createTime")
                .and(DateOperators.DateToString.dateOf(aggregationOperationContext ->
                                new Document("$add", Arrays.asList(new Date(28800000),"$createTime")))
                        .toString(groupDateVo.getDateFormat())).as(groupDateVo.getDateGroupField());
        // 分组统计
        GroupOperation groupOperation = Aggregation.group(groupDateVo.getDateGroupField())
                .first(groupDateVo.getDateGroupField()).as("date")
                .count().as("totalBlog")
                .sum("countRead").as("totalRead")
                .avg("countRead").as("aveRead")
                .sum("countLike").as("totalLike")
                .avg("countLike").as("aveLike");
        // 排序
        SortOperation sortOperation = Aggregation.sort(Sort.by(Sort.Direction.ASC, "date"));
        // 查询结果
        AggregationResults<BlogSummaryVo> results = mongoTemplate.aggregate(Aggregation.newAggregation(
                BlogEntity.class, matchOperation, projectionOperation, groupOperation, sortOperation),
                BlogSummaryVo.class);
        return ApiResult.success(results.getMappedResults());
    }

注意事项:

Spring 为 MongoDB 提供的日期转字符串的操作工具类为 org.springframework.data.mongodb.core.aggregation.DateOperators ,该类提供了多种维度的时间区间，包括: 分钟、小时、天、周、月、自定义日期格式等

6 按照日期分组查询(字段为Date)

./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/service/impl/BlogServiceImpl.java

    /**
     * 按照更新时间分组查询博客数据
     *
     * @param queryGroupByUpdateTimeParam
     * @return
     */
    @Override
    public ApiResult queryGroupByUpdateTime(BlogQueryGroupByUpdateTimeParam queryGroupByUpdateTimeParam) {
        // 查询条件
        Criteria criteria = Criteria.where("updateTime").gte(new Date(queryGroupByUpdateTimeParam.getMinUpdateTime()))
                .lte(new Date(queryGroupByUpdateTimeParam.getMaxUpdateTime()));
        MatchOperation matchOperation = Aggregation.match(criteria);
        // 查询字段
        ProjectionOperation projectionOperation = Aggregation.project("countRead", "countLike", "updateTime")
                .and(DateOperators.DayOfMonth.dayOfMonth("updateTime")).as("day")
                .and(DateOperators.Month.monthOf("updateTime")).as("month")
                .and(DateOperators.Year.yearOf("updateTime")).as("year");
        // 分组统计
        GroupOperation groupOperation = Aggregation.group("year","month","day")
                .addToSet("year").as("year")
                .addToSet("month").as("month")
                .addToSet("day").as("date")
                .count().as("totalBlog")
                .sum("countRead").as("totalRead")
                .avg("countRead").as("aveRead")
                .sum("countLike").as("totalLike")
                .avg("countLike").as("aveLike");
        // 排序
        SortOperation sortOperation = Aggregation.sort(Sort.by(Sort.Direction.ASC, "_id.year",
                        "_id.month", "_id.day"));
        // 查询结果
        AggregationResults<BlogSummaryDiyVo> results = mongoTemplate.aggregate(Aggregation.newAggregation(
                        BlogEntity.class, matchOperation, projectionOperation, groupOperation, sortOperation),
                BlogSummaryDiyVo.class);
        return ApiResult.success(results.getMappedResults());
    }

注意事项:

如果数据库字段为 java.util.Date，则可以直接从字段中获取到日期信息，不需要再进行转换
多个字段排序，则需要按照 _id.field 格式书写，否则可能报错；排序优先级是按照字段从左到右的顺序，进行排序

7 按照自定义时间区间分组查询

常用的时间分组区间为小时、天、月，Spring 支持的时间区间已经足够应对，但是针对特定场景，需要自定义时间区间的时候，Spring 自带的时间区间就不够用了。作者也是查遍全网才找到解决方案，赶紧把它记下来，以免下次忘记

./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/service/impl/BlogServiceImpl.java

    /**
     * 按照创建时间自定义区间分组查询博客数据
     *
     * @param queryGroupByCreateTimeDiyParam
     * @return
     */
    @Override
    public ApiResult queryGroupByCreateTimeDiy(BlogQueryGroupByCreateTimeDiyParam queryGroupByCreateTimeDiyParam) {
        // 查询条件
        Criteria criteria = Criteria.where("createTime").gte(queryGroupByCreateTimeDiyParam.getMinCreateTime())
                .lte(queryGroupByCreateTimeDiyParam.getMaxCreateTime());
        MatchOperation matchOperation = Aggregation.match(criteria);
        // 查询字段
        ProjectionOperation projectionOperation = Aggregation.project("countRead", "countLike","createTime")
                .and(DateOperators.Minute.minuteOf(aggregationOperationContext ->
                                new Document("$add", Arrays.asList(new Date(28800000),"$createTime"))))
                        .as("minute")
                .and(DateOperators.Hour.hourOf(aggregationOperationContext ->
                        new Document("$add", Arrays.asList(new Date(28800000),"$createTime"))))
                .as("hour")
                .and(DateOperators.DateToString.dateOf(aggregationOperationContext ->
                                new Document("$add", Arrays.asList(new Date(28800000),"$createTime")))
                        .toString("%Y-%m-%d")).as("day");
        ProjectionOperation projectionOperation2 = Aggregation.project("countRead", "countLike",
                "createTime", "minute", "hour","day")
                .andExpression("minute - minute % 30").as("halfHour");
        // 分组统计
        GroupOperation groupOperation = Aggregation.group("day","hour","halfHour")
                .addToSet("hour").as("hour")
                .addToSet("day").as("date")
                .addToSet("halfHour").as("minute")
                .count().as("totalBlog")
                .sum("countRead").as("totalRead")
                .avg("countRead").as("aveRead")
                .sum("countLike").as("totalLike")
                .avg("countLike").as("aveLike");
        // 排序
        SortOperation sortOperation = Aggregation.sort(Sort.by(Sort.Direction.ASC, "day", "hour", "halfHour"));
        // 查询结果
        AggregationResults<BlogSummaryDiyVo> results = mongoTemplate.aggregate(Aggregation.newAggregation(
                        BlogEntity.class, matchOperation, projectionOperation, projectionOperation2,
                        groupOperation, sortOperation), BlogSummaryDiyVo.class);
        return ApiResult.success(results.getMappedResults());
    }

注意事项:

在第二个 ProjectionOperation 中必须包含上一个的所有字段
本示例中的时间区间为 30 分钟

8 数据库实体类及其他相关类

8.1 数据库实体类

./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/model/entity/BaseEntity.java

package com.ljq.demo.springboot.mongodb.model.entity;

import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import org.springframework.data.annotation.CreatedDate;
import org.springframework.data.annotation.Id;
import org.springframework.data.annotation.LastModifiedDate;
import org.springframework.data.mongodb.core.mapping.Field;

import java.io.Serializable;
import java.util.Date;

/**
 * @Description: 基础实体类
 * @Author: junqiang.lu
 * @Date: 2021/9/24
 */
@Data
public class BaseEntity implements Serializable {

    private static final long serialVersionUID = -3003658740476069858L;

    /**
     * id,主键
     */
    @Id
    @ApiModelProperty(value = "id,主键", name = "id")
    private String id;
    /**
     * 创建时间
     */
    @Field
    @CreatedDate
    @ApiModelProperty(value = "创建时间", name = "createTime")
    private Long createTime;
    /**
     * 修改时间
     */
    @Field
    @LastModifiedDate
    @ApiModelProperty(value = "修改时间", name = "updateTime")
    private Date updateTime;
}

./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/model/entity/BlogEntity.java

package com.ljq.demo.springboot.mongodb.model.entity;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;
import lombok.ToString;
import org.springframework.data.mongodb.core.mapping.Document;
import org.springframework.data.mongodb.core.mapping.Field;

/**
 * @Description: 博客信息
 * @Author: junqiang.lu
 * @Date: 2021/11/13
 */
@Data
@ToString(callSuper = true)
@Document(value = "blog")
@ApiModel(value = "博客信息", description = "博客信息")
public class BlogEntity extends BaseEntity {

    private static final long serialVersionUID = -2124422309475024490L;

    /**
     * 标题
     */
    @Field
    @ApiModelProperty(value = "标题", name = "title")
    private String title;
    /**
     * 作者
     */
    @Field
    @ApiModelProperty(value = "作者", name = "author")
    private String author;
    /**
     * 内容
     */
    @Field
    @ApiModelProperty(value = "内容", name = "content")
    private String content;
    /**
     * 阅读数量
     */
    @Field
    @ApiModelProperty(value = "阅读数量", name = "countRead")
    private Integer countRead;
    /**
     * 点赞数量
     */
    @Field
    @ApiModelProperty(value = "点赞数量", name = "countLike")
    private Integer countLike;
    /**
     * 客户端时间戳(精确到秒)
     */
    @Field
    @ApiModelProperty(value = "客户端时间戳(精确到秒)", name = "clientTimestamp")
    private Integer clientTimestamp;



}

8.2 统计结果类

./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/model/vo/BlogSummaryVo.java

package com.ljq.demo.springboot.mongodb.model.vo;

import com.fasterxml.jackson.annotation.JsonInclude;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;

/**
 * @Description: 博客统计结果
 * @Author: junqiang.lu
 * @Date: 2021/11/15
 */
@Data
@JsonInclude(JsonInclude.Include.NON_NULL)
@ApiModel(value = "博客统计结果", description = "博客统计结果")
public class BlogSummaryVo implements Serializable {

    private static final long serialVersionUID = 6350918498981106620L;

    /**
     * 作者
     */
    @ApiModelProperty(value = "作者", name = "author")
    private String author;
    /**
     * 总博客数量
     */
    @ApiModelProperty(value = "总博客数量", name = "totalBlog")
    private Long totalBlog;
    /**
     * 总阅读量
     */
    @ApiModelProperty(value = "总阅读量", name = "totalRead")
    private Long totalRead;
    /**
     * 平均阅读量
     */
    @ApiModelProperty(value = "平均阅读量", name = "aveRead")
    private Long aveRead;
    /**
     * 总点赞数量
     */
    @ApiModelProperty(value = "总点赞数量", name = "totalLike")
    private Long totalLike;
    /**
     * 平均点赞数量
     */
    @ApiModelProperty(value = "平均点赞数量", name = "aveLike")
    private Long aveLike;
    /**
     * 日期
     */
    @ApiModelProperty(value = "日期", name = "date")
    private String date;


}

./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/model/vo/BlogSummaryDiyVo.java

package com.ljq.demo.springboot.mongodb.model.vo;

import com.fasterxml.jackson.annotation.JsonInclude;
import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

/**
 * @Description: 博客自定义时间区间统计结果
 * @Author: junqiang.lu
 * @Date: 2021/11/18
 */
@Data
@JsonInclude(JsonInclude.Include.NON_NULL)
@ApiModel(value = "博客自定义时间区间统计结果", description = "博客自定义时间区间统计结果")
public class BlogSummaryDiyVo extends BlogSummaryVo {

    private static final long serialVersionUID = 6688850910361408341L;

    /**
     * 年
     */
    @ApiModelProperty(value = "年", name = "year")
    private String year;
    /**
     * 月
     */
    @ApiModelProperty(value = "月", name = "month")
    private String month;
    /**
     * 小时
     */
    @ApiModelProperty(value = "小时", name = "day")
    private String hour;
    /**
     * 分钟
     */
    @ApiModelProperty(value = "分钟", name = "minute")
    private String minute;

}

8.3 日期分组信息类

./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/model/vo/BlogGroupDateVo.java

package com.ljq.demo.springboot.mongodb.model.vo;

import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;
import lombok.Data;

import java.io.Serializable;

/**
 * @Description: 博客日期分组信息
 * @Author: junqiang.lu
 * @Date: 2021/11/19
 */
@Data
@ApiModel(value = "博客日期分组信息", description = "博客日期分组信息")
public class BlogGroupDateVo implements Serializable {

    private static final long serialVersionUID = -4828325421504898453L;

    /**
     * 日期分组字段
     */
    @ApiModelProperty(value = "日期分组字段", name = "dateGroupField")
    private String dateGroupField;
    /**
     * 日期格式
     */
    @ApiModelProperty(value = "日期格式", name = "dateFormat")
    private String dateFormat;



}

8.4 获取日期分组信息的方法

./demo-mongodb/src/main/java/com/ljq/demo/springboot/mongodb/service/impl/BlogServiceImpl.java

    /**
     * 获取博客日期分组信息
     *
     * @param dateType
     * @return
     */
    private BlogGroupDateVo getGroupDate(Integer dateType) {
        BlogGroupDateVo groupDateVo = new BlogGroupDateVo();
        switch (dateType) {
            case BlogConst.DATE_TYPE_DAY:
                groupDateVo.setDateGroupField("day");
                groupDateVo.setDateFormat("%Y-%m-%d");
                break;
            case BlogConst.DATE_TYPE_MONTH:
                groupDateVo.setDateGroupField("month");
                groupDateVo.setDateFormat("%Y-%m");
                break;
            default:
                groupDateVo.setDateGroupField("day");
                groupDateVo.setDateFormat("%Y-%m-%d");
                break;
        }
        return groupDateVo;
    }

9 推荐参考资料

使用mongoTemplate进行Aggregation聚合查询

springboot使用MongoTemplate分组统计数据

SpringBoot之MongoTemplate的查询可以怎么耍

java 使用mongoTemplate 按月分组、聚合的实现（要求返回非分组字段）_deelness的博客-程序员宅基地

Group by time interval spring-data-mongo

关于MongoDB时间格式转换和时间段聚合统计的用法总结

spring mongodb 模糊查询

Mongodb系列- spring-data-mongodb使用MongoTemplate实现分页查询

记一次使用mongoTemplate的Aggregation类进行分组，分页操作

mongodb高级聚合查询

mongoDB对时间的处理ISODate与咱们时区相差8小时

5 Github 源码

Gtihub 源码地址 : https://github.com/Flying9001/springBootDemo

个人公众号:404Code,分享半个互联网人的技术与思考，感兴趣的可以关注.
404Code

Flying9001

关注

1
点赞
踩
29

收藏

觉得还不错? 一键收藏
3
评论
SpringBoot 2.X MongoTemplate 分组聚合查询一网打尽

文章目录1 摘要2 按照固定字段分组查询3 分组分页查询4 按照日期分组查询(字段为精确到秒的时间戳)5 按照日期分组查询(字段为精确到毫秒的时间戳)6 按照日期分组查询(字段为Date)7 按照自定义时间区间分组查询8 数据库实体类及其他相关类8.1 数据库实体类8.2 统计结果类8.3 日期分组信息类8.4 获取日期分组信息的方法9 推荐参考资料10 本次提交记录1 摘要MongoDb 如何实现聚合查询？分组查询？分组分页查询？自定义时间区间查询？时间格式转换查询？不要慌，本文将介绍基于 S
复制链接

扫一扫