在 JPA 标准查询的“having”子句中使用“case...when...then...else...end”构造

以下条件查询计算不同产品组的评分平均值。

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

它生成以下 SQL 查询：

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结构取代null价值观与0，如果指定的表达式case子句被评估为null.

我需要同样的case...when构造在having子句，以便返回的行组group by可以通过替换来过滤子句null with 0在计算值列表中case...when构造，如果有的话。

据此，having子句应该像这样生成

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

这是可能的，如果在greaterThanOrEqualTo()方法，selectExpression代替roundExpression已给出，但这是不可能的。这样做会生成一个编译时错误，指示之间的类型不匹配Expression<Integer> and Expression<Object>.

那么我怎样才能拥有相同的case...when结构中的having条款如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.}

此外，别名（prodId, rating）如给出的select可以看出，子句对生成的 SQL 没有影响。为什么这里的列没有别名？我错过了什么吗？

如果列有别名，那么应该可以写having子句如下。

having rating>=0

and having在条件查询中应如下所示，

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

但由于列在中没有别名select子句，它会抛出异常。

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

有什么办法可以解决这种情况呢？无论如何，返回的行Group by应通过替换来过滤null with 0在产生的值列表中case...when in the select clause.

我正在使用 Hibernate 4.2.7 Final 提供的 JPA 2.0。

EDIT:

我尝试过使用以下表达式：

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

但它导致抛出以下异常：

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)

那么下面的表达式如何工作，

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

两者有相同的类型吗？

有没有办法把case...when结构中的having clause?

EDIT

将表达式类型更改为

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

in EclipseLink (2.3.2)因此，它可以在having条款。

如果是 Hibernate 提供程序，它会抛出NullPoiterExcpetion，如果尝试更改表达式类型selectCase()（返回Expression<Object>默认情况下）。

Update :

这个问题在 Hibernate 5.0.5 Final 中仍然存在。

这不太可能是 Hibernate 中的错误。在构建给定的条件查询时出现技术错误。采用相同的示例，但形式更简单。

假设我们有兴趣生成以下 SQL 查询。

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

基于 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 in the product table).

在这个问题中，我们只关心CASE构造在HAVING clause.

以下条件查询，

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()构造。

_{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.}