Description
Context
We often create queries with optional filtering on parameters (ignore them when null) like so:
@Query("""
select j from Jedi j
where (:name is null or j.name = :name)
and (:desc is null or j.description like %:desc%)
""")
fun searchContains(
@Param("name") name: String?,
@Param("desc") desc: String?,
): List<Jedi>
As noted in Example 62 in https://docs.spring.io/spring-data/jpa/docs/current/reference/html/#jpa.query-methods.at-query.advanced-like
the LIKE delimiter character (%) is recognized, and the query is transformed into a valid JPQL query (removing the %).
Upon running the query, the parameter passed to the method call gets augmented with the previously recognized LIKE pattern.
This worked fine previously, but after upgrading til 2.7.0 it does not seem to work when such a named parameter is null, which it is intended to be when using that to implement optional filtering like this.
Problem
When the named parameter is null, the binded value is the literal string org.hibernate.jpa.TypedParameterValue@<number> augmented with the wildcard parameter, instead of just null.
I have prepared a demo app that showcases the issue: https://github.com/jonhakr/spring-data-jpa-null-optional-query
The query above is generated as such:
select
jedi0_.id as id1_0_,
jedi0_.description as descript2_0_,
jedi0_.name as name3_0_
from
jedi jedi0_
where
(
? is null
or jedi0_.name=?
)
and (
? is null
or jedi0_.description like ?
)
When name is null it works fine, and the resulting bound parameter values are:
binding parameter [1] as [VARCHAR] - [null]
binding parameter [2] as [VARCHAR] - [null]
binding parameter [3] as [VARCHAR] - [%er%]
binding parameter [4] as [VARCHAR] - [%er%]
However, if the wildcard appended desc is null, the resulting bound parameter values are:
binding parameter [1] as [VARCHAR] - [Mace Windu]
binding parameter [2] as [VARCHAR] - [Mace Windu]
binding parameter [3] as [VARCHAR] - [%org.hibernate.jpa.TypedParameterValue@38d895e8%]
binding parameter [4] as [VARCHAR] - [%org.hibernate.jpa.TypedParameterValue@38d895e8%]
Simply setting spring-data-jpa to 2.6.4 makes this work fine again