0

I make simple database of names. There are only three names: Adam , Arnold and Matin. And I want to get all names that contains letter "a". I have in my repository this method: Let String letter = "a";

@Query(value = "SELECT * FROM person WHERE name LIKE %:letter%", nativeQuery = true)
    List<PersonEntity> findByNameLike( @Param("letter") String letter);

It returns all names(List of names), because it find some "a" or "A" in all of names. But I want to find only names that contains exactly lower case "a". This query works in the Workbech wery well: SELECT * FROM person WHERE name LIKE BINARY '%a%'; But this code returns empty(null) List.

@Query(value = "SELECT * FROM person WHERE   name  LIKE BINARY '%:letter%'", nativeQuery = true)
    List<PersonEntity> findByNameLike( @Param("letter") String letter);

I dont know how to link the variable letter to the query. Thank you for any ideas. PS:How to make SQLinjection protection in this case if the variable letter is wrapped in "%" ?

2 Answers2

0

Change BINARY to lower. It specifies you want a lower-case.

@Query(value = "SELECT * FROM person WHERE name LIKE lower '%:letter%'", nativeQuery = true)
    List<PersonEntity> findByNameLike( @Param("letter") String letter);

As a tip, I wouldn't use 'jpql', if you can take advantage of spring-data, and you don't have to writte the query:

List<PersonEntity> findByLetterContaining(String letter);

Doc: JPQL Like Case Insensitive

Talenel
  • 422
  • 2
  • 6
  • 25
  • Hi Adolin! I chose bad example, String letter = "Aa" is much better. Concat operator solves my trouble. Thank you very much! –  May 24 '22 at 12:42
0

With

@Query(value = "SELECT * FROM person WHERE   name  LIKE BINARY '%:letter%'", nativeQuery = true)
    List<PersonEntity> findByNameLike( @Param("letter") String letter);

you are looking for names that contain the String ':letter', try

@Query(value = "SELECT * FROM person WHERE   name  LIKE BINARY CONCAT('%',:letter,'%')", nativeQuery = true)
    List<PersonEntity> findByNameLike( @Param("letter") String letter);

And using Query-annotation there is no chance of SQL-injection.

By using the right collation you decide on table creation wether queries are case sensitive or case insensitive, see Are UNIQUE indices case sensitive in MySQL?

Turo
  • 4,724
  • 2
  • 14
  • 27
  • Hi Turo! Your code works perfectly! Thank you for your answer and for the collation tip! –  May 24 '22 at 12:53