How to use collate in JPQL

Hibernate ORM
1

I want to use collation in JPQL, and it seems like it should work based on the Hibernate User Guide. Collate is listed as a string function, but it’s also the only string function that doesn’t get a full working example: Hibernate ORM 6.0.0.CR1 User Guide.

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.

2

Hello, collate() is an Hibernate HQL custom function and is not supported by the JPA standard JPQL language.

The correct syntax is the first one you used: COLLATE(i.displayId as display_id_collation). I’m not sure which other framework is causing the validation error, but BadJpqlGrammarException is not thrown by Hibernate. I suggest creating the query through the standard org.hibernate.Session APIs.

4

Thank you! This worked for me:

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