Mariadb 10.5.x
Spring boot 3.3.6 (also tested with 3.4.0)
Hibernate 6.5.3.Final (also tested with 6.6)
Hi
I am using spring boot and in the process of upgrading spring boot and moving to flyway migration I “suddenly” get a SQLSyntaxErrorException when doing “like” queries. I can not reproduce what the reason was, I tried to downgrade/upgrade various dependencies, mariadb client, hibernate, spring boot but I now have this error persistent.
Here is the query, really no magic here:
Hibernate:
select
b1_0.id,
b1_0.address,
b1_0.created_at,
b1_0.description,
b1_0.last_modified_at,
b1_0.name,
b1_0.vat
from
buildings b1_0
where
lower(b1_0.address) like ? escape '\'
or lower(b1_0.description) like ? escape '\'
or lower(b1_0.name) like ? escape '\'
or b1_0.id=?
order by
b1_0.name
offset
? rows
fetch
first ? rows only
Exception:
java.sql.SQLSyntaxErrorException: (conn=3161) You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '\' or lower(b1_0.name) like ? escape '\' or b1_0.id=? order by b1_0.name offs...' at line 1
After searching the web, I found various topics about the “escape slash in like queries” but nothing really helped to get around this issue. After a while I used old configs and suddently the error disappeared. It seems that setting the MariaDBDialect
workarounds this issue.
jpa:
properties:
hibernate:
dialect: org.hibernate.dialect.MariaDBDialect
After this setting, the query looks like:
Hibernate:
select
b1_0.id,
b1_0.address,
b1_0.created_at,
b1_0.description,
b1_0.last_modified_at,
b1_0.name,
b1_0.vat
from
buildings b1_0
where
lower(b1_0.description) like ? escape '\\'
or lower(b1_0.name) like ? escape '\\'
or b1_0.id=?
or lower(b1_0.address) like ? escape '\\'
order by
b1_0.name
limit
?, ?
Hope that helps
Regards