CODEWITHSUNDEEP
javaspringjpaspring-data-jpajpql
windupurnomo
windupurnomo

Reputation: 1190

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

Upvotes: 73

Views: 99097

Answers (6)

Swornim Shah
Swornim Shah

Reputation: 69

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

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

Upvotes: 0

MarekChr
MarekChr

Reputation: 1160

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 + "%");

Upvotes: 3

Shashank
Shashank

Reputation: 757

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.

Upvotes: 17

Wim Deblauwe
Wim Deblauwe

Reputation: 26858

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)

Upvotes: 153

M. Deinum
M. Deinum

Reputation: 124546

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.

Upvotes: 45

Nicholas
Nicholas

Reputation: 24

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");

Upvotes: -1

Related Questions