How to prevent Hibernate 6 from auto-escaping backslashes in a LIKE query via Criteria API?

Question

I'm migrating a Spring Boot 2.7 application to 3.0 which upgrades Hibernate from 5.6 to 6.1 and I'm experiencing an issue with JPA's Criteria API creating a SElECT ... LIKE query.

Given the following JPA Predicate:

String searchString = "search string with escaped special chars \\% and \\_";
criteriaBuilder.like(criteriaBuilder.lower(root.get("myColumn")), searchString)

A query like the following is generated in Hibernate 5.6 / Spring Boot 2.7:

SELECT ...
FROM ...
WHERE lower(myentity0_.`MyColumn`) like ?

While Hibernate 6.1 / Spring Boot 3.0 generate a query like:

SELECT ...
FROM ...
WHERE lower(m1_0.`MyColumn`) like replace(?, '\\', '\\\\')

I'm using MariaDB 10.6.

Unfortunately this behaviour breaks my application, because I do some preprocessing on my search string which includes escaping the special characters % and _ with \% and \_ in order to be able to search for them. The backslashes are then double-escaped by Hibernate which unescapes % and _.

I figured out a workaround which uses a different escape character like # in my search string and propagates this one to the query via

criteriaBuilder.like(criteriaBuilder.lower(root.get("myColumn")), searchString, '#')

but that feels inconvenient.

Is there a possibility to prevent Hibernate from auto-escaping the search string? Unfortunately I didn't find this behaviour mentioned in the migration guide.

Answer 1

This was done as part of https://hibernate.atlassian.net/browse/HHH-15736 and makes sure that the escaping behavior is consistent across all databases. If you want \ to be your escape char, then configure it as such by using

criteriaBuilder.like(criteriaBuilder.lower(root.get("myColumn")), searchString, '\\')