Search code examples
javaspringjpaspring-data-jpajpql

JPQL Like Case Insensitive

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 '%'?

Solution

  • 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)