jpa case when otherwise end 的用法

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_ratingFROM    product pLEFT OUTER JOIN    rating r        ON p.prod_id=r.prod_idGROUP 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

基于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)

此表rating与另一个表明显多到一的关系product（prod_id是外键引用的主键prod_id的product表）。

在这个问题中，我们只对该子句中的CASE结构感兴趣HAVING。

以下标准查询，

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));}

根据预期生成以下正确的SQL查询。

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

对于技术角度来看，在上述条件查询中查看以下行。

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

这个问题的类似之处如下。

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

在这个问题上看到原来的表达方式是完全一样的：

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

尝试将此表达式传递给criteriaBuilder.greaterThanOrEqualTo()如下。

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

要特别注意greaterThanOrEqualTo()上面的第二个参数。这是0。criteriaBuilder.literal(0)因此，应该是这个问题所提到的例外。

因此，CriteriaBuilder#literal(T value)在必要时始终坚持使用文字值，同时在CriteriaBuilder#selectCase()构造中使用表达式。