本文介绍了Criteria API中的ORDER BY用于计算的列名(按别名)的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

遇到我的Java代码具有符号查询的情况-

Having a situation where my java code is symbolic to query -

  SELECT CUSTOMER_ID,
         CUSTOMER_NAME,
         CASE
             WHEN COUNT (DISTINCT CARD_ID) > 1 THEN 'MULTIPLE'
             ELSE MAX(CARD_NUM)
         END    AS CARD_NUM
    FROM CUSTOMER LEFT JOIN CARD ON CARD.CUSTOMER_ID = CUSTOMER.CUSTOMER_ID
GROUP BY CUSTOMER_ID, CUSTOMER_NAME

有关详细信息的Java代码-

Java code for detailed info -

CriteriaBuilder cb = em.getCriteriaBuilder();
final CriteriaQuery<Tuple> query = cb.createQuery(Tuple.class);
final Root<Customer> root = query.from(Customer.class);
Expression<Object> caseSelect = cb.selectCase()
        .when(cb.greaterThan(cb.countDistinct(join.get(Card_.cardId)), 1L), "MULTIPLE")
        .otherwise(cb.greatest(Card_.get(Card_.cardNum)));
caseSelect.alias("card_num");
selects.add(caseSelect);
query.multiselect(selects).distinct(true);
query.groupBy(exprs);
query.orderBy(cb.asc(caseSelect));

现在,如何在Criteria API中执行订购。

Now, how to do the order by in Criteria API.


  1. 如果我通过root.CARD_NUM执行订购,则Root中不存在该属性。-引发异常。

  2. 如果我通过caseSelect表达式本身进行订购,则会引发错误
    说明CARD_ID不在查询的SELECTED部分中。

  3. 我不能在选择表达式中使用card_num / card_id,这不适合查询。

是否可以通过别名来订购?我看到Order是Expression类型,以及如何从字符串名称获取表达式。我想您可以在Hibernate中做到这一点。无论如何,是否可以在条件API中使用休眠orderby?猜出一个愚蠢的q

Any way to just order by Alias name? I see Order is Expression type, and how to get an expression from string name. I guess you can do this in Hibernate. Would it be possible to use hibernate orderby in criteria API anyways ? Guess a stupid q

任何帮助都是值得的。

推荐答案

看来这不是通过JPA Criteria API可以实现,并且您将不得不转而使用JPQL / HQL。

It seems this is not possible with the JPA Criteria API and you will have to fallback to using JPQL/HQL instead.

这篇关于Criteria API中的ORDER BY用于计算的列名(按别名)的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-02 14:17