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?

Answer 1

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()

Answer 2

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.