In the past it was so that in order to search some substring from String column one could use UPPER(columnName) LIKE UPPER(:parameterKey), where parameterKey had as parameterValue% + escaped substring + %.
For escaping one had to use backslashes. org.springframework.data.jpa.repository.query.EscapeCharacter.DEFAULT.escape(value) currently works nicely for escaping substrings.
Unfortunately in Hibernate 6.4.1 this old behavior doesn’t work anymore (both with PostgreSQL and H2).
If parameterKey is %\\\%\_% (% + escaped "\%_" + %), then row with columnNameabc\%_def won’t be found (incorrect behavior).
When I add ESCAPE '\' (written in Java as '\\') after LIKE expression (UPPER(columnName) LIKE UPPER(:parameterKey) ESCAPE '\'), then it works properly.
This seems redundant, because '\' is default escape character anyway, so why do I have to specify it every time for LIKE expression? It is also very easy to forget to specify this, which leads to behavior, that might seem correct at first, but gives incorrect results in some cases. I would prefer if old behavior is restored either by default or at least be made globally configurable.
It is not the default escape character. By default, there is no escape character according to the SQL spec. Only a few databases choose to use \ as default escape character and Hibernate ORM tries to standardize a few things like that for the sake of portability.
It’s always best to make this explicit in your queries to avoid surprises.
I am still not fond of this change. In the past in Hibernate it was and in the present in native PostgreSQL/H2 it is possible to write LIKE clauses by using default escape character \ without needing to write ESCAPE '\' after every LIKE clause.
It would make more sense if I would want to use different escape characters in the same application, but instead I want to always use the default escape character of the database that my application uses. Having to constantly specify the same escape character for every LIKE clause and facing buggy behavior if I forget to use it (likely to happen as I am used to not needing to specify them in native SQL) is unnecessary hindrance IMO.
Currently my backup solution would be to define QueryUtil.likeClause(columnName, parameterKey) method that would output String “UPPER(${columnName}) LIKE UPPER(:${parameterKey}) ESCAPE '\'” and use that method for every LIKE clause, but IMO that is not optimal.
Could you please revert back to using a default native escape character of database and add a Hibernate property, that would override that character? People could make porting easier by using that override property. This way I could continue to write LIKE clauses in JPQL queries similarly to native SQL.
but instead I want to always use the default escape character of the database that my application uses.
I find it more confusing that the same HQL behaves differently depending on the used database. So here we have two opinions.
Could you please revert back to using a default native escape character of database and add a Hibernate property, that would override that character? People could make porting easier by using that override property. This way I could continue to write LIKE clauses in JPQL queries similarly to native SQL.
No. It was a deliberate decision to align with the SQL standard. Just write out the escape character you want to use in your queries and start thinking about prefixing special characters % and ? in user supplied values with that escape character to avoid potential performance issues due to full table scans.
HQL would behave the same, only the escape character would be different. Also the default escape character would be easy to set to the same value for every database with Hibernate property (for example hibernate.dialect.escape_character=\).
I will not write it out in every LIKE clause either way. If I can’t rely on non-empty default value and can’t set it with global property, then I will use the QueryUtil.likeClause(columnName, parameterKey) method that I described earlier. I would just prefer to not have to be forced to go that route due to empty default escape character.
It should have been obvious so far that I am already very well aware of the need to escape wildcards in LIKE clauses. I am not afraid of full table scans anyway due to PostgreSQL trigram index.