Spring-data-Jpa分组分页求和查询
由于需求变更,该部分代码需要从项⽬中删除,记录到博客中以便⽇后使⽤,仅供参考
创建VO类
public class AgriculturalMaterialInputSumVO {
private Long agriculturalMaterialId;
private Long batchId;
private BigDecimal assetQuantitySum;
private Long facilityId;
}
实现类
public Page<AgriculturalMaterialInputVO>queryDouble(PageParam<AgriculturalMaterialInputBO> param){
//分页参数
Pageable pageable=PageRequest.Page()-1, Size());
//criteriaBuilder⽤于构建CriteriaQuery的构建器对象
CriteriaBuilder criteriaBuilder = CriteriaBuilder();
//criteriaQuery包含查询语句的各个部分,如where、max、sum、groupBy、orderBy等
CriteriaQuery<AgriculturalMaterialInputSumVO> query = ateQuery(AgriculturalMaterialInputSumVO.class);
//获取查询实例的属性,select * from books
Root<AgriculturalMaterialInput> root = query.from(AgriculturalMaterialInput.class);
//分组求和
//相当于select type,max(price) maxPrice,sum(price) sumPrice from books中select 与 from之间的部分
query.("agriculturalMaterialId"),("batchId"),BigDecimal(criteriaBuilder.("assetQuantity") )),("facilityId"));
//条件查询相当于
List<Predicate> predicateList =new ArrayList<>();
if(Optional.ofNullable(param).map(PageParam::getEntity).isPresent()){
if(OrganizationList.printList().size()>0){
Expression<Long> exp = ("organizationId");
predicateList.add(exp.in(OrganizationList.printList()));
}
if(Optional.Entity().getFacilityId()).isPresent()){
predicateList.add(criteriaBuilder.("facilityId").as(Long.class),Entity().getFacilityId()));
}
if(Optional.Entity().getBatchId()).isPresent()){
predicateList.add(criteriaBuilder.("batchId").as(Long.class),Entity().getBatchId()));
}
if(Optional.Entity().getAssetDate()).isPresent()){
predicateList.("assetDate").as(Date.class),Entity().getAssetDate()));
}
if(Optional.Entity().getAssetDate()).isPresent()){
predicateList.add(criteriaBuilder.("assetDate").as(Date.class),Entity().getAssetDate()));
}
query.where(criteriaBuilder.Array(new Predicate[predicateList.size()])));
}
/
/分组
<("agriculturalMaterialId"),("batchId"),("facilityId"));
List<AgriculturalMaterialInputSumVO> resultList = ateQuery(query).getResultList();
TypedQuery<AgriculturalMaterialInputSumVO> managerQuery = ateQuery(query);
//设置分页参数
managerQuery.setFirstResult((Page()-1)* Size());
managerQuery.Size());
//分页查询
PageImpl<AgriculturalMaterialInputSumVO> inputVOPage =new PageImpl<>(ResultList(), pageable, resultList.size());
PageImpl<AgriculturalMaterialInputSumVO> inputVOPage =new PageImpl<>(ResultList(), pageable, resultList.size());
List<AgriculturalMaterialInputSumVO> content = Content();
//农资投⼊
List<AgriculturalMaterialInput> listVO=new ArrayList<>();
//农资
List<Long> agriculturalMaterialIdList=new ArrayList();
//设施
List<Long> facilityIdList=new ArrayList();
//批次
List<Long> batchIdList=new ArrayList();
//农资
Map<Long,AgriculturalMaterialDTO> AgriculturalMap=new HashMap<>();
/
/设施
Map<Long,FacilityDTO> FacilityMap=new HashMap<>();
//批次
Map<Long,String> BatchMap=new HashMap<>();
Content()!=null){
for(AgriculturalMaterialInputSumVO inputVO : content){
AgriculturalMaterialInput materialInput = agriculturalMaterialInputRepository.A griculturalMaterialId(),BatchId(),FacilityId());
materialInput.AssetQuantitySum());
listVO.add(materialInput);
}
for(int i =0; i < listVO.size(); i++){
agriculturalMaterialIdList.(i).getAgriculturalMaterialId());
facilityIdList.(i).getFacilityId());
batchIdList.(i).getBatchId());
}
String materialJson = JSONObject.parseObject(materialFeign.queryListById(agriculturalMaterialIdList)).getString("data");
String batchJson = JSONObject.parseObject(batchFeign.queryListById(batchIdList)).getString("data");
String facilityJson = JSONObject.parseObject(facilityFeign.queryListById(facilityIdList)).getString("data");
List<AgriculturalMaterialDTO> AgriculturalList = JSON.parseArray(materialJson,AgriculturalMaterialDTO.class);
groupby分组List<FacilityDTO> FacilityList= JSON.parseArray(facilityJson,FacilityDTO.class);
List<BatchDTO> BatchList= JSON.parseArray(batchJson, BatchDTO.class);
if(AgriculturalList !=null){
AgriculturalList.forEach(agriculturalMaterialDTO -> AgriculturalMap.AgriculturalMaterialId(),agriculturalMaterialDTO));
}
if(FacilityList !=null){
FacilityList.forEach(facilityDTO -> FacilityMap.FacilityId(),facilityDTO));
}
if(BatchList !=null){
BatchList.forEach(batchDTO -> BatchMap.BatchId(),BatchName()));
}
}
List<AgriculturalMaterialInputVO> agriculturalMaterialInputVOList=new ArrayList<>();
listVO.forEach(agriculturalMaterialInput ->{
AgriculturalMaterialInputVO agriculturalMaterialInputVO =new AgriculturalMaterialInputVO();
//农资名称
agriculturalMaterialInputVO.(AgriculturalMaterialId()).getAgriculturalMat erialName());
//设施名
agriculturalMaterialInputVO.(FacilityId()).getFacilityName());
//批次名
agriculturalMaterialInputVO.(BatchId()));
//定额⽤量
agriculturalMaterialInputVO.(AgriculturalMaterialId()).getRatedQuantity());
//亩均⽤量
BigDecimal scope=new (FacilityId()).getScope()));
Double average= AssetQuantitySum().divide(scope,3,BigDecimal.ROUND_HALF_UP).doubleValue();
agriculturalMaterialInputVO.setAverage(average);
agriculturalMaterialInputVOList.add(agriculturalMaterialInputVO);
});
return new PageImpl<>(agriculturalMaterialInputVOList, pageable, resultList.size());
return new PageImpl<>(agriculturalMaterialInputVOList, pageable, resultList.size()); }
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论