Having clause along with 'case when' in CriteriaBuilder
Solution 1
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);
Solution 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)) ),
Nikhil
Updated on October 04, 2022Comments
-
Nikhil over 1 year
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 enclosesselectCase()
call.The
equal()
call internally needs type info of theExpression
. TheselectCase()
call, while creating theExpression
, puts in type asnull
. Do we have some way to handle this situation? Either a way to let theequal()
know the type, or a completely different approach to the above mentioned query?