在JPA条件查询的“具有”子句中使用“ case ... when ... then ... else ... end”构造

Question

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

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

的case...when结构取代null值与0，如果在指定的表达式case子句进行评估以null。

我需要case...when在having子句中使用相同的构造，以便group by可以通过用该构造所计算的值列表中的替换null为来过滤由子句返回的行组。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>和之间的类型不匹配Expression<Object>。

那么case...when，该having子句中如何与该子句具有相同的结构select？

我也尝试过删除Object表达式的泛型类型参数，Expression selectExpression但这样做NullPointerException会引发。

此外，别名（prodId，rating作为中给出）select子句具有在生成的SQL没有影响，因为可以看到。为什么列在这里没有别名？我想念什么吗？

如果列是别名，那么应该可以having像下面这样写子句。

having rating>=0

和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为来过滤由返回的行。0``case...when``select

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

编辑：

我尝试使用以下表达式：

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子句中？

编辑

将表达式类型更改为

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

因此，在 EclipseLink（2.3.2）中 工作时，可以在该having子句中使用它。

对于Hibernate提供程序，NullPoiterExcpetion如果尝试更改的表达式类型selectCase()（Expression<Object>默认返回），则抛出。

更新：

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

Answer 1

这不太可能是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的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()构造中使用表达式时，请始终坚持在上述必要时始终使用文字值。

在Hibernate 4.3.6最终版，Hibernate 5.0.5最终版上进行测试。
稍后，我将尝试在EclipseLink（最终版2.6.1）上运行相同的查询。 不再有古怪之处。

EclipseLink的查询的修改版本完全没有问题，只不过Object如果使用构造函数表达式代替Tuple该问题，则它需要在构造函数参数（形式参数）中使用类型参数。这是EclipseLink中一个长期存在的错误，尚待修复-