How should I use COLLATE in Hibernate Query

Question

I'am trying to use COLLATE statement in a Hibernate SQL query but, it doesn't recognize the statement.

CONSULTA: FROM Articulos WHERE activo=0 
              and (codigodearticulo like '%CIN EMB%' COLLATE='Modern_Spanish_CI_AI'
              or descripcion like '%CIN EMB%' COLLATE='Modern_Spanish_CI_AI'  
              or descripcionadicional like '%CIN EMB%' COLLATE='Modern_Spanish_CI_AI' )
              and codigodelinea in
              (select CODIGODELINEA from Lineas where CATAUTOPARTES='1')

And when app compile, Hibernate return this exception :

- line 1:107: unexpected token: COLLATE
- line 1:107: unexpected token: COLLATE
- line 1:107: unexpected token: COLLATE
- Error hibernate: unexpected token: COLLATE near line 1, column 107

I can't find the problem, in MSSQL Server it works fine.

score 3 · Answer 1 · edited May 23 '17 at 12:06

3

Unfortunately HQL isn't a complete replacement for SQL, and doesn't seem to allow specifying Collation at all.

It can be specified on a Criteria query. See this answer

edited May 23 '17 at 12:06

Community

1
1

answered Jul 16 '13 at 20:56

Matt the Big

70
7

score 0 · Answer 2 · answered Sep 17 '21 at 12:10

Instead of having in HQL:

String hql = "SELECT e" +
    " FROM EntityJPA e" +
    " WHERE e.mycolumn COLLATE 'utf8_bin' = 'VALUE'"

Query query = entityManager.createQuery(hql);

You can use a Native Query like this

String sql = "SELECT t" +
    " FROM entity_table t" +
    " WHERE t.mycolumn COLLATE 'utf8_bin' = 'VALUE'"

and execute it a a Native Query:

Query query = entityManager.createNativeQuery(sql);

This was the simpler solution by keeping in mind that native query create a dependency on the SQL language of the target database (mySQL in our case).

For us this was still acceptable as the possibility to use a different DB Engine is very low.

How should I use COLLATE in Hibernate Query

2 Answers2