mongoTemplate的分页,分组,统计

287 阅读 0 评论 190 点赞

我是靠谱客的博主威武大象，最近开发中收集的这篇文章主要介绍mongoTemplate的分页,分组,统计，觉得挺不错的，现在分享给大家，希望可以做个参考。

概述

crud参考:https://blog.csdn.net/weixin_39729846/article/details/82626721

mongoTemplate的分页,分组,统计以及聚合Aggregation和mapReduce的使用

package com.star.ac.mongodb.impl;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.commons.collections.CollectionUtils;
import org.apache.commons.lang3.StringUtils;
import org.apache.log4j.Logger;
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.DateOperators;
import org.springframework.data.mongodb.core.aggregation.GroupOperation;
import org.springframework.data.mongodb.core.aggregation.MatchOperation;
import org.springframework.data.mongodb.core.aggregation.ProjectionOperation;
import org.springframework.data.mongodb.core.aggregation.SortOperation;
import org.springframework.data.mongodb.core.aggregation.TypedAggregation;
import org.springframework.data.mongodb.core.mapreduce.GroupBy;
import org.springframework.data.mongodb.core.mapreduce.GroupByResults;
import org.springframework.data.mongodb.core.query.BasicQuery;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;
import org.springframework.stereotype.Repository;
import com.alibaba.fastjson.JSON;
import com.star.ac.dto.AlarmStatisDto;
import com.star.ac.dto.CommunitySecurityStatis;
import com.star.ac.dto.CommunitySecurityStatisData;
import com.star.ac.dto.DeviceAlarmStatisDto;
import com.star.ac.dto.SingleAlarm;
import com.star.ac.model.AlarmInfo;
import com.star.ac.model.AlarmStatisPerDay;
import com.star.ac.model.BreakdownInfo;
import com.star.ac.mongodb.AlarmMongodbDao;
import com.star.ac.param.AlarmParam;
import com.star.ac.param.AlarmStatisParam;
import com.star.ac.param.AlarmUpToDateParam;
import com.star.ac.param.CommunitySecurityParam;
import com.star.ac.param.DeviceAlarmStatisParam;
import com.star.common.utils.DateUtil;
import com.star.common.utils.StringUtil;
import com.mongodb.BasicDBObject;
import com.mongodb.DBObject;
@Repository
public class AlarmMongodbDaoImpl implements AlarmMongodbDao {
private static final Logger logger = Logger.getLogger(AlarmMongodbDaoImpl.class);
@Autowired
private MongoTemplate mongoTemplate;
/**
* @Description: 新增告警日志表并返回主键ID
* @Author:
* @Date: 2019/5/7 17:13
*/
@Override
public AlarmInfo insertAlarmInfo(AlarmInfo alarmInfo) {
mongoTemplate.insert(alarmInfo, "alarm_info");
return alarmInfo;
}
/**
* @Description: 新增故障日志表并返回主键ID
* @Author:
* @Date: 2019/5/8 9:50
*/
@Override
public BreakdownInfo insertBreakdownInfo(BreakdownInfo breakdownInfo) {
mongoTemplate.insert(breakdownInfo, "breakdown_info");
return breakdownInfo;
}
/**
* @Description: 分页查询
* @Author:
* @Date: 2019/5/8 9:59
*/
@Override
public Map<String, Object> getAlarmLogByDeviceCode(AlarmParam param) {
logger.info("AlarmMongodbDaoImpl.getAlarmLogByDeviceCode start");
Map<String, Object> resultMap = new HashMap<>();
int currentPage = param.getCurPage();
int pageSize = param.getPageSize();
// 设置字段
// 创建查询及条件
Query query = Query.query(Criteria.where("spCode").is(param.getSpCode()));
if (!StringUtil.isNil(param.getDeviceCode())) {
query.addCriteria(Criteria.where("deviceCode").is(param.getDeviceCode()));
}
// 设置起始数
query.skip((currentPage - 1) * pageSize)
// 设置查询条数
.limit(pageSize);
// 查询记录总数
int totalCount = (int) mongoTemplate.count(query, AlarmInfo.class);
// 数据总页数
int totalPage = totalCount % pageSize == 0 ? totalCount / pageSize : totalCount / pageSize + 1;
// 设置记录总数和总页数
resultMap.put("totalCount", totalCount);
resultMap.put("totalPage", totalPage);
// 按创建时间倒序
query.with(new Sort(Sort.Direction.DESC, "createDate"));
// 查询当前页数据集合
List<AlarmInfo> records = mongoTemplate.find(query, AlarmInfo.class);
resultMap.put("data", records);
return resultMap;
}
/**
* 分页查询
*/
@Override
public Map<String, Object> getBreakdownLogByDeviceCode(AlarmParam param) {
logger.info("AlarmMongodbDaoImpl.getBreakdownLogByDeviceCode start");
Map<String, Object> resultMap = new HashMap<>();
int currentPage = param.getCurPage();
int pageSize = param.getPageSize();
// 创建查询及条件
Query query = Query.query(Criteria.where("spCode").is(param.getSpCode()));
if (!StringUtil.isNil(param.getDeviceCode())) {
query.addCriteria(Criteria.where("deviceCode").is(param.getDeviceCode()));
}
// 设置起始数
query.skip((currentPage - 1) * pageSize)
// 设置查询条数
.limit(pageSize);
// 查询记录总数
int totalCount = (int) mongoTemplate.count(query, BreakdownInfo.class);
// 数据总页数
int totalPage = totalCount % pageSize == 0 ? totalCount / pageSize : totalCount / pageSize + 1;
// 设置记录总数和总页数
resultMap.put("totalCount", totalCount);
resultMap.put("totalPage", totalPage);
// 按创建时间倒序
query.with(new Sort(Sort.Direction.DESC, "createDate"));
// 查询当前页数据集合
List<BreakdownInfo> records = mongoTemplate.find(query, BreakdownInfo.class);
resultMap.put("data", records);
return resultMap;
}
public static void main(String[] args) {
String date2string = DateUtil.date2string(new Date());
System.out.println(date2string.substring(0, 10));
}
/*
* 指定字段 且返回最新的数据
*/
@Override
public List<AlarmInfo> getAlarmStatisUpToDate(AlarmUpToDateParam param) {
logger.info("AlarmMongodbDaoImpl.getAlarmStatisUpToDate start");
BasicDBObject dbObject = new BasicDBObject();
// dbObject.put("content", "content1");
// dbObject.put("level", 888); //相当于条件 level = 888
// dbObject.append(key, val);
// 指定返回的字段
BasicDBObject fieldsObject = new BasicDBObject();
fieldsObject.put("content", true);
fieldsObject.put("type", true);
fieldsObject.put("level", true);
fieldsObject.put("createDate", true);
Query query = new BasicQuery(dbObject.toJson(), fieldsObject.toJson());
String today = DateUtil.date2string(new Date()).substring(0, 10);
// 查询时间 mongoDB存储的是UTC时间 , 但读取和条件查询时会自动转换 不需要额外处理
Date createDateStart = DateUtil.string2Date(today + " 00:00:00", DateUtil.YYYYMMDDHHMMSS);
Date createDateEnd = DateUtil.string2Date(today + " 23:59:59", DateUtil.YYYYMMDDHHMMSS);
query.addCriteria(Criteria.where("createDate").gte(createDateStart).lte(createDateEnd));
query.addCriteria(Criteria.where("spCode").is(param.getSpCode()));
query.limit(param.getCount());
query.with(new Sort(Sort.Direction.DESC, "createDate"));
List<AlarmInfo> list = mongoTemplate.find(query, AlarmInfo.class, "alarm_info");
return list;
}
/**
* 先将日期转化成 yyyy-mm-dd格式, 再用日期分组count
*/
@Override
public List<CommunitySecurityStatis> communitySecurityStatis(CommunitySecurityParam param) {
logger.info("AlarmMongoDaoImpl.communitySecurityStatis start");
String begin = DateUtil.date2string(param.getBeginDate()).substring(0,10);
String end = DateUtil.date2string(param.getEndDate()).substring(0,10);
// 查询时间
mongoDB存储的是UTC时间 , 但读取和条件查询时会自动转换 不需要额外处理
Date createDateStart = DateUtil.string2Date(begin+" 00:00:00", DateUtil.YYYYMMDDHHMMSS);
Date createDateEnd = DateUtil.string2Date(end+" 23:59:59", DateUtil.YYYYMMDDHHMMSS);
List<CommunitySecurityStatis> stats = new ArrayList<CommunitySecurityStatis>();
if (CollectionUtils.isNotEmpty(param.getRuleIds())) {
for (Long ruleId : param.getRuleIds()) {
String ruleName = getRuleName(ruleId);
Criteria criteria = Criteria.where("createDate").gte(createDateStart).lte(createDateEnd);
criteria.and("spCode").is(param.getSpCode());
criteria.and("ruleId").is(ruleId);
//聚合查询转换格式时需要增加8小时
Aggregation aggregation1 = Aggregation.newAggregation(Aggregation.match(criteria),
Aggregation.project().andExpression("{$dateToString:{format:'%Y-%m-%d',date: {$add:{'$createDate',8*60*60000}}}}").as("date"),
Aggregation.group("date").first("date").as("date")
.count().as("num"));
AggregationResults<CommunitySecurityStatisData> outputTypeCount1 = mongoTemplate.aggregate(aggregation1, "alarm_info",
CommunitySecurityStatisData.class);
if (null == outputTypeCount1 || CollectionUtils.isEmpty(outputTypeCount1.getMappedResults())) {
continue;
}
CommunitySecurityStatis statis = new CommunitySecurityStatis();
List<CommunitySecurityStatisData> list = outputTypeCount1.getMappedResults();
statis.setName(ruleName);
statis.setRuleId(ruleId);
statis.setStatisData(list);
stats.add(statis);
}
}
/*
* criteria.andOperator(Criteria.where("createTime").lte(param.getEndDate()),
* Criteria.where("createTime").gte(param.getBeginDate()));
*/
/*
GroupBy groupBy = GroupBy
.keyFunction("function(doc){ var date = new Date(doc.createTime);"
+ "	var dateKey = ""+date.getFullYear()+"-"+(date.getMonth()+1)+"-"+date.getDate();"
+ "	return {'date':dateKey};"
+ "}")
.initialDocument("{num : 0}").reduceFunction("function(doc, out){"
+ "out.num += 1;"
+ "}");
GroupBy groupBy1 = new GroupBy("{keyf : function(doc){ var date = new Date(doc.createTime);"
+ "	var dateKey = ""+date.getFullYear()+"-"+(date.getMonth()+1)+"-"+date.getDate();"
+ "	return {'date':dateKey};"
+ "} }")
.initialDocument("{num : 0}")
.reduceFunction("function(doc, out){"
+ "out.num += 1;"
+ "}");
GroupByResults<CommunitySecurityStatisData> statisResults = mongoTemplate.group(criteria, "alarm_info", groupBy1,
CommunitySecurityStatisData.class);
*/
return stats;
}
/**
* 单条件查询limit 1
* @param ruleId
* @return
*/
private String getRuleName(Long ruleId) {
Query query = Query.query(Criteria.where("ruleId").is(ruleId));
AlarmInfo findOne = mongoTemplate.findOne(query, AlarmInfo.class);
if (null == findOne) {
return null;
}
return findOne.getRuleName();
}
/*
* 以deviceTypeName分组 个数求和
*/
@Override
public List<DeviceAlarmStatisDto> deviceAlarmStatis(Map<String, Object> paramMap) {
Criteria criteria = Criteria.where("createDate").gte(paramMap.get("beginDate")).lte(paramMap.get("endDate"));
if (StringUtils.isNotBlank((String) paramMap.get("spCode"))) {
criteria.and("spCode").is(paramMap.get("spCode"));
}
// first("") 分组后，如果某属性(非分组属性)出现多个值 ,取第一个
Aggregation aggregation1 = Aggregation.newAggregation(Aggregation.match(criteria),
Aggregation.group("deviceTypeName").first("deviceTypeName").as("deviceTypeName").first("deviceType").as("deviceType").count().as("value"));
AggregationResults<DeviceAlarmStatisDto> outputTypeCount1 = mongoTemplate.aggregate(aggregation1, "alarm_info",
DeviceAlarmStatisDto.class);
if (null == outputTypeCount1) {
return null;
}
return outputTypeCount1.getMappedResults();
}
/*
* 分组统计
以statisDate分组并且将total值求和
*/
@Override
public List<AlarmStatisDto> getAlarmStatis(Map<String, Object> paramMap) {
Date beginDate = (Date) paramMap.get("beginDate");
Date endDate = (Date) paramMap.get("endDate");
AlarmStatisParam paramDto = (AlarmStatisParam) paramMap.get("paramDto");
Criteria criteria = Criteria.where("updateTime").gte(beginDate).lte(endDate);
if (StringUtils.isNotBlank(paramDto.getSpCode())) {
criteria.and("spCode").is(paramDto.getSpCode());
}
if (StringUtils.isNotBlank(paramDto.getProductCode())) {
criteria.and("produCode").is(paramDto.getProductCode());
}
if (StringUtils.isNotBlank(paramDto.getDeviceType())) {
criteria.and("deviceType").is(paramDto.getDeviceType());
}
if (StringUtils.isNotBlank(paramDto.getDeviceCode())) {
criteria.and("deviceCode").is(paramDto.getDeviceCode());
}
if (StringUtils.isNotBlank(paramDto.getDeviceGroupCode())) {
criteria.and("deviceGroupCodes").is(paramDto.getDeviceGroupCode());
}
Aggregation aggregation1 = Aggregation.newAggregation(Aggregation.match(criteria),
Aggregation.group("statisDate").first("statisDate").as("date").first("updateTime").as("updateTime").sum("total").as("value"),
Aggregation.sort(new Sort(Sort.Direction.ASC, "updateTime")));
String collectionName = "";
//按天
if (1 == (Integer)paramMap.get("type")) {
collectionName = "alarm_statis_per_day";
//按小时
}else if(0 == (Integer)paramMap.get("type")){
collectionName = "alarm_statis_per_hour";
}
AggregationResults<AlarmStatisDto> outputTypeCount1 = mongoTemplate.aggregate(aggregation1, collectionName,
AlarmStatisDto.class);
List<AlarmStatisDto> mappedResults = outputTypeCount1.getMappedResults();
// 分组函数 同样可以实现上面分组求和功能
/*GroupBy groupBy = new GroupBy("statisDate").
initialDocument("{date : '', value : 0}").
reduceFunction("function(doc, prev){"
+ "prev.date = doc.statisDate;"
+ "prev.value += doc.total;"
+ "}");
GroupByResults<AlarmStatisDto> statisResults = mongoTemplate.group(criteria, "alarm_statis_per_day", groupBy, AlarmStatisDto.class);
Iterator<AlarmStatisDto> iterator = statisResults.iterator();*/
return mappedResults;
}
@Override
public AlarmStatisDto getAlarmStatisParam(AlarmStatisParam param) {
logger.info("getAlarmStatisToday start");
Criteria criteria = Criteria.where("createDate").gte(param.getBegin()).lte(param.getEnd());
if (StringUtils.isNotBlank(param.getSpCode())) {
criteria.and("spCode").is(param.getSpCode());
}
if (StringUtils.isNotBlank(param.getProductCode())) {
criteria.and("produCode").is(param.getProductCode());
}
if (StringUtils.isNotBlank(param.getDeviceType())) {
criteria.and("deviceType").is(param.getDeviceType());
}
if (StringUtils.isNotBlank(param.getDeviceCode())) {
criteria.and("deviceCode").is(param.getDeviceCode());
}
if (StringUtils.isNotBlank(param.getDeviceGroupCode())) {
criteria .and("deviceGroupCodes").is(param.getDeviceGroupCode());
}
Query query = Query.query(criteria);
int count = (int) mongoTemplate.count(query, AlarmInfo.class);
AlarmStatisDto dto = new AlarmStatisDto();
if (param.getStatisType() == 1) {
dto.setDate(DateUtil.date2string(param.getEnd(), DateUtil.YYYYMMDD));
}else {
dto.setDate(DateUtil.date2string(param.getEnd(), DateUtil.YYYYMMDDHH).substring(0,13)+":00:00");
}
dto.setUpdateTime(new Date());
dto.setValue(count);
return dto;
}
}

对内嵌文档的某个字段按天(小时)分组求最大值

数据格式

{
"_id" : ObjectId("5eb66d53fa92d16ba17cedc6"),
"spCode" : "project_99c7041d7c274df6a0049935231d9f6c",
"spName" : "交付中心",
"productId" : "PC_1578358882051",
"productName" : "红外传感器",
"deviceType" : "WeiChuanInfraredSensor_304C",
"deviceCode" : "ffffff100000a58b",
"deviceName" : "公司红外感应",
"reportData" : {
"voltage" : "18.8"
},
"createDate" : ISODate("2020-05-09T08:44:03.000Z"),
"deviceTypeName" : "人体红外",
"productGroupCodes" : [],
"msgSource" : "da",
"msgCode" : "updata",
"msgId" : "5608",
"msgStatus" : "100",
"serviceId" : "CoshipElevator",
"confirm" : "false",
"port" : "10",
"imageCodes" : [],
"_class" : "com.coship.da.model.DcReportRecords"
}

代码

Criteria criteria = Criteria.where("createDate").gte(DateUtil.string2Date(param.getStartTime())).lte(DateUtil.string2Date(param.getEndTime()));
criteria.and("deviceCode").is(param.getDeviceCode());
String propertyName = "reportData."+ param.getPropertyCode();
String format ;
if("1".equals(param.getGroupType())){
format = "%Y-%m-%d";
}else{
format = "%H:00";
}
Aggregation aggregation = Aggregation.newAggregation(
Aggregation.match(criteria),
Aggregation.project("reportData", "createDate").andExpression("{$dateToString:{format:'"+format+"', date: '$createDate', timezone: 'Asia/Shanghai'}}").as("date"),
Aggregation.group("date")
.max(propertyName).as("propertyValue")
.first("createDate").as("createDate").first("date").as("date"));
AggregationResults<JSONObject> results = mongoTemplate.aggregate(aggregation, "t_dc_report_records", JSONObject.class);
List<JSONObject> mappedResults = results.getMappedResults();

[{
"_id": "16:00",
"propertyValue": "18.8",
"createDate": {
"date": 9,
"day": 6,
"hours": 16,
"minutes": 44,
"month": 4,
"seconds": 3,
"time": 1589013843000,
"timezoneOffset": -480,
"year": 120
},
"date": "16:00"
},
{
"_id": "17:00",
"propertyValue": "19.8",
"createDate": {
"date": 9,
"day": 6,
"hours": 16,
"minutes": 44,
"month": 4,
"seconds": 3,
"time": 1589013843000,
"timezoneOffset": -480,
"year": 120
},
"date": "17:00"
},
}]