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.