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

Issue

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

Solution

I solved this issue by changing escape character constant

import org.springframework.data.jpa.repository.query.EscapeCharacter

val ESCAPE_CHARACTER: EscapeCharacter = EscapeCharacter.of('#')

I also manually added escape '#' to each JPQL LIKE query

@Query("""
    select t from ToneEntity t
    where (:title is null or (t.title like %:title%  escape '#'))
    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>

This is not the best solution, but the release deadline forced me to use this approach

Answered By - Jean Ponomarev