escaping values in Spring Data Repository

Question

I have the following in my Spring data repository:

@Query("select i from Image i where i.name like %:name% and deleted = false")
Collection<Image> findByName(@Param("name") String name);

The method searches the database for image records whose name contains a certain string - hence the "like %:name%". The problem is that if the name contains a character which is special to the "like" operator (such as percentage or underscore), it is not being escaped. Seems like this is due to the @Query annotation - without it, everything works as expected.

If I had an image named "my%image" and would like to search for "y%", the SQL would have been "...like '%y\%%'

My question is how do I make the code automatically escape special characters?

Answer 1

I ended up using native query:

@Query(nativeQuery = true, value = "select * from asset where name like concat('%', replace(replace(:name, '%', '\\%'), '_', '\_'), '%')")