CriteriaBuilder - Sum using SelectCase

Question

I am trying to perform a summation SQL query like the following:

select group_ID, sum(case when user_type = 'Exec' then 1000  
                          when user_type = 'Office' then 10 else 0 end)  
from subscription  
group by group_ID;

using the following snippet from a hiberate CriteriaBuilder query:

criteriaBuilder.sum(
  criteriaBuilder.selectCase()  
     .when(criteriaBuilder.equal(subscriptionJoin.get(Subscription_.userType), "Exec"),1000)  
     .when(criteriaBuilder.equal(subscriptionJoin.get(Subscription_.userType), "Office"),1)  
     .otherwise(101))

However the following compile error appears:

Inferred type 'java.lang.object' for type parameter 'N' is not within its bound; should extend 'java.lang.number'

Any idea how to support performing a summation using the selectCase?

score 18 · Accepted Answer · answered Jul 17 '12 at 19:35

18

Sum is defined as follows:

<N extends Number> Expression<N> sum(Expression<N> x);

So reason to the compilation error is that sum method expect such arguments which is Expression with type that extends Number. It determines type from the selectCase and ends up with java.lang.Object, which is not acceptable.

Problem can be solved by giving type parameter (<Number>):

criteriaBuilder.sum(
  criteriaBuilder.<Number>selectCase()

answered Jul 17 '12 at 19:35

Mikko Maunu

41,366
10
132
135

1

Thank you very much for the solution, Mikko. Is this approach preferable to suffixing .as(Number.class) to the selectCase(). – user1186233 Jul 18 '12 at 15:35
1

That is just matter of opinion, I like using language construct more than calling as-method, also because it asks less code. Because this answer provided solution, only thing there is still to do is to accept answer: http://meta.stackexchange.com/a/5235/171185 – Mikko Maunu Jul 19 '12 at 07:45

score 1 · Answer 2 · answered Feb 22 '16 at 20:56

We are using Spring Data JPA in our project and i have the same case where i need to do sum. Instead of criteria query i'm just following the "named parameters" approach because this approach seems easy.

My method which gives me sum is as follows.

    public interface ITransactionEntryRepo extends PagingAndSortingRepository<TransactionEntryEntity, String> {

        @Query("select SUM(CASE WHEN te.debit = 'Y' THEN (te.amount * - 1) WHEN te.debit = 'N' THEN te.amount ELSE 0 END) AS availablebalance FROM TransactionEntity t, TransactionEntryEntity te WHERE t.id = te.transactionEntity.id and te.accountEntity.id = :id and te.valid = 'T' and t.retcode = 'XX' GROUP BY te.accountEntity.id")
            public double findAvailableBalance(@Param("id") String id);
}

And I call this method in the class where i need

double balance = iTransactionEntryRepo.findAvailableBalance(accountEntity.getId());

and pass it(balance) wherever I need to. Hope this helps someone.

Lunatic · Answer 3 · 2022-11-13T16:09:26.623

For aggregate operation you should pass the CriteriaQuery with numeric type to be proper expression for criteria builder, however this may not affect your criteria base restriction of you entity type. Finally you can append the desired predicates to your criteria query for having criteria base aggregation.

public class Aggregate<T, S extends Number> {
    public Aggregate(Class<T> tableType, Class<S> type) {
        this.criteriaBuilder = entityManager.getCriteriaBuilder();
        this.criteria = criteriaBuilder.createQuery(type);
        this.root = criteria.from(tableType);
    }

    public Aggregate<T, S> aggregate(String field) {
        criteria.select(criteriaBuilder.sum(root.get(field)));
        return this;
    }

    public <I> Aggregate<T, S> restrict(String field, I i) {
        criteria.where(criteriaBuilder.equal(root.get(field), i));
        return this;
    }

    public S perform() {
        return entityManager.createQuery(criteria).getSingleResult();
    }

    private Root<T> root;

    private final CriteriaQuery<S> criteria;

    private final CriteriaBuilder criteriaBuilder;
}

CriteriaBuilder - Sum using SelectCase

3 Answers3

Linked