CODEWITHSUNDEEP
javaspring-data-jpacriteria-api
beginner
beginner

Reputation: 55

How to Implement sum in CriteriaQuery

I want to fetch the data with the sum of one column and group by two fields from the database where the parameters will be dynamic

I tried to implement using predicates I am able to get the group by but sum is not working.

Page<PaymentDetail> aggregatedPaymentDetails2 = paymentDetailRepository.findAll(new Specification<PaymentDetail>() {
            @Override
            public Predicate toPredicate(Root<PaymentDetail> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
                final List<Predicate> predicates = new ArrayList<>();
                query.multiselect(root.get("id"), root.get("lockVersion"), cb.sum(root.get("amountPaid")), root.get("referenceNumber"), root.get("paymentSlot"));
                query.groupBy(root.get("referenceNumber"), root.get("paymentSlot").get("id"));
                for (final QueryCriterion queryCriterion : queryCriterionList) {
                    final OperatorEnum operatorEnum = queryCriterion.getOperatorEnum();
                    From join = root;
                    final String[] attributes = queryCriterion.getKey().split("\\.");
                    for (int i = 0, attributesLength = attributes.length - 1; i < attributesLength; i++) {
                        join = join.join(attributes[i], JoinType.LEFT);
                    }
                    final Path path = join.get(attributes[attributes.length - 1]);
                    final Object value = dataTypesHelper.typeCastValue(path, queryCriterion);
                    predicates.add(operatorEnum.getOperator().getPredicateByKeyAndValue(path, value, cb));

                }
                   return cb.and(predicates.toArray(new Predicate[predicates.size()]));
            }

        },pageable);

I expect the output to be page with sum of amountpaid and group by paymentslotid, PaymentSlot has a onetomany relationship with paymentdetails

Upvotes: 0

Views: 880

Answers (1)

Jens Schauder
Jens Schauder

Reputation: 81907

Specifications are intended to produce a Predicate which is essentially a where clause.

That you also can trigger side effects through the JPA API is an unfortunate weakness of the JPA API. There is no support in Spring Data JPA to change the return value through a Specification. Use a custom method implementation for this.

Upvotes: 1

Related Questions