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 "%" ?
CodePudding user response：
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?
CodePudding user response：
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);