Using the 'case...when...then...else...end' construct in the 'having' clause in JPA criteria query
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.
Tiny
Just an orphan kid and have no more to say. Three things in general, cannot be avoided (at least I can never) Mother Mother-tongue Mother-land. They are always unique. I'm a family-less boy. My family was hunted leaving me all alone when my house targeted and deliberately set on a fire by a mob during a nonsense communal riot but I was survived by a rescue team with the help of firemen. As a survival, I didn't know whether it was my fortune or misfortune but when I recovered, the rescue team came to my home, one day. One of the members gave me a piece of paper in my hand in which the following text was written. lifeisnowhere. He asked me to read it carefully and I could hardly interpret the text as Life is now here, instead of Life is nowhere. All of them gave me a cute smile and went away and I decided to live peacefully and hopefully on their saying from then onwards and very soon. Because of this tragedy, I'm alone couldn't join a school but a curiosity to learn something made me a self-learner. I'm indeed a self-learner, so I'm likely not able to answer any questions on this site right now. In the field of computer science, my self-study mainly includes, QBASIC, C, C++, C#, VB, Java, JavaScript, PHP and a little about ASP.NET. Oracle, MySQL and MSSQL-Server with DBMS. and other theoretical subjects. I'm currently dealing with - Android and Java EE including Servlet, JSP-JSTL/EL (with Spring and Struts with ORM models JPA/Hibernate) and JSF.
Updated on June 23, 2022Comments
-
Tiny almost 2 years
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 replacesnull
values with0
, if the specified expression in thecase
clause is evaluated tonull
.I need the same
case...when
construct in thehaving
clause so that the group of rows returned by thegroup by
clause can be filtered by replacingnull
with0
in the list of values calculated by thecase...when
construct, if any.Accordingly, the
having
clause should be generated likeHAVING (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 ofroundExpression
is given but it is not possible. Doing so, generates a compile-time error indicating type mismatch betweenExpression<Integer>
andExpression<Object>
.So how can I have the same
case...when
structure in thehaving
clause as in theselect
clause?I have also tried by removing the generic type parameter
Object
of the expression likeExpression selectExpression
but doing so, caused theNullPointerException
to be thrown.
Moreover, alias names (
prodId
,rating
) as given in theselect
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 replacingnull
with0
in the list of values produced bycase...when
in theselect
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 thehaving
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 ofselectCase()
(which returnsExpression<Object>
by default).
Update :
This issue still persists in Hibernate 5.0.5 final.