Using the 'case...when...then...else...end' construct in the 'having' clause in JPA criteria query
The following criteria query calculates the average of rating of different groups of products.
CriteriaBuilder criteriaBuilder=entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple>criteriaQuery=criteriaBuilder.createQuery(Tuple.class);
Metamodel metamodel=entityManager.getMetamodel();
EntityType<Product>entityType=metamodel.entity(Product.class);
Root<Product>root=criteriaQuery.from(entityType);
SetJoin<Product, Rating> join = root.join(Product_.ratingSet, JoinType.LEFT);
Expression<Number> quotExpression = criteriaBuilder.quot(criteriaBuilder.sum(join.get(Rating_.ratingNum)), criteriaBuilder.count(join.get(Rating_.ratingNum)));
Expression<Integer> roundExpression = criteriaBuilder.function("round", Integer.class, quotExpression);
Expression<Object> selectExpression = criteriaBuilder.selectCase().when(quotExpression.isNull(), 0).otherwise(roundExpression );
criteriaQuery.select(criteriaBuilder.tuple(root.get(Product_.prodId).alias("prodId"), selectExpression.alias("rating")));
criteriaQuery.groupBy(root.get(Product_.prodId));
criteriaQuery.having(criteriaBuilder.greaterThanOrEqualTo(roundExpression, 0));
criteriaQuery.orderBy(criteriaBuilder.desc(root.get(Product_.prodId)));
TypedQuery<Tuple> typedQuery = entityManager.createQuery(criteriaQuery);
List<Tuple> tuples = typedQuery.getResultList();
It generates the following SQL query :
SELECT product0_.prod_id AS col_0_0_,
CASE
WHEN Sum(ratingset1_.rating_num) / Count(ratingset1_.rating_num) IS
NULL THEN
0
ELSE Round(Sum(ratingset1_.rating_num) / Count(ratingset1_.rating_num))
END AS col_1_0_
FROM social_networking.product product0_
LEFT OUTER JOIN social_networking.rating ratingset1_
ON product0_.prod_id = ratingset1_.prod_id
GROUP BY product0_.prod_id
HAVING Round(Sum(ratingset1_.rating_num) / Count(ratingset1_.rating_num)) >= 0
ORDER BY product0_.prod_id DESC
The case...when structure replaces null values with 0, if the specified expression in the case clause is evaluated to null.
I need the same case...when construct in the having clause so that the group of rows returned by the group by clause can be filtered by replacing null with 0 in the list of values calculated by the case...when construct, if any.
Accordingly, the having clause should be generated like
HAVING
(CASE
WHEN Sum(ratingset1_.rating_num)/Count(ratingset1_.rating_num) IS
NULL THEN 0
ELSE Round(sum(ratingset1_.rating_num)/Count(ratingset1_.rating_num))
END)>=0
It could be possible, if in the greaterThanOrEqualTo() method, selectExpression instead of roundExpression is given but it is not possible. Doing so, generates a compile-time error indicating type mismatch between Expression<Integer> and Expression<Object>.
So how can I have the same case...when structure in the having clause as in the select clause?
I have also tried by removing the generic type parameter Object of the expression like Expression selectExpression but doing so, caused the NullPointerException to be thrown.
Moreover, alias names (prodId, rating) as given in the select clause have no effect in the generated SQL as can be seen. Why columns are not aliased here? Am I missing something?
If columns are aliased then, it should be possible to write the having clause just like follows.
having rating>=0
and having in the criteria query should be as follows,
criteriaQuery.having(criteriaBuilder.greaterThanOrEqualTo(join.<Integer>get("rating"), 0));
but as columns are not aliased in the select clause, it throws an exception.
java.lang.IllegalArgumentException: Unable to resolve attribute [rating] against path [null]
What is the way to get around this situation? Anyway, the rows returned by Group by should be filtered by replacing null with 0 in the list of values produced by case...when in the select clause.
I'm using JPA 2.0 provided by Hibernate 4.2.7 final.
EDIT:
I have tried with the following expression :
Expression<Integer> selectExpression = criteriaBuilder.<Integer>selectCase()
.when(quotExpression.isNull(), 0)
.<Integer>otherwise(roundExpression);
but it caused the following exception to be thrown :
Caused by: java.lang.NullPointerException
at java.lang.Class.isAssignableFrom(Native Method)
at org.hibernate.ejb.criteria.ValueHandlerFactory.isNumeric(ValueHandlerFactory.java:69)
at org.hibernate.ejb.criteria.predicate.ComparisonPredicate.<init>(ComparisonPredicate.java:69)
at org.hibernate.ejb.criteria.CriteriaBuilderImpl.greaterThanOrEqualTo(CriteriaBuilderImpl.java:468)
How can the following expression work then,
Expression<Integer> roundExpression = criteriaBuilder
.function("round", Integer.class, quotExpression);
both have the same type?
Is there a way to put the case...when structure in the having clause?
EDIT
Changing the expression type to
Expression<Integer> selectExpression = criteriaBuilder
.<Integer>selectCase()
.when(quotExpression.isNull(), 0)
.<Integer>otherwise(roundExpression);
in EclipseLink (2.3.2) works hence, it can be made available in the having clause.
In case of Hibernate provider, it throws the NullPoiterExcpetion, if an attempt is made to change the expression type of selectCase() (which returns Expression<Object> by default).
Update :
This issue still persists in Hibernate 5.0.5 final.
Answer
This is very unlikely to be a bug in Hibernate. There was a technical mistake in fabricating the criteria query given. Taking the same example but in a simpler form.
Let's assume that we are interested in generating the following SQL query.
SELECT
p.prod_id,
p.prod_name,
CASE
WHEN sum(r.rating_num)/count(DISTINCT r.rating_id) IS NULL THEN 0
ELSE round(sum(r.rating_num)/count(DISTINCT r.rating_id))
END AS avg_rating
FROM
product p
LEFT OUTER JOIN
rating r
ON p.prod_id=r.prod_id
GROUP BY
p.prod_id,
p.prod_name
HAVING
CASE
WHEN sum(r.rating_num)/count(DISTINCT r.rating_id) IS NULL THEN 0
ELSE round(sum(r.rating_num)/count(DISTINCT r.rating_id))
END>=1
Based on the following table in MySQL.
mysql> desc rating;
+-------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+----------------+
| rating_id | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| prod_id | bigint(20) unsigned | YES | MUL | NULL | |
| rating_num | int(10) unsigned | YES | | NULL | |
| ip_address | varchar(45) | YES | | NULL | |
| row_version | bigint(20) unsigned | NO | | 0 | |
+-------------+---------------------+------+-----+---------+----------------+
5 rows in set (0.08 sec)
This table rating has an obvious many-to-one relationship with another table product (prod_id is the foreign key referencing the primary key prod_id in the product table).
In this question, we are only interested in the CASE construct in the HAVING clause.
The following criteria query,
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder();
CriteriaQuery<Tuple> criteriaQuery = criteriaBuilder.createTupleQuery();
Root<Product> root = criteriaQuery.from(entityManager.getMetamodel().entity(Product.class));
ListJoin<Product, Rating> prodRatingJoin = root.join(Product_.ratingList, JoinType.LEFT);
List<Expression<?>> expressions = new ArrayList<Expression<?>>();
expressions.add(root.get(Product_.prodId));
expressions.add(root.get(Product_.prodName));
Expression<Integer> sum = criteriaBuilder.sum(prodRatingJoin.get(Rating_.ratingNum));
Expression<Long> count = criteriaBuilder.countDistinct(prodRatingJoin.get(Rating_.ratingId));
Expression<Number> quotExpression = criteriaBuilder.quot(sum, count);
Expression<Integer> roundExpression = criteriaBuilder.function("round", Integer.class, quotExpression);
Expression<Integer> selectExpression = criteriaBuilder.<Integer>selectCase().when(quotExpression.isNull(), criteriaBuilder.literal(0)).otherwise(roundExpression);
expressions.add(selectExpression);
criteriaQuery.multiselect(expressions.toArray(new Expression[0]));
expressions.remove(expressions.size() - 1);
criteriaQuery.groupBy(expressions.toArray(new Expression[0]));
criteriaQuery.having(criteriaBuilder.greaterThanOrEqualTo(selectExpression, criteriaBuilder.literal(1)));
List<Tuple> list = entityManager.createQuery(criteriaQuery).getResultList();
for (Tuple tuple : list) {
System.out.println(tuple.get(0) + " : " + tuple.get(1) + " : " + tuple.get(2));
}
Generates the following correct SQL query as expected.
select
product0_.prod_id as col_0_0_,
product0_.prod_name as col_1_0_,
case
when sum(ratinglist1_.rating_num)/count(distinct ratinglist1_.rating_id) is null then 0
else round(sum(ratinglist1_.rating_num)/count(distinct ratinglist1_.rating_id))
end as col_2_0_
from
projectdb.product product0_
left outer join
projectdb.rating ratinglist1_
on product0_.prod_id=ratinglist1_.prod_id
group by
product0_.prod_id ,
product0_.prod_name
having
case
when sum(ratinglist1_.rating_num)/count(distinct ratinglist1_.rating_id) is null then 0
else round(sum(ratinglist1_.rating_num)/count(distinct ratinglist1_.rating_id))
end>=1
For the technical perspective, look at the following line in the above criteria query.
criteriaQuery.having(criteriaBuilder.greaterThanOrEqualTo(selectExpression, criteriaBuilder.literal(1)));
Its analogous line in the question was written like following.
createQuery.having(criteriaBuilder.greaterThanOrEqualTo(selectExpression, 1));
See the original expression in the question doing the exact same thing :
Expression<Integer> selectExpression = criteriaBuilder.<Integer>selectCase()
.when(quotExpression.isNull(), 0)
.<Integer>otherwise(roundExpression);
This expression was attempted to be passed to criteriaBuilder.greaterThanOrEqualTo() as follows.
criteriaQuery.having(criteriaBuilder.greaterThanOrEqualTo(selectExpression, 0));
Pay special attention to the second parameter to greaterThanOrEqualTo() above. It is 0. It should have been criteriaBuilder.literal(0) instead hence, the exception as mentioned in the question.
Thus, always insist upon using CriteriaBuilder#literal(T value) for literal values whenever necessary as done above while using expressions in the CriteriaBuilder#selectCase() construct.
Tested on Hibernate 4.3.6 final, Hibernate 5.0.5 final alternatively. I will try to run the same query on EclipseLink (2.6.1 final) later on. There should not be a quirk anymore.
EclipseLink has no problem at all with the modified version of the query except that it requires an Object type parameter to the constructor argument (formal parameter), if constructor expressions are used in place of Tuple which this question has nothing to do with after all. This is a long-standing bug in EclipseLink still to be fixed - an analogous example.