JPA EntityManager Specification CriteriaQuery 动态条件求和 统计 汇总

1. 求和查询方法

    private ReceivablesCostSumVO sumReceivablesCostDO(Specification<ReceivablesCostDO> receivablesCostDOSpecification) {
    final CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();// 返回值类型 ReceivablesCostSumVO.classfinal CriteriaQuery<ReceivablesCostSumVO> criteriaQuery = criteriaBuilder.createQuery(ReceivablesCostSumVO.class);//查询主表Root<ReceivablesCostDO> root = criteriaQuery.from(ReceivablesCostDO.class);//Specification 转换为 PredicatePredicate receivablesCostDOPredicate = receivablesCostDOSpecification.toPredicate(root,criteriaQuery,criteriaBuilder);//返回值：收款订单费用求和criteriaQuery.multiselect(// 计划应收金额 求和criteriaBuilder.sum(root.get("receivablesAmountPlan")),// 实际应收金额 求和criteriaBuilder.sum(root.get("receivablesAmount"))).where(receivablesCostDOPredicate);final TypedQuery<ReceivablesCostSumVO> receivablesCostDOTypedQuery = entityManager.createQuery(criteriaQuery);// 收款订单费用求和return receivablesCostDOTypedQuery.getSingleResult();}

public Specification<T> getCommonSpecification(R baseQueryReceivablesBillDTO) {
    Specification<T> disputeStatusSpecification = ((root, criteriaQuery, criteriaBuilder) -> {
    Predicate predicate = criteriaBuilder.conjunction();Join<T, List<ReceiptSource>> receiptSourceListJoin = root.join("receiptSourceList", JoinType.LEFT);Path<Object> receiptsCostList = receiptSourceListJoin.join("receiptsCostList", JoinType.LEFT);Predicate disputeStatus = criteriaBuilder.equal(receiptsCostList.get("disputeStatus"),IConstant.DISPUTESTATUS_YES);predicate = criteriaBuilder.and(predicate,// 纠纷确认：是criteriaBuilder.equal(receiptsCostList.get("disputeStatus"),IConstant.DISPUTESTATUS_YES));criteriaQuery.where(predicate);return criteriaQuery.getRestriction();});
}

2. 查询结果对象

package com.allqj.finance.business.receivables.secondhouse.entity.vo;import io.swagger.annotations.ApiModel;
import io.swagger.annotations.ApiModelProperty;import java.math.BigDecimal;/*** @description: 收款订单费用求和**/
@ApiModel(value = "收款订单费用求和")
public class ReceivablesCostSumVO {
    // 计划应收@ApiModelProperty(value = "计划应收金额 合计")private BigDecimal sumReceivablesAmountPlan ;// 应收金额@ApiModelProperty(value = "实际应收金额 合计")private BigDecimal sumReceivablesAmount ;/*** entityManager 的返回值使用了该构造方法*/public ReceivablesCostSumVO(BigDecimal sumReceivablesAmountPlan, BigDecimal sumReceivablesAmount) {
    this.sumReceivablesAmountPlan = sumReceivablesAmountPlan;this.sumReceivablesAmount = sumReceivablesAmount;}public ReceivablesCostSumVO() {
    }public BigDecimal getSumReceivablesAmountPlan() {
    return sumReceivablesAmountPlan;}public BigDecimal getSumReceivablesAmount() {
    return sumReceivablesAmount;}public void setSumReceivablesAmountPlan(BigDecimal sumReceivablesAmountPlan) {
    this.sumReceivablesAmountPlan = sumReceivablesAmountPlan;}public void setSumReceivablesAmount(BigDecimal sumReceivablesAmount) {
    this.sumReceivablesAmount = sumReceivablesAmount;}
}

3. 查询主表

package com.allqj.finance.business.receivables.common.entity.doe;import com.allqj.finance.business.common.entity.Item;
import lombok.Getter;
import lombok.NoArgsConstructor;
import lombok.Setter;
import lombok.experimental.Accessors;
import org.hibernate.annotations.BatchSize;
import org.hibernate.annotations.Where;import javax.persistence.*;
import java.math.BigDecimal;/*** @Description: 收款订单费用信息表*/
@Entity
@Getter
@Setter
@NoArgsConstructor
@Accessors(chain = true)
@Table(name = "biz_finance_receivables_cost")
public class ReceivablesCostDO {
    @Id@GeneratedValue(strategy = GenerationType.IDENTITY)private Integer id;// 计划应收private BigDecimal receivablesAmountPlan ;// 应收金额private BigDecimal receivablesAmount ;}

4. 其他

 public Specification<ReceivablesCostDO> getSpecification(){
    Specification<ReceivablesCostDO> specification = // 纠纷确认：是((root, criteriaQuery, criteriaBuilder) -> {
    Predicate predicate = criteriaBuilder.between(root.get("id"),1,10);predicate = criteriaBuilder.and(predicate,// 纠纷确认：是criteriaBuilder.equal(root.get("receivablesAmountPlan"),100));criteriaQuery.where(predicate);return criteriaQuery.getRestriction();});return specification;
}