67

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

windupurnomo
  • 1,121
  • 1
  • 11
  • 22

6 Answers6

144

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)

Wim Deblauwe
  • 25,113
  • 20
  • 133
  • 211
  • Good approach. Thank you! – Saxophonist Jan 15 '20 at 15:04
  • Note that this might change your query plan and bypass a SQL index, slowing down queries, especially with lots of users in the table. This is because it has to apply the lower() to all the values in the DB on every call. – Max Jul 06 '20 at 11:50
  • @Max Thanks for that observation, did not think of that. According to https://stackoverflow.com/questions/12855/database-case-insensitive-index there are ways to avoid it, but it very much seems to depend on what db you are using. – Wim Deblauwe Jul 06 '20 at 12:21
  • when given null, boom baby! – withoutOne Jul 27 '20 at 12:01
  • it fails when nameToFind is null. any solution? – Malindu Sandaruwan Sep 28 '20 at 05:18
  • @MalinduSandaruwan that's because you need to specify what you want to happen when nameToFind is null. If you want to NOT filter by name, then the entire clause `lower(u.name) like lower(...)` must be omitted from the string. If you have multiple clauses, you will have to omit and `and` or replace the omitted part with a `1=1`. And so on. – Tobia Mar 02 '21 at 10:45
40

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.

M. Deinum
  • 115,695
  • 22
  • 220
  • 224
16

I am using Spring Boot 2.1.6, You can define query methods using Containing, Contains, and IsContaining as below:

List<User> findByNameContaining(String name);
List<User> findByNameContains(String name);
List<User> findByNameIsContaining(String name);

Case Insensitivity:

List<User> findByNameContainingIgnoreCase(String name);

OR you can also define as below as well:

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

The @Param annotation is important here because we're using a named parameter.

Shashank
  • 709
  • 8
  • 16
3

Without using concat and using TypedQuery:

  TypedQuery<Baptism> query = entityManager.createQuery("SELECT d FROM " + Baptism.class.getSimpleName()
                            + " d JOIN d.person p WHERE UPPER(p.lastName) LIKE UPPER(:ln)", Baptism.class);
                    query.setParameter("ln", "%" + ln + "%");
Wortig
  • 963
  • 2
  • 11
  • 37
0

You can use spring boot naming convention strategy to achieve with naming pattern like: findByYourFieldCamelCaseLikeIgnoreCase

example: findByUserNameLikeIgnoreCase(userName: String): List<UserEntity>

Swornim Shah
  • 59
  • 1
  • 5
-1

You can use wildcard matching.

for example, i want to search name like haha,

@Query("select u from User u where lower(u.name) like :u_name")
public List<User> findByNameFree(@Param("u_name") String name);
List<User> users = userDao.findByNameFree("%haha");
Nicholas
  • 24
  • 3