Search code examples
springhibernatespring-dataspring-data-jpajpql

Spring data: CONCAT are removing commas when used with TRANSLATE

I'm trying to perform the following query:

SELECT p FROM Pessoa p WHERE TRANSLATE(UPPER(p.nome), 'ÂÁÀÄÃÊÉÈËÎÍÌÏÔÓÒÖÕÛÚÙÜÇ', 'AAAAAEEEEIIIIOOOOOUUUUC') LIKE CONCAT('%',TRANSLATE(UPPER(?1), 'ÂÁÀÄÃÊÉÈËÎÍÌÏÔÓÒÖÕÛÚÙÜÇ', 'AAAAAEEEEIIIIOOOOOUUUUC'),'%')

But i realize that hibernate is generating the following final SQL:

... where TRANSLATE(upper(pessoa0_.ds_nome), 'ÂÁÀÄÃÊÉÈËÎÍÌÏÔÓÒÖÕÛÚÙÜÇ', 'AAAAAEEEEIIIIOOOOOUUUUC') like ('%'||TRANSLATE(upper(?)||'ÂÁÀÄÃÊÉÈËÎÍÌÏÔÓÒÖÕÛÚÙÜÇ'||'AAAAAEEEEIIIIOOOOOUUUUC')||'%')...

Note that the commas inside the TRANSLATE function were replaced by || which leads to an org.postgresql.util.PSQLException: ERROR: function translate(text) does not exist

What am I doing wrong?

Spring: 4.3.4.RELEASE

Spring data: spring-data-jpa:1.10.5.RELEASE

Solution

  • Hibernate is not simply replacing commas. It is converting a JPQL (or possibly HQL) query int SQL. The double pipes || are a common SQL syntax for the JPQL CONCAT function.

    If it doesn't work for your Postgres DB you are probably using the wrong dialect. Configure Hibernate to use the correct Dialect for your database.

    See for example this article for how to do that.