JPQL Like Case Insensitive

Question

I want to search data in User table by name case insensitive.

@Repository public interface UserRepository extends JpaRepository<User, Long> {    @Query("select u from User u where lower(u.name) like %lower(?1)%")   public List<User> findByNameFree(String name);  } 

I got an error: unexpected token: %. Where should I place '%'?

Answer 1

You can use the concat operator:

@Query("select u from User u where lower(u.name) like lower(concat('%', ?1,'%'))") public List<User> findByNameFree(String name); 

or with a named parameter:

@Query("select u from User u where lower(u.name) like lower(concat('%', :nameToFind,'%'))") public List<User> findByNameFree(@Param("nameToFind") String name); 

(Tested with Spring Boot 1.4.3)

Answer 2

If that is only what you want and you are using Spring Data JPA you don't need to write a query.

List<User> findByNameContainingIgnoreCase(String name); 

Else you need to wrap the name attribute with % before you pass it to the method (putting those directly in the query will simply not work). Or don't use a query but use a specification or the Criteria API to create the query.