其实我也是第一次用,花了一天时间,总结下,自己算是留下文档,有问题的朋友也可以看看!废话不说,直接上代码
package com.hollysys.tn.service;
import java.util.Date;
import java.util.List;
import java.util.Map;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.mongodb.core.MongoTemplate;
import org.springframework.data.mongodb.core.aggregation.Aggregation;
import org.springframework.data.mongodb.core.aggregation.AggregationOperation;
import org.springframework.data.mongodb.core.aggregation.LookupOperation;
import org.springframework.data.mongodb.core.aggregation.ProjectionOperation;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.stereotype.Service;
import com.hollysys.tn.common.base.service.impl.BaseServiceImpl;
import com.hollysys.tn.entity.MaterialUsage;
import lombok.Data;
@Data
class ResultEntity{
private Double amount;
private String _id;
private String name;
}
@Service
public class MaterialUsageService extends BaseServiceImpl<MaterialUsage>{
@Autowired
private MongoTemplate mongoTemplate;
public List<MaterialUsage> query(Date beginDate, Date endDate) {
LookupOperation lookupToLots = LookupOperation.newLookup().
from("lots").//关联表名 lots
localField("lotId").//关联字段
foreignField("_id").//主表关联字段对应的次表字段
as("entity");//查询结果集合名
LookupOperation lookupToMaterials = LookupOperation.newLookup().
from("materials").//关联表名 materials
localField("materialId").//关联字段
foreignField("_id").//主表关联字段对应的次表字段
as("bojo");//查询结果集合名
//Criteria criteriaToLots = new Criteria().and("entity.beginAt").lte(endDate).and("entity.endAt").gte(beginDate);
Criteria criteriaToLots = new Criteria().and("entity.fillingRate").gte(0);
AggregationOperation matchToLots = Aggregation.match(criteriaToLots);
ProjectionOperation project = Aggregation.project("entity._id","amount","bojo.name");
project.andInclude("_id");
Aggregation counts = Aggregation.newAggregation(lookupToLots,matchToLots,lookupToMaterials,project);
List<Map> results = mongoTemplate.aggregate(counts,"materialusage",Map.class).getMappedResults();
System.out.println(results.size());
//List<ResultEntity> results = mongoTemplate.aggregate(counts,"materialusage",ResultEntity.class).getMappedResults();
System.out.println(results.get(0).getName());
return null;
}
}
包都上了,我下面把代码解析下,我用的是springBoot哦!
-
lookupToLots
这是主表materialusage
关联---->lots
表,具体看代码后面注释。 -
lookupToMaterials
这是主表materialusage
关联---->materials
表,具体看代码后面注释 -
matchToLots
是我子表需要的过滤的条件。 -
ProjectionOperation
这个是选择你想要的字段,可以看到bojo.name
(子表)。 -
project.andInclude("_id");
这个注意了,是剃掉ID
,有个坑就是:ProjectionOperation project = Aggregation.project("entity._id","amount","bojo.name").andInclude("_id");
这样是剃不掉的。可以测试下。 -
Aggregation counts = Aggregation.newAggregation(lookupToLots,matchToLots,lookupToMaterials,project);
注意:这段代码主要注意后面括号里面的东西的顺序,按照你需要关联的表的顺序来吧
-
List<ResultEntity> results = mongoTemplate.aggregate(counts,"materialusage",Map.class).getMappedResults();
这段代码就是最后的查询了。至于返回的值类型,我是用自己设置的ResultEntity
对象,见代码头部。 -
对于返回值,我再补充点,我本来用的
Map
泛型接受的,其实我的返回值是这样的:
[{amount=100.0, _id=[20190612S], name=[自动打包带]}, {amount=100.0, _id=[20190612S], name=[纸箱空白条码]}]
你会注意到里面有两个数组,因为那两个值分别是另两个表的两个值,我尝试用对象ResultEntity接受,很有意思,看代码:
[ResultEntity(amount=100.0, _id=20190612S, name=自动打包带), ResultEntity(amount=100.0, _id=20190612S, name=纸箱空白条码), ResultEntity(amount=100.0, _id=20190612S, name=胶纸), ResultEntity(amount=100.0, _id=20190612S, name=纸箱)]