在JPA条件查询的'having'子句中使用'case ... when ... then ... else ... end'构造 [英] Using the 'case...when...then...else...end' construct in the 'having' clause in JPA criteria query
问题描述
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）;
表达式< Number> quotExpression = criteriaBuilder.quot（criteriaBuilder.sum（join.get（Rating_.ratingNum）），criteriaBuilder.count（join.get（Rating_.ratingNum）））;
表达式<整数> roundExpression = criteriaBuilder.function（round，Integer.class，quotExpression）;
表达式< 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
当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
0 替换 null 时，如果指定表达式 case 子句的计算结果为 null 。
我需要相同的 因此，应该生成子句 如果在 那么如何在 像 此外，别名（ 如果列是别名，那么应该可以编写 和 但是在 解决这种情况的方法是什么？无论如何，应该通过用 我使用由Hibernate 4.2.7 final提供的JPA 2.0。 编辑： 我曾尝试过以下表达式： 但引发以下异常： 下面的表达式如何工作呢， p> 两者都有相同的类型？ 当 编辑 将表达式类型更改为 p> 可用于可在 在Hibernate提供程序的情况下，如果尝试更改表达式类型，它会引发 更新： 这个问题在Hibernate 5.0.5 final 。 这不太可能是Hibernate中的一个错误。制作标准查询时出现技术错误。假设我们有兴趣生成以下SQL查询。 基于MySQL中的下表。 此表 在这个问题中，我们只关心 以下标准查询 p> 按预期生成以下正确的SQL查询。 对于技术角度来看，在上面的条件查询中查看以下行。 问题中的类似线条如下所示。 查看问题中的原始表达式，完成相同的操作： 这个表达式试图传递给 特别注意第二个参数<情况...当构造在具有子句，以便可以通过替换 null来过滤子句由子句返回的行组在以上。它是情况下计算的值列表中与构造，如果有的话。 0 ... when
< pre-class =lang-sql prettyprint-override> HAVING
（CASE
当Sum（ratingset1_.rating_num）/ Count（ratingset1_.rating_num）是
NULL THEN 0
ELSE Round（sum（ratingset1_.rating_num）/ Count（ratingset1_.rating_num））
END）> = 0
greaterThanOrEqualTo（）方法中， selectExpression 而不是 roundExpression ，但这是不可能的。这样做会产生一个编译时错误，指出 Expression< Integer> 和 Expression< Object> 之间的类型不匹配。 / p>
结构中具有 / code>子句，如选择子句？
表达式selectExpression 这样的表达式的泛型类型参数 Object ，但这样做导致 NullPointerException
$ b ）将被抛出。在， select 子句中给出的c> prodId rating ）在生成的SQL可以看出。为什么列在这里不是别名？我错过了什么？
具有子句，就像
评分> = 0
在标准查询中有应该如下，
<$ p $ （join。< Integer> get（rating），0））;
中没有别名，请选择子句，它会抛出一个异常。
java.lang.IllegalArgumentException：无法解析path [null]的属性[rating]
0替换时产生的值列表中的code> p> null 来过滤由 Group by 返回的行。 code> case ...在选择子句中
表达式<整数> selectExpression = criteriaBuilder。< Integer> selectCase（）
.when（quotExpression.isNull（），0）
。<整数>否则（roundExpression）;
引发：java.lang.NullPointerException $ java.util.Class.isAssignableFrom上的b $ b（本地方法）
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）
表达式<整数> roundExpression = criteriaBuilder
.function（round，Integer.class，quotExpression）;
结构存在子句时，是否有方法可以将 case ... 结构？
表达式<整数> selectExpression = criteriaBuilder
。< Integer> selectCase（）
.when（quotExpression.isNull（），0）
。< Integer> otherwise（roundExpression）;因此，EclipseLink（2.3.2）中的
中有子句。
NullPoiterExcpetion selectCase（）（默认返回表达式< Object> ）。
SELECT
p.prod_id，
p.prod_name，
CASE
当sum（r。 （总数（r.rating_num）/计数（DISTINCT r.rating_id））
END AS avg_rating
FROM
产品p
LEFT OUTER JOIN
等级r
ON p.prod_id = r.prod_id
GROUP BY
p.prod_id，
p.prod_name
HAVING
CASE
当sum（r.rating_num）/ count（DISTINCT r.rating_id）是NULL THEN 0
ELSE round（sum（r.rating_num）/ count（DISTINCT r.rating_id））
END> = 1
mysql>降级评级;
+ ------------- + --------------------- + ------ + - --- + --------- + ---------------- +
|字段|类型|空| Key |默认|额外|
+ ------------- + --------------------- + ------ + - --- + --------- + ---------------- +
| rating_id | bigint（20）unsigned | NO | PRI | NULL | auto_increment |
| prod_id | bigint（20）unsigned |是| MUL | NULL | |
| rating_num | int（10）unsigned |是| | NULL | |
| ip_address | varchar（45）|是| | NULL | |
| row_version | bigint（20）unsigned | NO | | 0 | |
+ ------------- + --------------------- + ------ + - --- + --------- + ---------------- +
5行（0.08秒）
评级与另一个表具有明显的多对一关系 product （ prod_id 是引用主键的外键 prod_id 在 product 表中）。
CASE 构造在 HAVING 子句中。
CriteriaBuilder criteriaBuilder = entityManager.getCriteriaBuilder（）;
CriteriaQuery< Tuple> criteriaQuery = criteriaBuilder.createTupleQuery（）;
Root<产品> root = criteriaQuery.from（entityManager.getMetamodel（）。entity（Product.class））;
ListJoin< Product，Rating> prodRatingJoin = root.join（Product_.ratingList，JoinType.LEFT）;
List< Expression<>>表达式= new ArrayList< Expression<>>（）;
expressions.add（root.get（Product_.prodId））;
expressions.add（root.get（Product_.prodName））;
表达式<整数> sum = criteriaBuilder.sum（prodRatingJoin.get（Rating_.ratingNum））;
表达式< Long> count = criteriaBuilder.countDistinct（prodRatingJoin.get（Rating_.ratingId））;
表达式< Number> quotExpression = criteriaBuilder.quot（sum，count）;
表达式<整数> roundExpression = criteriaBuilder.function（round，Integer.class，quotExpression）;
表达式<整数> 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（）; （元组元组：列表）{
System.out.println（tuple.get（0）+：+tuple.get（1）+：+ tuple）
。得到（2））;
}
select
product0_.prod_id as col_0_0_，
product0_.prod_name as col_1_0_，
case
当sum（ratinglist1_.rating_num）/ count（distinct ratinglist1_.rating_id）为空然后0
else round（sum（ratinglist__rating_rate）/ 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
具有
的情况下
当sum（ratinglist1_.rating_num）/ count（distinct ratinglist1_.rating_id）为null时，则为0
其他轮（总和（ratinglist1_.rating_num）/计数（distinct ratinglist1_.rating_id））
结束> = 1
criteriaQuery.having（criteriaBuilder.greaterThanOrEqualTo（selectExpression， criteriaBuilder.literal（1）））;
createQuery.having（criteriaBuilder.greaterThanOrEqualTo（selectExpression，1））;
表达式<整数> selectExpression = criteriaBuilder。< Integer> selectCase（）
.when（quotExpression.isNull（），0）
。<整数>否则（roundExpression）;
criteriaBuilder.greaterThanOrEqualTo（） code>，如下所示。
criteriaQuery.having（criteriaBuilder.greaterThanOrEqualTo（selectExpression，0））;
0 。它应该是 criteriaBuilder.literal（0），因此，问题中提到了异常。
因此，在 测试Hibernate 4.3.6 final，Hibernate 5.0.5 final或者。 对于查询的修改版本，EclipseLink没有任何问题，只是它如果使用构造函数表达式来代替 The following criteria query calculates the average of rating of different groups of products. It generates the following SQL query : The I need the same Accordingly, the It could be possible, if in the So how can I have the same I have also tried by removing the generic type parameter Moreover, alias names ( If columns are aliased then, it should be possible to write the and but as columns are not aliased in the
What is the way to get around this situation? Anyway, the rows returned by I'm using JPA 2.0 provided by Hibernate 4.2.7 final. EDIT: I have tried with the following expression : but it caused the following exception to be thrown : How can the following expression work then, both have the same type? Is there a way to put the EDIT Changing the expression type to in EclipseLink (2.3.2) works hence, it can be made available in the In case of Hibernate provider, it throws the Update : This issue still persists in Hibernate 5.0.5 final. 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. Based on the following table in MySQL. This table In this question, we are only interested in the The following criteria query, Generates the following correct SQL query as expected. For the technical perspective, look at the following line in the above criteria query. Its analogous line in the question was written like following. See the original expression in the question doing the exact same thing : This expression was attempted to be passed to Pay special attention to the second parameter to Thus, always insist upon using Tested on Hibernate 4.3.6 final, Hibernate 5.0.5 final alternatively. EclipseLink has no problem at all with the modified version of the query except that it requires an 这篇关于在JPA条件查询的'having'子句中使用'case ... when ... then ... else ... end'构造的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！ CriteriaBuilder＃selectCase（）中使用表达式时，始终坚持使用 CriteriaBuilder＃literal（T value） / code>构造。
我将尝试稍后在EclipseLink（2.6.1 final）上运行相同的查询。不应该有一个怪癖。
Tuple ，则需要构造函数参数（形参）需要一个 Object参数毕竟这个问题与这个问题无关。这是EclipseLink中一个长期存在的bug，仍然需要修复 - 一个类似的例子。 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();
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 structure replaces null values with 0, if the specified expression in the case clause is evaluated to null.case...when construct in the having clause so that the group of rows returned by the group by clause can be filtered by replacing null with 0 in the list of values calculated by the case...when construct, if any. 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
greaterThanOrEqualTo() method, selectExpression instead of roundExpression is given but it is not possible. Doing so, generates a compile-time error indicating type mismatch between Expression<Integer> and Expression<Object>.case...when structure in the having clause as in the select clause?Object of the expression like Expression selectExpression but doing so, caused the NullPointerException to be thrown.
prodId, rating) as given in the select clause have no effect in the generated SQL as can be seen. Why columns are not aliased here? Am I missing something?having clause just like follows.having rating>=0
having in the criteria query should be as follows,criteriaQuery.having(criteriaBuilder.greaterThanOrEqualTo(join.<Integer>get("rating"), 0));
select clause, it throws an exception.java.lang.IllegalArgumentException: Unable to resolve attribute [rating] against path [null]
Group by should be filtered by replacing null with 0 in the list of values produced by case...when in the select clause.
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 structure in the having clause?
Expression<Integer> selectExpression = criteriaBuilder
.<Integer>selectCase()
.when(quotExpression.isNull(), 0)
.<Integer>otherwise(roundExpression);
having clause. NullPoiterExcpetion, if an attempt is made to change the expression type of selectCase() (which returns Expression<Object> by default).
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> 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 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).CASE construct in the 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));
}
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() as follows.criteriaQuery.having(criteriaBuilder.greaterThanOrEqualTo(selectExpression, 0));
greaterThanOrEqualTo() above. It is 0. It should have been criteriaBuilder.literal(0) instead hence, the exception as mentioned in the question.CriteriaBuilder#literal(T value) for literal values whenever necessary as done above while using expressions in the CriteriaBuilder#selectCase() construct.
I will try to run the same query on EclipseLink (2.6.1 final) later on. There should not be a quirk anymore.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.
