How to escape '_' and '%' wildcards in Spring Data JPQL LIKE query?

Question

I have the following method in Spring data repositiry:

@Query("""
    select t from ToneEntity t
    where (:title is null or (t.title like %:title%))
    and (:albumId is null or (t.album.id = :albumId))
    and (:artistId is null or (t.artist.id = :artistId))
    and (:creatorId is null or (t.creator.id = :creatorId))
""")
fun findFilteredTones(
    @Param("title") title: String?,
    @Param("albumId") albumId: Long?,
    @Param("artistId") artistId: Long?,
    @Param("creatorId") creatorId: Long?,
    pageable: Pageable
): List<ToneEntity>

When a title contains _ or % chars, Spring data passes them as wildcards but not as literals. Example: I have a tone with the title 'bug_with_underscore' in the database. User on the web UI passes '' to find tones with '' literal in the title, but the actual result includes all tones.

How to set up automatic character escaping in LIKE queries?

Version of Spring Data JPA: 2.3.5

I found several possible solutions:

1. Use concat and replace SQL functions: https://stackoverflow.com/questions/30740432/escaping-values-in-spring-data-repository
2. Shield wildcard characters before passing them to the query: https://stackoverflow.com/questions/53558667/java-jpa-sql-accept-wrong-with-like

Is there any better solution in 2022? So far, I haven't found anything better than using aspects to escape wildcard characters manually. Because this problem occurs very often in our project

Answer 1

This may help you, although unfortunately it didn't help me yet as this syntax is no longer valid in Spring Boot 3. I did try it in Spring Boot 2 and it worked. It's in Java but the query part shouldn't be any different.

@Query("SELECT m FROM Movie m WHERE m.director LIKE %?#{escape([0])} escape ?#{escapeCharacter()}")
List<Movie> searchByDirectorEndsWith(String director);

Source: https://www.baeldung.com/spring-jpa-like-queries#2-ordered-parameters