[SOLVED] Collation in Hibernate v6 for ORDER BY clause of JPQL

Collation in Hibernate v6 for ORDER BY clause of JPQL

Hibernate has support for collation in v6: https://docs.jboss.org/hibernate/orm/6.0/userguide/html_single/Hibernate_User_Guide.html#hql-string-functions. A lot of the SO questions about collation in hibernate are from before this feature was added to Hibernate. I want to use this in a JPQL query to sort by a column.

The Hibernate 6 user guide references a collate function. Unfortunately, it shows functional examples of complete queries for all the string functions except collate: https://docs.jboss.org/hibernate/orm/6.0/userguide/html_single/Hibernate_User_Guide.html#_collate.

What is shown in the user guide is collate(p.name as collation), whereas the other functions from the same table are called with parameters separated by commas, not the AS keyword. I tried it both ways, and got the following results:

SQL Migration:

CREATE COLLATION display_id_collation (
    provider = icu,
    deterministic = false,
    locale = 'und-u-ka-shifted-kn');

Attempt #1

SELECT new com.test.Result(i, i.customer, pi)
FROM Invoice i
INNER JOIN PaidInvoice pi ON i.id = pi.invoice.id
ORDER BY COLLATE(i.displayId as display_id_collation)

Error:

Caused by: o.s.d.j.r.q.BadJpqlGrammarException: Line 6:35 mismatched input 'as' expecting {',', ')', '+', '-', '/', '||', '[', '.', '*', BY, DAY, EPOCH, HOUR, MINUTE, MONTH, NANOSECOND, QUARTER, SECOND, WEEK, YEAR}; Bad JPQL grammar [...]
        at o.s.d.j.r.q.BadJpqlGrammarErrorListener.syntaxError(BadJpqlGrammarErrorListener.java:39)

Attempt #2

SELECT new com.test.Result(i, i.customer, pi)
FROM Invoice i
INNER JOIN PaidInvoice pi ON i.id = pi.invoice.id
ORDER BY COLLATE(i.displayIdSearch, display_id_collation) ASC

Error:

Caused by: o.h.q.SemanticException: Could not interpret path expression 'display_id_collation'
        at o.h.q.h.i.BasicDotIdentifierConsumer$BaseLocalSequencePart.resolvePathPart(BasicDotIdentifierConsumer.java:255)

Attempt #2.1

SELECT new com.test.Result(i, i.customer, pi)
FROM Invoice i
INNER JOIN PaidInvoice pi ON i.id = pi.invoice.id
ORDER BY COLLATE(i.displayIdSearch, 'display_id_collation') ASC

Error:

Caused by: o.h.q.s.p.f.FunctionArgumentException: Parameter 2 of function 'collate()' has type 'COLLATION', but argument is of type 'java.lang.Object'
        at o.h.q.s.p.f.ArgumentTypesValidator.throwError(ArgumentTypesValidator.java:303)
        at o.h.q.s.p.f.ArgumentTypesValidator.validate(ArgumentTypesValidator.java:120)

The error message from attempt #2.1 seems to suggest it could work if I can pass in an object of type 'COLLATION', but I couldn't find any documentation for how to do that.

Solution

I got a very helpful answer on the Hibernate forums that explained where I was going wrong: https://discourse.hibernate.org/t/how-to-use-collate-in-jpql/9542, so I'm not sure why I'm getting downvoted here.

Apparently there is a difference between HQL and JPQL. Repository methods use JPQL, which does not support collation, but HQL does. In order to use HQL, you need to run your query against the EntityManager directly. For example:

List<Invoice> content =
    entityManager
        .createQuery(
            """
              SELECT i
                FROM Invoice i
                WHERE (:customerId IS NULL OR i.customer.id = :customerId)
                ORDER BY COLLATE(i.displayIdSearch as display_id_collation) ASC
            """,
            Invoice.class)
        .setParameter("customerId", filter.customerId())
        .getResultList();