6

I want to build the having clause shown below using CriteriaBuilder :

select objectid,
   sum(case when attr_meta = 'severity' then 1 else 0 end) as severity,
   sum(case when attr_meta = 'priority' then 1 else 0 end) as priority
from object d
group by objectid
having sum(case when attr_meta = 'severity' then 1 else 0 end) != 1 
    or sum(case when attr_meta = 'priority' then 1 else 0 end) != 1;

I tried the below approach:

Predicate p = cb.equal(cb.sum(cb.<Integer>selectCase()
    .when(cb.equal(root.get("name"), 'severity'), 1).otherwise(0)), 1);
p = cb.or(p, cb.equal(cb.sum(cb.<Integer>selectCase()
    .when(cb.equal(root.get("name"), 'priority'), 1).otherwise(0)), 1));

but this gives the below exception:

java.lang.NullPointerException
    at java.lang.Class.isAssignableFrom(Native Method)
    at org.hibernate.ejb.criteria.ValueHandlerFactory.isNumeric(ValueHandlerFactory.java:70)
    at org.hibernate.ejb.criteria.predicate.ComparisonPredicate.<init>(ComparisonPredicate.java:69)
    at org.hibernate.ejb.criteria.CriteriaBuilderImpl.equal(CriteriaBuilderImpl.java:392)

The exception seems to be coming from the outer CriteriaBuilderImpl.equal() call, the one that encloses selectCase() call.

The equal() call internally needs type info of the Expression. The selectCase() call, while creating the Expression, puts in type as null. Do we have some way to handle this situation? Either a way to let the equal() know the type, or a completely different approach to the above mentioned query?

Tiny
  • 27,221
  • 105
  • 339
  • 599
Nikhil
  • 93
  • 1
  • 2
  • 6
  • Did you solve the problem? I'm having the same problem. – Tiny Nov 21 '13 at 11:16
  • Just replace all literals with `CriteriaBuilder#literal(T value)` such as `cb.literal(0)`, `cb.literal(1)` etc : http://stackoverflow.com/a/34302569/1391249 – Tiny Dec 17 '15 at 01:54

2 Answers2

8

Performing typecast upon expression (by using Expression#as(Class)) could help.

Expression<Integer> sumExp = builder.sum(
            builder.<Integer>selectCase()
            .when(builder.equal(root.get("name"), "severity"), 1)
            .otherwise(0)
    );
Predicate eqPredicate = builder.equal(sumExp.as(Integer.class), 1);
Snarkovski
  • 273
  • 4
  • 10
2

I think that the answer to this is to have a cb.nullLiteral return for as the ".otherwise" part of the clause. This is the solution that got all this working for me. See example below. If this helps, please flag this answer.

cb.count(cb.selectCase().when( status.get("maxAction").in( introTypes ), 1).otherwise(cb.nullLiteral(Number.class)) ),
NicholasKarl
  • 253
  • 3
  • 12