SpringBoot JPA HQL LIKE query is not working after upgrade to Spring-boot 3.1.0, JDK17 from Spring boot 3.0.6

Question

Spring Boot 3.1.0 JPA HQL LIKE query is not working after upgrade to Spring-boot 3.1.0, JDK17 from Spring boot 3.0.6

JPA Like query with named parameters was working fine before upgrade. But after upgrading to SpringBoot 3.1.0 getting runtime error. We get this error, when we use like operator with % symbol on string column for 'like' condition.

We can reproduce the issue by mentioning the DEBUG log level for package "org.hibernate.orm.query.hql" in application.properties file.

logging.level.org.hibernate.orm.query.hql=DEBUG

Note: This query JPA HQL query was working fine using older versions of Spring boot (< 3.0.6). It is upgrade issue.

JPA HQL query example using @Query annotation.

@Query("SELECT m FROM Movie m WHERE m.title LIKE %:title%")
List<Movie> searchByTitleLike(@Param("title") String title);

Error message/Stack Trace:

Caused by: java.util.UnknownFormatConversionException: Conversion = '''
    at java.base/java.util.Formatter.parse(Formatter.java:2750)
    at java.base/java.util.Formatter.format(Formatter.java:2671)
    at java.base/java.util.Formatter.format(Formatter.java:2625)
    at java.base/java.lang.String.format(String.java:4143)
    at org.jboss.logging.Slf4jLocationAwareLogger.doLogf(Slf4jLocationAwareLogger.java:81)
    at org.jboss.logging.Logger.logf(Logger.java:2445)
    at org.jboss.logging.DelegatingBasicLogger.debugf(DelegatingBasicLogger.java:344)
    at org.hibernate.query.hql.internal.StandardHqlTranslator.translate(StandardHqlTranslator.java:75)
    at org.hibernate.internal.AbstractSharedSessionContract.lambda$interpretHql$2(AbstractSharedSessionContract.java:744)
    at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.createHqlInterpretation(QueryInterpretationCacheStandardImpl.java:141)
    at org.hibernate.query.internal.QueryInterpretationCacheStandardImpl.resolveHqlInterpretation(QueryInterpretationCacheStandardImpl.java:128)
    at org.hibernate.internal.AbstractSharedSessionContract.interpretHql(AbstractSharedSessionContract.java:741)
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:786)
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:704)
    at org.hibernate.internal.AbstractSharedSessionContract.createQuery(AbstractSharedSessionContract.java:120)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:568)
    at org.springframework.orm.jpa.ExtendedEntityManagerCreator$ExtendedEntityManagerInvocationHandler.invoke(ExtendedEntityManagerCreator.java:360)
    at jdk.proxy3/jdk.proxy3.$Proxy153.createQuery(Unknown Source)
    at org.springframework.data.jpa.repository.query.SimpleJpaQuery.validateQuery(SimpleJpaQuery.java:94)
    ... 96 more

Answer 1

It still not working in Spring Boot 3.2.0 version. I hadn't this problem in 2.7.5 version.

If somebody has problem with criteriaBuilder problem, I fixed this with adding third parameter to criteriaBuilder.like(foo, foo, '/')

Now hibernate adds ESCAPE to like clause

Answer 2

The given issue is fixed in the latest version of Spring Boot 3.1.2 without doing any code changes for like query.

Answer 3

Given this HQL query

@Query("SELECT m FROM Movie m WHERE m.title LIKE %:title%")

SpringBoot 3.0.7 Converted HQL Query for printing HQL Query in Logs:

SELECT m FROM Movie m WHERE m.title LIKE :title

Note: here Spring Boot 3.0.7 library is stripping of % symbol before printing to logs.

SpringBoot 3.1.0 Converted HQL Query for printing HQL query in Logs:

SELECT m 
FROM Movie m 
WHERE m.title LIKE CONCAT('%', :title, '%')

But % symbol is used as format specifier when format() method is called before printing to logs. Refer Java.util.Formatter.format() method.

Single % symbol in string argument to format() method throws conversion exception.

Workarounds

Option #1

Add the INFO log level to package 'org.hibernate.orm.query.hql' in the application.properties file to avoid the exception on the application start up during bean creation time.

logging.level.org.hibernate.orm.query.hql=INFO

Option #2

Escape the % symbol in the given HQL query with another % symbol as shown below to avoid the exception on the application start up during bean creation time.

SELECT m 
FROM Movie m 
WHERE m.title LIKE CONCAT('%%', :title, '%%')

Looks like it is bug in the Spring Boot 3.1.0 version.

Answer 4

How i know % is a special character in a format string that indicates that a format specifier follows.

Try it:

@Query("SELECT m FROM Movie m WHERE m.title LIKE :title")
List<Movie> searchByTitleLike(@Param("title") String title);