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

1. 求和查询方法

    private ReceivablesCostSumVO sumReceivablesCostDO(Specification<ReceivablesCostDO> receivablesCostDOSpecification) {
        final CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
        // 返回值类型 ReceivablesCostSumVO.class
        final CriteriaQuery<ReceivablesCostSumVO> criteriaQuery = criteriaBuilder
                .createQuery(ReceivablesCostSumVO.class);
        //查询主表
        Root<ReceivablesCostDO> root = criteriaQuery.from(ReceivablesCostDO.class);
        //Specification 转换为 Predicate
        Predicate 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;
}