Mit den 'Fall zu sein...wenn...dann...sonst...Ende' Konstrukt im "having" - Klausel in der JPA criteria-query

Dies ist sehr unwahrscheinlich, dass ein bug in Hibernate. Es gab einen technischen Fehler bei der Herstellung die Kriterien der Abfrage gegeben. Nehmen das gleiche Beispiel, aber in einer einfacheren form.

Lassen Sie uns davon ausgehen, dass wir daran interessiert sind, erzeugen die folgenden SQL-Abfrage.

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

Basierend auf der folgenden Tabelle 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)

Dieser Tabelle rating hat offensichtlich viele-zu-eins-Beziehung mit einer anderen Tabelle product (prod_id ist der Fremdschlüssel verweisen auf Primärschlüssel prod_id im product Tabelle).

In dieser Frage sind wir nur daran interessiert, die CASE Konstrukt in der HAVING - Klausel.

Den folgenden Kriterien Abfrage,

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

Generiert die folgende korrekte SQL-Abfrage wie erwartet.

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

Für die technische Perspektive, betrachten Sie die folgende Zeile in die oben genannten Kriterien Abfrage.

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

Ihre analogen line-in der Frage geschrieben wurde, wie die folgenden.

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

Sehen, die ursprüngliche Ausdruck in der Frage um genau das gleiche :

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

Dieser Ausdruck wurde versucht zu übergeben criteriaBuilder.greaterThanOrEqualTo() wie folgt.

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

Besondere Aufmerksamkeit widmen, um den zweiten parameter zu greaterThanOrEqualTo() oben. Es ist 0. Es sollte criteriaBuilder.literal(0) statt daher die Ausnahme, wie erwähnt, in Frage stellen.

Also, immer darauf bestehen, auf die Verwendung CriteriaBuilder#literal(T value) für Literale Werte, wenn notwendig, wie oben, während die Verwendung von Ausdrücken in der CriteriaBuilder#selectCase() konstruieren.

_{Getestet auf Hibernate 4.3.6 abschließende, Hibernate 5.0.5 Letzte alternativ. ich werde versuchen zum ausführen der gleichen Abfrage auf EclipseLink (2.6.1 final) später auf. Sollte es nicht eine Macke mehr.}

_{EclipseLink hat überhaupt kein problem mit der modifizierten version der Abfrage, außer dass es erfordert eine Object geben Sie parameter an den Konstruktor-argument (formale parameter), wenn Konstruktor-Ausdrücke verwendet werden, anstelle von Tuple dem diese Frage hat nichts damit zu tun, nachdem alle. Dies ist eine langjährige Bugs in EclipseLink noch behoben werden - ein analoges Beispiel.}

InformationsquelleAutor Tiny