数据库 非关系型数据库 MongoTemplate实现mongodb聚合管道查询

项目中使用mongodb也有5个多月了，总结一下MongoTemplate的聚合查询

以项目中的一个走访表作为例子

在对应项目中引入对应版本的spring-boot-data-mongodb依赖

org.springframework.boot

spring-boot-starter-data-mongodb

然后yml配置文件中配置好mongodb的连接信息就可以了

spring:

data:

mongodb:

uri: mongodb://账号:密码@ip:port/库名

logging:

level:

org.springframework.data.mongodb.core: debug

 monggodb集合对应的实体类：

package com.dcboot.module.visit.mongodb.entity;

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;

import io.swagger.annotations.ApiModel;

import io.swagger.annotations.ApiModelProperty;

import lombok.Data;

import org.springframework.data.annotation.Id;

import org.springframework.data.mongodb.core.mapping.Document;

import java.util.List;

/**

* @author xiaomifeng1010

* @version 1.0

* @date: 2022/7/16 15:47

* @Description

*/

@Document("visitObjectDeatil")

@ApiModel(value = "走访对象详情信息")

@Data

@JsonIgnoreProperties(ignoreUnknown = true)

public class VisitObjectDeatil {

private Long taskObjectId;

@Id

private Long id;

/**

* 走访状态（只有走访中和已走访两种状态了）

*/

private Integer visitStatus;

/**

* 属地镇街(顺德区的10个镇街)

* {@link com.dcboot.module.common.enums.task.ShunDeDistrictEnum}

*/

@ApiModelProperty(name = "townOrStreet",value = "属地镇街(顺德区的10个镇街)")

private String townOrStreet;

/**

* 走访主题

* {@link com.dcboot.module.common.enums.task.TaskSubjectEnum}

*/

@ApiModelProperty(name = "visitSubject",value = "走访主题")

private Integer visitSubject;

/**

* 走访对象

*/

@ApiModelProperty(name = "visitObject",value = "走访对象(6种类别)")

private VisitObject visitObject;

/**

* 走访详情说明

*/

@ApiModelProperty(name = "visitDeatilDescription",value = "走访详情说明")

private String visitDeatilDescription;

/**

* 走访附件信息列表

*/

@ApiModelProperty(name = "attachmentList",value = "走访附件")

private List attachmentList;

/**

* 走访现场图片

*/

@ApiModelProperty(name = "visitPictureList",value = "走访现场图片")

private List visitPictureList;

/**

* 企业联系人名片

*/

@ApiModelProperty(name = "enterpriseContactPersonBusinessCardList",value = "企业联系人名片")

private List enterpriseContactPersonBusinessCardList;

// private List visitThePersonOfEnterpriseList;

@ApiModelProperty(name = "visitEnterpriseBaseInfo",value = "走访企业基本信息")

private VisitEnterpriseBaseInfo visitEnterpriseBaseInfo;

/**

* 财务指标

*/

@ApiModelProperty(name = "financeIndexList",value = "财务指标")

private List financeIndexList;

/**

* 企业规模

*/

@ApiModelProperty(name = "enterpriseScale",value = "企业规模")

private EnterpriseScale enterpriseScale;

/**

* 企业营销情况

*/

@ApiModelProperty(name = "enterpriseMarktingCondition",value = "企业营销情况")

private EnterpriseMarktingCondition enterpriseMarktingCondition;

/**

* 经营主要指标

*/

@ApiModelProperty(name = "mainOperatingIndicators",value = "经营主要指标")

private MainOperatingIndicators mainOperatingIndicators;

/**

* 企业融资情况和意愿

*/

@ApiModelProperty(name = "enterpriseFinancingAndWilling",value = "企业融资意愿")

private EnterpriseFinancingAndWilling enterpriseFinancingAndWilling;

/**

* 企业专利著作情况

*/

@ApiModelProperty(name = "enterprisePatentAndBook",value = "企业专利著作情况")

private EnterprisePatentAndBook enterprisePatentAndBook;

/**

* 企业陪同走访人

*/

@ApiModelProperty(name = "companionPerson",value = "企业陪同走访人")

private String companionPerson;

/**

* 企业标签

*/

@ApiModelProperty(name = "enterpriseLabel",value = "企业标签")

private String enterpriseLabel;

/**

* 走访形式

*/

@ApiModelProperty(name = "visitMode",value = "走访形式")

private Integer visitMode;

/**

* 是否自主走访

*/

private Integer isSelfVisit;

/**

* 走访对象类型 分为：企业主体和非企业主体 1 企业主体 0 非企业主体

*/

@ApiModelProperty(name = "visitObjectType",value = "走访对象类型 分为：企业主体和非企业主体 1 企业主体 0 非企业主体")

private Integer visitObjectType;

/**

* 走访人

*/

@ApiModelProperty(name = "visitPersonId",value = "走访人id")

private Long visitPersonId;

private String visitPerson;

/**

* 走访人所在科室

*/

@ApiModelProperty(name = "visitDepartmentId",value = "走访人所在科室id")

private Long visitDepartmentId;

private String visitDepartment;

@ApiModelProperty(name = "visitTime",value = "走访日期")

private String visitTime;

private String createTime;

private String updateTime;

private String createBy;

private String updateBy;

}

内嵌对象

package com.dcboot.module.visit.mongodb.entity;

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;

import io.swagger.annotations.ApiModel;

import io.swagger.annotations.ApiModelProperty;

import lombok.Data;

/**

* @author xiaomifeng1010

* @version 1.0

* @date: 2022/7/18 11:11

* @Description

*/

@Data

@ApiModel(value = "走访对象")

@JsonIgnoreProperties(ignoreUnknown = true)

public class VisitObject {

private String visitObjectId;

/**

* 走访对象类型

* {@link com.dcboot.module.common.enums.task.VisitObjectTypeEnum}

*/

@ApiModelProperty(name = "type",value = "走访对象类型")

private Integer type;

@ApiModelProperty(name = "typeName",value = "走访对象类型名称")

private String typeName;

@ApiModelProperty(name = "visitObjectName",value = "走访对象名称")

private String visitObjectName;

}

内部数组对象:

package com.dcboot.module.visit.mongodb.entity;

import com.fasterxml.jackson.annotation.JsonIgnoreProperties;

import io.swagger.annotations.ApiModel;

import io.swagger.annotations.ApiModelProperty;

import lombok.Data;

import java.math.BigDecimal;

/**

* @author xiaomifeng1010

* @version 1.0

* @date: 2022/7/16 18:47

* @Description 财务指标

*/

@Data

@ApiModel(value = "财务指标")

@JsonIgnoreProperties(ignoreUnknown = true)

public class FinanceIndex {

/**

* 年份

*/

private String year;

/**

* 营业总收入

*/

@ApiModelProperty(name = "income", value = "营业总收入")

private BigDecimal income;

/**

* 利润总额

*/

@ApiModelProperty(name = "profit", value = "利润总额")

private BigDecimal profit;

/**

* 纳税总额

*/

@ApiModelProperty(name = "amountOfTax", value = "纳税总额")

private BigDecimal amountOfTax;

/**

* 资产总额

*/

@ApiModelProperty(name = "totalAmountOfAssets", value = "资产总额")

private BigDecimal totalAmountOfAssets;

/**

* 负债总额

*/

@ApiModelProperty(name = "totalAmountOfLiabilities", value = "负债总额")

private BigDecimal totalAmountOfLiabilities;

/**

* 流动比率

*/

@ApiModelProperty(name = "currentRatio", value = "流动比率")

private BigDecimal currentRatio;

/**

* 速动比率

*/

@ApiModelProperty(name = "quickRatio", value = "速动比率")

private BigDecimal quickRatio;

/**

* 资产负债率

*/

@ApiModelProperty(name = "assetsLiabilityRatio", value = "资产负债率")

private Double assetsLiabilityRatio;

/**

* 净资产收益率

*/

@ApiModelProperty(name = "netAssetsYieldsRatio",value = "净资产收益率")

private BigDecimal netAssetsYieldsRatio;

/**

* 资产回报率

*/

@ApiModelProperty(name = "assetsReturnRatio",value = "资产回报率")

private BigDecimal assetsReturnRatio;

/**

* 毛利率

*/

@ApiModelProperty(name = "profitRatio",value = "毛利率")

private BigDecimal profitRatio;

/**

* 企业所得税实际纳税额

*/

@ApiModelProperty(name = "actualTaxAmountOfEnterpriseIncomeTax",value = "企业所得税实际纳税额")

private BigDecimal actualTaxAmountOfEnterpriseIncomeTax;

/**

* 经营活动产生的现金流量净额

*/

@ApiModelProperty(name = "netCashFlowFromOperatingActivities",value = "经营活动产生的现金流量净额")

private BigDecimal netCashFlowFromOperatingActivities;

/**

* 研发投入

*/

@ApiModelProperty(name = "researchInvestment",value = "研发投入")

private BigDecimal researchInvestment;

/**

* 文件列表

*/

// private List attachmentList;

}

查询例子1

package com.dcboot.module.visit.task.service.impl;

import com.dcboot.module.visit.mongodb.entity.VisitObjectDeatil;

import com.dcboot.module.visit.task.common.PageListResponse;

import com.dcboot.module.visit.task.service.PortalVisitService;

import com.dcboot.module.visit.task.vo.SimpleVisitRecordVO;

import lombok.Setter;

import org.springframework.beans.factory.annotation.Autowired;

import org.springframework.data.domain.Sort;

import org.springframework.data.mongodb.core.MongoTemplate;

import org.springframework.data.mongodb.core.aggregation.Aggregation;

import org.springframework.data.mongodb.core.aggregation.AggregationResults;

import org.springframework.data.mongodb.core.aggregation.TypedAggregation;

import org.springframework.data.mongodb.core.query.Criteria;

import org.springframework.data.mongodb.core.query.Query;

import org.springframework.stereotype.Service;

import java.util.List;

/**

* @author xiaomifeng1010

* @version 1.0

* @date: 2022/7/15 11:46

* @Description

*/

@Service

@Setter(onMethod_=@Autowired)

public class PortalVisitServiceImpl implements PortalVisitService {

private MongoTemplate mongoTemplate;

@Override

public PageListResponse getVisitRecords(Long page,Integer size,String enterpriseName) {

Criteria criteria = Criteria.where("visitObject.visitObjectName").is(enterpriseName);

Query query = Query.query(criteria);

TypedAggregation visitObjectDeatilTypedAggregation = Aggregation.newAggregation(

VisitObjectDeatil.class,

// 相当于select

Aggregation.project()

.and("visitPerson").as("userName")

.and("visitObject.visitObjectName").as("enterpriseName")

.and("visitTime").as("visitTime").andExclude("_id")

.and("createTime").as("createTime"),

// 查询条件，相当于where

Aggregation.match(Criteria.where("enterpriseName").is(enterpriseName)),

Aggregation.sort(Sort.Direction.DESC, "createTime"),

Aggregation.skip((page - 1) * size),

Aggregation.limit(size)

);

AggregationResults mapAggregationResults = mongoTemplate.aggregate(visitObjectDeatilTypedAggregation, SimpleVisitRecordVO.class);

List mappedResults = mapAggregationResults.getMappedResults();

mappedResults.forEach(System.out ::println);

long totalCount = mongoTemplate.count(query, VisitObjectDeatil.class);

PageListResponse pageListResponse = new PageListResponse<>();

pageListResponse.setRecordList(mappedResults);

pageListResponse.setTotalCount(totalCount);

return pageListResponse;

}

}

,这是一个分页查询，需要分页查询出结果，并且还要查询出总条数，提供给前端，所以只用一个聚合管道查询不够，聚合管道查询出分页的数据，然后还有一个查询查满足查询条件的总条数，然后封装返回给前端

mongodb聚合查询分页查出来的数据，对应的VO实体类：

package com.dcboot.module.visit.task.vo;

import lombok.Data;

import lombok.NoArgsConstructor;

/**

* @author xiaomifeng1010

* @version 1.0

* @date: 2022/7/19 20:18

* @Description

*/

@NoArgsConstructor

@Data

public class SimpleVisitRecordVO {

private String visitTime;

private String userName;

private String enterpriseName;

}

还有一个mongodb集合collection是同事创建的，他创建的实体类是这样的：

package com.dcboot.module.manage.vo.req;

import com.baomidou.mybatisplus.annotation.TableField;

import com.fasterxml.jackson.annotation.JsonInclude;

import io.swagger.annotations.ApiModel;

import io.swagger.annotations.ApiModelProperty;

import lombok.Data;

import lombok.experimental.Accessors;

import org.springframework.data.annotation.CreatedDate;

import org.springframework.data.annotation.Id;

import org.springframework.data.annotation.LastModifiedDate;

import org.springframework.data.mongodb.core.index.Indexed;

import org.springframework.data.mongodb.core.mapping.Document;

import java.io.Serializable;

import java.util.Date;

import java.util.List;

/**

* @description: ScienceProjectParams

* @date: 2022/4/25 16:32

* @author: HCF

* @version: 1.0

*/

@Document(collection = "scienceProjectParams")

@ApiModel(value = "科技项目参数")

@Data

@Accessors(chain = true)

@JsonInclude(JsonInclude.Include.NON_NULL)

public class ScienceProjectParams implements Serializable {

@ApiModelProperty(value = "_id")

private String _id;

@ApiModelProperty(value = "数据")

private Object data;

@ApiModelProperty(value = "类别")

private String category;

@ApiModelProperty(value = "备注")

private List