项目中使用mongodb也有5个多月了,总结一下MongoTemplate的聚合查询(使用练习实例演示)
在对应项目中引入对应版本的spring-boot-data-mongodb依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-mongodb</artifactId>
</dependency>
然后yml配置文件中配置好mongodb的连接信息就可以了
spring:
data:
mongodb:
uri: mongodb://账号:密码@ip:port/库名
logging:
level:
org.springframework.data.mongodb.core: debug
monggodb集合对应的实体类:
package com.xiaomifeng1010.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:
* @Description
*/
@Document("businessDetail")
@ApiModel(value = "xx详情信息")
@Data
@JsonIgnoreProperties(ignoreUnknown = true)
public class BusinessDetail {
private Long businessTaskId;
@Id
private Long id;
private Integer businessStatus;
@ApiModelProperty(name = "businessDetailDescription",value = "xx详情说明")
private String businessDetailDescription;
@ApiModelProperty(name = "attachmentList",value = "业务附件")
private List<Attachment> attachmentList;
@ApiModelProperty(name = "pictureList",value = "活动图片")
private List<Attachment> pictureList;
@ApiModelProperty(name = "enterpriseBaseInfo",value = "企业基本信息")
private EnterpriseBaseInfo enterpriseBaseInfo;
private String executor;
* 企业标签
*/
@ApiModelProperty(name = "enterpriseLabel",value = "企业标签")
private String enterpriseLabel;
@ApiModelProperty(name = "produceTime", value = "产生日期")
private String produceTime;
private String createTime;
private String updateTime;
private String createBy;
private String updateBy;
}
内嵌对象
package com.xiaomifeng1010.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:
* @Description 企业基本信息
*/
@Data
@ApiModel(value = "企业基本信息")
@JsonIgnoreProperties(ignoreUnknown = true)
public class EnterpriseBaseInfo {
private String enterpriseName;
@ApiModelProperty(name = "unifiedSocialCreditCode", value = "统一社会信用代码")
private String unifiedSocialCreditCode;
@ApiModelProperty(name = "industry",value = "所属行业")
private Integer industry;
@ApiModelProperty(name = "legalRepresentative",value = "法定代表人")
private String legalRepresentative;
@ApiModelProperty(name = "registryAddress",value = "注册地")
private String registryAddress;
}
内部数组对象:
package com.xiaomifeng1010..mongodb.entity;
import lombok.Data;
/**
* @author xiaomifeng1010
* @version 1.0
* @date:
* @Description
*/
@Data
public class Attachment {
/**
* minio文件id
*/
private Long fileId;
private String fileUrl;
private String originalFileName;
}
查询例子1
package com.xiaomifeng1010.task.service.impl;
import com.xiaomifeng1010.task.common.PageListResponse;
import com.xiaomifeng1010.task.service.PortalService;
import com.xiaomifeng1010.task.vo.SimpleRecordVO;
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:
* @Description
*/
@Service
@Setter(onMethod_=@Autowired)
public class PortalServiceImpl implements PortalService {
private MongoTemplate mongoTemplate;
@Override
public PageListResponse<SimpleRecordVO> getRecords(Long page,Integer size,String enterpriseName) {
Criteria criteria = Criteria.where("enterpriseBaseInfo.enterpriseName").is(enterpriseName);
Query query = Query.query(criteria);
TypedAggregation<BusinessDetail> businessDetailTypedAggregation = Aggregation.newAggregation(
BusinessDetail.class,
// 相当于select
Aggregation.project()
.and("executor").as("userName")
.and("enterpriseBaseInfo.enterpriseName").as("enterpriseName")
.and("produceTime").as("produceTime").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<SimpleRecordVO> mapAggregationResults = mongoTemplate.aggregate(businessDetailTypedAggregation , SimpleRecordVO.class);
List<SimpleRecordVO> mappedResults = mapAggregationResults.getMappedResults();
mappedResults.forEach(System.out ::println);
long totalCount = mongoTemplate.count(query, BusinessDetail.class);
PageListResponse<SimpleRecordVO> pageListResponse = new PageListResponse<>();
pageListResponse.setRecordList(mappedResults);
pageListResponse.setTotalCount(totalCount);
return pageListResponse;
}
}
,这是一个分页查询,需要分页查询出结果,并且还要查询出总条数,提供给前端,所以只用一个聚合管道查询不够,聚合管道查询出分页的数据,然后还有一个查询查满足查询条件的总条数,然后封装返回给前端
mongodb聚合查询分页查出来的数据,对应的VO实体类:
package com.xiaomifeng1010.task.vo;
import lombok.Data;
import lombok.NoArgsConstructor;
/**
* @author xiaomifeng1010
* @version 1.0
* @date:
* @Description
*/
@NoArgsConstructor
@Data
public class SimpleRecordVO {
private String produceTime;
private String userName;
private String enterpriseName;
}
还有一种情况,mongodb集合collection是这样创建的,实体类是这样的:
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:
* @date:
* @author:
* @version: 1.0
*/
@Document(collection = "otherCondition")
@ApiModel(value = "其他示例")
@Data
@Accessors(chain = true)
@JsonInclude(JsonInclude.Include.NON_NULL)
public class OtherCondition implements Serializable {
@ApiModelProperty(value = "_id")
private String _id;
@ApiModelProperty(value = "数据")
private Object data;
@ApiModelProperty(value = "类别")
private String category;
@ApiModelProperty(value = "备注")
private List<Object> remarks;
@ApiModelProperty(value = "创建时间")
private String createTime;
@ApiModelProperty(value = "修改时间")
private String updateTime;
}
那么在使用聚合管道查询时:
int thisYear = DateUtil.thisYear();
String thisYearStr = String.valueOf(thisYear);
Aggregation aggregation = Aggregation.newAggregation(
Aggregation.project().and("data.enterpriseName").as("enterpriseName")
.and("data.unifiedSocialCreditCode").as("unifiedSocialCreditCode")
.andExclude("_id")
.and("createTime").as("createTimeStr")
.and("createTime").substring(5,2).as("month")
.and("createTime").substring(0,4).as("year")
.and("data.type").as("typeCode"),
Aggregation.match(Criteria.where("typeCode").is(typeCode).and("year").is(thisYearStr)),
Aggregation.sort(Sort.Direction.DESC, "createTimeStr")
);
AggregationResults<EnterpriseDetailForTypeBO> aggregate = mongoTemplate.aggregate(aggregation,mongoTemplate.getCollectionName(OtherCondition .class),EnterpriseDetailForTypeBO.class);
List<EnterpriseDetailForTypeBO> mappedResults = aggregate.getMappedResults();
说明,由于 OtherCondition 类中有个属性data是Object类型,所以不能使用TypedAggregation<OtherCondition >,需要使用不指定泛型类型的Aggregation 然后在使用mongoTemplate.aggregate方法时,使用 mongoTemplate.getCollectionName(OtherCondition .class),传入集合名;
如果使用TypedAggregation<OtherCondition >这种带泛型类型聚合api,会报异常,提示没有实体类可以转换成Object类(Couldn't find PersistentEntity for type java.lang.Object!)
比如之前的写法和异常出错
因为属性data是Object类型
出现异常提示
所以改成上边的写法就可以正常聚合查询了;
而上一个查询例子中,我自己创建的一个mongodb的collection对应的实体类可以使用TypedAggregation<BusinessDetail>是因为,我创建的实体类BusinessDetail的属性中没有Object类型
查询3
聚合中如果带有分组和求和,则查询出来的字段会只有分组的字段和求和的字段,其他字段是查询不出来的,而且查询出来的结构也是分组字段和求和字段是隔离开的
例如:
for (int i = 1; i <= 10 ; i++) {
String typeCode = EnterpriseTypeEnum.getTypeCode(i);
Aggregation aggregation = Aggregation.newAggregation(
Aggregation.project().and("data.enterpriseName").as("enterpriseName")
.and("data.unifiedSocialCreditCode").as("unifiedSocialCreditCode")
.andExclude("_id")
.and("createTime").as("createTime")
.and("createTime").substring(5,2).as("month")
.and("createTime").substring(0,4).as("year")
.and("data.type").as("typeCode")
.and("enterpriseNum"),
Aggregation.match(Criteria.where("typeCode").is(typeCode)),
Aggregation.sort(Sort.Direction.DESC, "createTime"),
Aggregation.group("month","year","typeCode").count().as("enterpriseNum")
);
AggregationResults<Map> aggregate = mongoTemplate.aggregate(aggregation,mongoTemplate.getCollectionName(OtherCondition.class),Map.class);
List<Map> mappedResults = aggregate.getMappedResults();
if (CollectionUtils.isNotEmpty(mappedResults)){
JSONArray jsonArray = JSONUtil.parseArray(mappedResults);
int size = jsonArray.size();
for (int j = 0; j < size; j++) {
JSONObject jsonObject = jsonArray.getJSONObject(j);
Integer enterpriseNum = jsonObject.getInt("enterpriseNum", NumberUtils.INTEGER_ZERO);
JSONObject groupInfo = jsonObject.getJSONObject("_id");
String month = groupInfo.getStr("month");
Integer monthNum=0;
if (NumberUtil.isInteger(month)){
monthNum = Integer.valueOf(month);
}
String year = groupInfo.getStr("year");
Integer yearNum=0;
if (NumberUtil.isInteger(year)) {
yearNum = Integer.valueOf(year);
}
groupInfo.getStr("typeCode");
EnterpriseTypeStatistics one = enterpriseTypeStatisticsService.getOne(Wrappers.<EnterpriseTypeStatistics>lambdaQuery()
.eq(EnterpriseTypeStatistics::getIsValid, 1)
.eq(EnterpriseTypeStatistics::getMonth, monthNum)
.eq(EnterpriseTypeStatistics::getType, i)
.eq(EnterpriseTypeStatistics::getYear, yearNum));
EnterpriseTypeStatistics enterpriseTypeStatistics = new EnterpriseTypeStatistics();
enterpriseTypeStatistics.setEnterpriseNum(enterpriseNum);
enterpriseTypeStatistics.setYear(yearNum);
enterpriseTypeStatistics.setMonth(monthNum);
enterpriseTypeStatistics.setTypeCode(typeCode);
enterpriseTypeStatistics.setType(i);
String typeName = EnterpriseTypeEnum.getTypeName(i);
enterpriseTypeStatistics.setIsValid(1);
enterpriseTypeStatistics.setTypeName(typeName);
if (Objects.isNull(one)){
enterpriseTypeStatistics.insert();
}else {
Long id = one.getId();
enterpriseTypeStatistics.setId(id);
boolean update = enterpriseTypeStatistics.updateById();
}
}
}
}
聚合查询出来的数据结构是这样的
[{
"_id": {
"month": "05",
"year": "2022",
"typeCode": "xxxcodetype"
},
"enterpriseNum": 4905
}, {
"_id": {
"month": "06",
"year": "2022",
"typeCode": "abctype"
},
"enterpriseNum": 2
}]
如果 Aggregation.group("month","year","typeCode").count().as("enterpriseNum")这个聚合操作,只是分组,没有计数count操作,则可以直接映射给一个实体类的形式
比如这个聚合查询中group操作之后,没有计数或者求和
/**
*
* @return
*/
public ApiResult twentyEightTypeDetailRecordTest(){
for (int i = 18; i <= 28; i++) {
List<EnterpriseTypeDetailRecord> enterpriseTypeDetailRecordList = Lists.newArrayList();
String typeCode = EnterpriseTypeEnum.getTypeCode(i);
String typeName = EnterpriseTypeEnum.getTypeName(i);
List<String> enterpriseTypeChildCodeList = enterpriseTypeDetailRecordService.getEnterpriseTypeChildCode(typeCode);
Aggregation aggregation = null;
if (Range.closed(18, 20).contains(i) || Range.closed(25, 28).contains(i)) {
aggregation = Aggregation.newAggregation(
Aggregation.project().and("data.list.enterpriseName").as("enterpriseName")
.and("data.list.unifiedSocialCreditCode").as("unifiedSocialCreditCode")
.andExclude("_id")
.and("createTime").substring(5, 2).as("month")
.and("createTime").substring(0, 4).as("year")
.and("data.type").as("typeCode"),
Aggregation.match(Criteria.where("typeCode").in(enterpriseTypeChildCodeList)),
Aggregation.group("month","year","enterpriseName","uscc").last("month")
.as("month").last("year").as("year").last("enterpriseName").as("enterpriseName")
.last("unifiedSocialCreditCode").as("unifiedSocialCreditCode")
);
}
AggregationResults<EnterpriseDetailForTypeBO> aggregate = mongoTemplate.aggregate(aggregation,mongoTemplate.getCollectionName(OtherCondition.class),EnterpriseDetailForTypeBO.class);
List<EnterpriseDetailForTypeBO> mappedResults = aggregate.getMappedResults();
log.info("第{}类表的数据:{}",i, JSONUtil.toJsonPrettyStr(mappedResults));
}
return ApiResult.success();
}
那么最终聚合查询出来的数据的格式就是这样的:
[
{
"enterpriseName": "Adong公司",
"unifiedSocialCreditCode": "xxxxxxxxxY",
"year": 2022,
"month": 6
},
{
"enterpriseName": "Bgog公司",
"unifiedSocialCreditCode": "567890000098877",
"year": 2022,
"month": 6
},
{
"enterpriseName": "gungdong公司",
"unifiedSocialCreditCode": "899999999999999999999999991",
"year": 2022,
"month": 6
},
]
就可以直接用定义好的实体类EnterpriseDetailForTypeBO来映射接收数据
package com.xiaomifeng1010.bo;
import com.fasterxml.jackson.annotation.JsonFormat;
import lombok.Data;
import java.io.Serializable;
import java.time.LocalDateTime;
/**
* @author xiaomifeng1010
* @version 1.0
* @date:
* @Description
*/
@Data
public class EnterpriseDetailForTypeBO implements Serializable {
private static final long serialVersionUID = 4343533526326L;
private String enterpriseName;
/**
* 统一社会信用代码
*/
private String unifiedSocialCreditCode;
private String createTimeStr;
private Integer year;
private Integer month;
private Integer enterpriseNum;
private String typeCode;
}
查询4
/**
* @param year
* @param month
* @description: 获取某年某月xxxx数量
* @author: xiaomifeng1010
* @date:
* @return: Integer
**/
@Override
public Integer getSomeConditionStatistics(Integer year, Integer month) {
String yearStr = StringUtils.EMPTY;
String monthStr = StringUtils.EMPTY;
if (Objects.nonNull(year) && Objects.isNull(month)) {
yearStr = Convert.toStr(year);
} else if (ObjectUtils.allNotNull(year, month)) {
yearStr = Convert.toStr(year);
monthStr = String.format("%02d", month);
}
Criteria criteria = new Criteria();
MatchOperation match = null;
GroupOperation group = null;
if (StringUtils.isNotEmpty(yearStr) && StringUtils.isEmpty(monthStr)) {
match = Aggregation.match(Criteria.where("year").is(yearStr));
group = Aggregation.group("year", "enterpriseName").last("year").as("year")
.last("enterpriseName").as("enterpriseName");
} else if (!StringUtils.isAnyBlank(monthStr, yearStr)) {
match = Aggregation.match(Criteria.where("year").is(yearStr).and("month").is(monthStr));
group = Aggregation.group("year", "month", "enterpriseName").last("year").as("year")
.last("month").as("month")
.last("enterpriseName").as("enterpriseName");
} else {
match = Aggregation.match(criteria);
group = Aggregation.group("enterpriseName")
.last("enterpriseName").as("enterpriseName");
}
TypedAggregation<BusinessDetail> businessDetailTypedAggregation = Aggregation.newAggregation(
BusinessDetail.class,
Aggregation.project().and("produceTime").substring(0, 4).as("year")
.and("produceTime").substring(5, 2).as("month")
.and("enterpriseBaseInfo .enterpriseName").as("enterpriseName")
.andExclude("_id"),
// 只获取指定年份的数据
match,
// 使用group去重
group,
// 统计今年xxx数量
Aggregation.count().as("count")
);
AggregationResults<Map> aggregateResults = mongoTemplate.aggregate(businessDetailTypedAggregation, Map.class);
List<Map> mappedResults = aggregateResults.getMappedResults();
Integer yearOrMonthCount = 0;
if (CollectionUtils.isNotEmpty(mappedResults)) {
yearOrMonthCount = MapUtils.getInteger(mappedResults.get(0), "count");
}
return yearOrMonthCount;
}
各类的Aggregation对象是可以抽取出来的,因为有时候,查询条件是变化的,所以需要根据查询条件是否有值,来进行判断是否需要进行某种类型的聚合操作,因为mongodb的聚合管道查询类似于linux中的管道符操作,前一步的操作结果会直接影响下一步的聚合操作,所以聚合查询时,project,match,goup,count等的顺序很重要,比如你在Aggregation.newAggregation()中最先传入了project类型的聚合并给对应的字段取了别名也就是as后的名称,那么你在第二步match的时候,字段就需要使用project操作后的别名,如果还是使用mongodb中collection中的原始的字段名就会报错,就会提示无法找到reference_name,所以这就是管道操作需要注意的地方,第一步操作的结果是下一步操作时的入参参数值,所以一般情况下,需要根据你的查询需求,project,match,goup,sort,sikp,count,unwind,bucket等操作,放在Aggregation.newAggregation()中的顺序都会不一样的,对于mongodb中内置的一些字符串函数和日期函数,主要都是在project类型的聚合操作中进行的,比如上边的例子,为了取出日期中的年份,使用substring函数操作提取日期字符串的前四位,当然如果你保存日期的时候,不是使用String类型,而且DateTime类型,则可以直接使用year,month这类的函数提取,更方便
下边罗列一下,mongodb中常用的一些函数操作
字符串类型的操作:
$substr,$indexOfBytes,$switch,$strLenBytes,$toLower,$toUpper,$concat,$split,
对数字型字段可以做加减乘除,取余等操作$add,$subtract,$multiply,$divide,$mod
时间类型字段:$year,$month,$week,$hour,$minute,$second,$millsecond,$dayOfYear,$dayOfMonth,$dayOfWeek
如果你创建了多个collection,并且之间有关联关系,你想实现类似mysql的联表查询,你需啊哟使用聚合中的lookup
$lookup操作符可以查找出集合中与另一个集合天剑匹配的文档,此功能类似于关系型数据库中的join查询,此操作符需要以下参数:
from:想要关联的另一个集合
localField: 集合中需要关联的键
foreignField: 与另一个集合关联的键
as: 关联后将另外一个结合的数据嵌入到此字段下
有时候,你需要做一些报表统计,为了查询速度加快,那么你可能需要将聚合的操作放在另一个collection中,平时用定时任务去聚合查询,查询结构汇出到另一个新的collection中,这样以后每次查询只需要从新的collection中普通查询就可以了,那么你需要使用到聚合操作中的out操作符
用$out可以将聚合出来的结果写入到一个指定的结合中,如果指定的集合不存在,则会创建一个新集合,如果指定的集合已存在,则会覆写到此集合中。但如果原有的集合中存在索引,而使用$out写入文档违反此索引,则会写入失败