ORDER BY in Criteria API for a computed column name (by alias)

Question

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

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

If I do orderby root.CARD_NUM, the attribute is not present in Root.- Throws exception.
If I do order by the caseSelect expression itself it throws an error stating CARD_ID is not in SELECTED part of query.
I cannot have card_num / card_id in select expression, that is not right for the query.

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

Any help is appreciated.

score 4 · Answer 1 · answered Jan 21 '21 at 22:40

4

I faced a similar situation...

query.multiselect(root, computedColumn);
query.orderBy(new Order[]{filterDTO.getSortAsc() ? cb.asc(cb.literal(2)) : cb.desc(cb.literal(2))});

I my case computedColumn is Subquery...I did not manage to make it work by column alias but it seems to work by column index returned in the tupple so I guess in your code it should work by index 1

query.orderBy(cb.asc(cb.literal(1)));

answered Jan 21 '21 at 22:40

Isaac Pindado

81
1
3

That only works if hibernate.criteria.literal_handling_mode is set to INLINE or AUTO. If it is BIND then it will generate SQL with a bind variable, which is not allowed in ORDER BY clauses. The default is AUTO, which is probably why it worked for you. – Murray Wilson Jun 29 '22 at 21:47
AUTO creates bind variables for strings and inserts numeric literals into the code, which is probably why using the column alias didn't work. – Murray Wilson Jun 29 '22 at 21:53

score 2 · Answer 2 · answered Sep 16 '20 at 04:53

2

Using the Criteria API, you need to order by the caseSelect expression. I gave it a try and it works fine with Hibernate 5.4. Which version do you use?

answered Sep 16 '20 at 04:53

Thorben Janssen

3,012
9
23

score 1 · Answer 3 · answered Jul 27 '20 at 07:22

1

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

answered Jul 27 '20 at 07:22

Christian Beikov

15,141
2
32
58

score 0 · Accepted Answer · answered Jul 30 '20 at 19:27

This is fairly not possible and just feels like a case missed by JPA. Though if using hibernate API it is possible. But, my workaround was -

Created a view which would contain the case expression.
Join the view with my entity (you cannot do a join, but one more query.from(View.class)).
In the where add the ids of View and the entity.

Now in the order by you could mention the column name by View.column_name.

ORDER BY in Criteria API for a computed column name (by alias)

4 Answers4

Linked