Using "case ... when ... then ... else ... end" in the "having" expression in a JPA criteria request

Question

The next criteria request calculates the average rating of various product groups.

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 0 if the specified expression in the case expression evaluates to null .

I need to have the same case...when construct in the having so that the group of rows returned by the group by clause can be filtered out by replacing null with 0 in the list of values ​​computed by case...when to build, if any.

Accordingly, a having should be generated as

 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 is possible if the greaterThanOrEqualTo() method specifies selectExpression instead of roundExpression , but this is not possible. This generates a compile-time error indicating a type mismatch between Expression<Integer> and Expression<Object> .

So, how can I have the same case...when structure in the having as in the select clause?

_{I also tried to remove a parameter of the type Object type of an expression of type Expression selectExpression , but a NullPointerException is NullPointerException .}

---

In addition, the alias names ( prodId , rating ), as indicated in the select clause, have no effect in the generated SQL, as can be seen. Why are the columns here not aliases? Did I miss something?

If the columns have an alias, then it should be possible to write a having , as shown below.

 having rating>=0 

and having in the query criteria should be as follows:

 criteriaQuery.having(criteriaBuilder.greaterThanOrEqualTo(join.<Integer>get("rating"), 0)); 

but since the columns are not flattened in the select clause, it throws an exception.

 java.lang.IllegalArgumentException: Unable to resolve attribute [rating] against path [null] 

---

What is the way around this? In any case, the rows returned by group by should be filtered out, replacing null with 0 in the list of values ​​created by case...when in the select clause.

---

I am using JPA 2.0 provided by the final version of Hibernate 4.2.7.

---

EDIT:

I tried with the following expression:

 Expression<Integer> selectExpression = criteriaBuilder.<Integer>selectCase() .when(quotExpression.isNull(), 0) .<Integer>otherwise(roundExpression); 

but this caused the following exception:

 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?

 Expression<Integer> roundExpression = criteriaBuilder .function("round", Integer.class, quotExpression); 

both have the same type?

Is there a way to place a case...when structure in a having ?

---

EDIT

Change expression type to

 Expression<Integer> selectExpression = criteriaBuilder .<Integer>selectCase() .when(quotExpression.isNull(), 0) .<Integer>otherwise(roundExpression); 

works in EclipseLink (2.3.2) , so it can be accessed in the having .

In the case of the Hibernate provider, it throws NullPoiterExcpetion if an attempt is made to change the type of the selectCase() expression (which returns the default Expression<Object> ).

---

Update:

This issue still persists in the final of Hibernate 5.0.5.

Answer 1

This is unlikely to be a mistake in Hibernate. There was a technical error in making the requested criterion. Taking the same example, but in a simpler form.

Suppose 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 rating table has an obvious multi-valued relationship with another product table ( prod_id is a foreign key that refers to the primary key prod_id in the product table).

In this matter, we are only interested in the CASE construct in the HAVING .

The following criteria request,

 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 valid 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 a technical perspective, look at the next line in the criteria query above.

 criteriaQuery.having(criteriaBuilder.greaterThanOrEqualTo(selectExpression, criteriaBuilder.literal(1))); 

His similar line in the question was written as follows.

 createQuery.having(criteriaBuilder.greaterThanOrEqualTo(selectExpression, 1)); 

Look at the original expression in the question by doing the same:

 Expression<Integer> selectExpression = criteriaBuilder.<Integer>selectCase() .when(quotExpression.isNull(), 0) .<Integer>otherwise(roundExpression); 

This expression was sent to criteriaBuilder.greaterThanOrEqualTo() as follows.

 criteriaQuery.having(criteriaBuilder.greaterThanOrEqualTo(selectExpression, 0)); 

Pay particular attention to the second parameter at greaterThanOrEqualTo() above. This is 0 . Therefore, this should be criteriaBuilder.literal(0) , hence the exception mentioned in the question.

Thus, always insist on using CriteriaBuilder#literal(T value) for literal values ​​when necessary, as done above, when using expressions in the CriteriaBuilder#selectCase() construct.

---

_{Tested in Hibernate 4.3.6 final, Hibernate 5.0.5 final alternatively. I will try to run the same request on EclipseLink (end of 2.6.1) later. There should be no more quirks.}

_{EclipseLink has no problems with the modified version of the request, except that for the constructor argument (formal parameter), an Object type parameter is required if constructor expressions are used instead of Tuple , which in the end is irrelevant. This is a long-standing bug in EclipseLink that has not yet been fixed - a similar example .}