Why hibernate query language does not support collation in HQL?

Question

I am going to sort a table data by a column using database collation.

SELECT *
    FROM country
    ORDER BY code COLLATE utf8_swedish_ci DESC;

But when I am trying to get table data with HQL Its getting exception because HQL does not support this feature. So is there any way to do it same as sql? And although hibernate does not support all sql features but may be there is a logical reason for not supporting collate feature. Why hibernate HQL does not support collate feature in query level?

yes I can do it but my all existing code is with hibernate query or criteria query Thats why I want to do it with HQL. — flopcoder, Oct 01 '19 at 09:27
Hibernate does not support all features that SQL query does. So that you can consider to use native SQL. — Rowi, Oct 01 '19 at 09:29
@flopcoder apparently it does work with [Criteria queries](https://stackoverflow.com/questions/11030921/case-sensitive-search-in-hibernate-criteria/16996115#16996115) — Kayaman, Oct 01 '19 at 09:30
@Kayaman Yes I can. But when its come to a join query can we use this link? — flopcoder, Oct 01 '19 at 09:38
@Kayaman suppose I have a Country and People table I will fetch data using both table. — flopcoder, Oct 01 '19 at 09:43
Ok i will not do it No problem. But in my question I have another part of question that why HQL does not support this. — flopcoder, Oct 01 '19 at 09:46
Because HQL is about objects not about SQL. Not every SQL feature has a corresponding HQL feature and HQL (as JPQL) supports the set of features shared amongst all databases. — M. Deinum, Oct 01 '19 at 09:58

score 0 · Answer 1 · answered Oct 02 '19 at 11:25

You can do it with FluentJPA:

public List<Country> getCountries() {
    FluentQuery query = FluentJPA.SQL((Country country) -> {

        SELECT(country);
        FROM(country);
        ORDER(BY(COLLATE(country.getCode(), "utf8_swedish_ci")).DESC());
    });

    return query.createQuery(em, Country.class).getListResult();
}

score 0 · Answer 2 · answered Oct 02 '19 at 12:10

We can do it by override renderOrderByElement method of database dialect

 @Override
    public String renderOrderByElement(String expression, String collation, String order, NullPrecedence nulls)
    {
        expression = (new StringBuilder(expression)).append(" collate  'utf8_swedish_ci'").toString();
        return super.renderOrderByElement(expression, collation, order, nulls);
    }

Why hibernate query language does not support collation in HQL?

2 Answers2