使用MongoTemplate 实现简单查询
1. pom依赖
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-mongodb</artifactId>
<version>2.2.2.RELEASE</version>
</dependency>
2. impl
@Resource
private MongoTemplate mongoTemplate;
AggregationOptions aggregationOptions = AggregationOptions.builder().allowDiskUse(true).build();
//封装查询结果条件
Aggregation reportlist = Aggregation.newAggregation(match(vo),group(vo), sort(vo), skip(vo), limit(vo)).withOptions(aggregationOptions);
//封装查询总条数条件
Aggregation reportCount = Aggregation.newAggregation(match(vo), group(vo), Aggregation.count().as("total")).withOptions(aggregationOptions);
//封装查询汇总条件
Aggregation reportsum = Aggregation.newAggregation(match(vo), groupSum()).withOptions(aggregationOptions);
//获取查询结果
List<ReportData> list = mongoTemplate.aggregate(reportlist, "集合名", ReportData.class).getMappedResults();
//获取汇总结果
ReportData rd = mongoTemplate.aggregate(reportsum, "集合名", ReportData.class).getMappedResults().get(0);
//获取总条数
Long total = ((Integer) mongoTemplate.aggregate(reportCount, "集合名", Map.class).getMappedResults().get(0).get("total")).longValue();
//where条件
private MatchOperation match(ReportRequestVo vo) {
Criteria c = Criteria.where("date").gte(vo.getDateStart()).lte(vo.getDateEnd());
List<Criteria> list = new ArrayList<>();
list.add(Criteria.where("a_id").in(vo.getAIdList()));
if (!CollectionUtils.isEmpty(vo.getDeIdList())) {
list.add(Criteria.where("ae_id").nin(vo.getDeIdList()));
}
if (StringUtils.isNoneBlank(vo.getSn())) {
list.add(Criteria.where("sn").is(vo.getSn()));
}
c.andOperator(list.toArray(new Criteria[0]));
return Aggregation.match(c);
}
//group分组 然后查询的字段名
private GroupOperation group(ReportRequestVo vo) {
return Aggregation.group(vo.getGroupByList().toArray(new String[0]))
.first("date").as("date")
.first("a_id").as("aId")
.sum("num").as("num")
;
}
//排序
private AggregationOperation sort(ReportRequestVo vo) {
return Aggregation.sort("desc".equals(vo.getSortOrder()) ? Sort.Direction.DESC : Sort.Direction.ASC, vo.getSortBy());
}
//每页条数
private LimitOperation limit(ReportRequestVo vo) {
return Aggregation.limit(vo.getLimit());
}
//从第几条开始
private SkipOperation skip(ReportRequestVo vo) {
return Aggregation.skip((vo.getPage() - 1) * vo.getLimit());
}
3. 排除结果集默认id字段
- 问题: 由于查询结果集有默认id字段,java类也有id字段,类型不一致导致类转换异常
- 处理: 在查询时排除默认id字段
// 添加 Aggregation.project().andExclude("_id")
Aggregation reportlist = Aggregation.newAggregation(match(vo), groupPlace(vo),Aggregation.project().andExclude("_id")).withOptions(reportAggregationOptions);