CODEWITHSUNDEEP
javajpaspring-data-jpa
sudeep cv
sudeep cv

Reputation: 1951

%Like% Query in spring JpaRepository

I would like to write a like query in JpaRepository but it is not returning anything :

LIKE '%place%'-its not working.

LIKE 'place' works perfectly.

Here is my code : 

@Repository("registerUserRepository")
public interface RegisterUserRepository extendsJpaRepository<Registration,Long> {

    @Query("Select c from Registration c where c.place like :place")
     List<Registration> findByPlaceContaining(@Param("place")String place);
}

Upvotes: 151

Views: 247308

Answers (16)

Satish Patro
Satish Patro

Reputation: 4414

There can be various approaches. As mentioned in answer of many, if possible you can use JPA predefined template query.

List<Registration> findByPlaceContainingIgnoreCase(String place);

Also, you can append '%' in java layer before calling the above method.

If complex query, then you can normally use @Query one.

@Query("Select r from Registration r where r.place like '%' || :place || '%'")

For readability, you can use below one.

@Query("Select r from Registration r where r.place like CONCAT('%', :place, '%')")

Upvotes: 2

Md. Sajedul Karim
Md. Sajedul Karim

Reputation: 7095

For your case, you can directly use JPA methods. That code is like bellow :

Containing: select ... like %:place%

List<Registration> findByPlaceContainingIgnoreCase(String place);

here, IgnoreCase will help you to search item with ignoring the case.

Using @Query in JPQL :

@Query("Select registration from Registration registration where 
registration.place LIKE  %?1%")
List<Registration> findByPlaceContainingIgnoreCase(String place);

Here are some related methods:

  1. Like findByPlaceLike

    … where x.place like ?1

  2. StartingWith findByPlaceStartingWith

    … where x.place like ?1 (parameter bound with appended %)

  3. EndingWith findByPlaceEndingWith

    … where x.place like ?1 (parameter bound with prepended %)

  4. Containing findByPlaceContaining

    … where x.place like ?1 (parameter bound wrapped in %)

More info, view this link (where the above quote is from), this link and this

Upvotes: 44

Mohamed AbdElRazek
Mohamed AbdElRazek

Reputation: 1684

This is now possible with Spring Data JPA. Check out http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#query-by-example

Registration registration = new Registration();
registration.setPlace("UK");

ExampleMatcher matcher = ExampleMatcher.matchingAll()
  .withIgnoreCase()
  .withStringMatcher(StringMatcher.CONTAINING);

Example<Registration> example = Example.of(registration, matcher);

List<Registration> registrationList = registerUserRepository.findAll(example);

Upvotes: 0

Alan Naicson
Alan Naicson

Reputation: 59

I had to use something like this CONCAT('%',:setName,'%')

Upvotes: 2

Ali
Ali

Reputation: 327

We can use native query

@Query(nativeQuery = true, value ="Select * from Registration as c where c.place like %:place%")

List<Registration> findByPlaceContaining(@Param("place")String place);

Upvotes: 2

user1355816
user1355816

Reputation: 1

Us like this

@Query("from CasFhgDeviceView where deviceGroupName like concat(concat('%CSGW%', :usid), '%') ")

Upvotes: 0

Namesh Silva
Namesh Silva

Reputation: 1

You can just simply say 'Like' keyword after parameters..

List<Employee> findAllByNameLike(String name);

Upvotes: 0

Alexius DIAKOGIANNIS
Alexius DIAKOGIANNIS

Reputation: 2584

You dont actually need the @Query annotation at all.

You can just use the following

    @Repository("registerUserRepository")
    public interface RegisterUserRepository extends JpaRepository<Registration,Long>{
    
    List<Registration> findByPlaceIgnoreCaseContaining(String place);

    }

Upvotes: 133

Mateusz Niedbal
Mateusz Niedbal

Reputation: 346

I use this:

@Query("Select c from Registration c where lower(c.place) like lower(concat('%', concat(:place, '%')))")

lower() is like toLowerCase in String, so the result isn't case sensitive.

Upvotes: 6

catch32
catch32

Reputation: 18612

Found solution without @Query (actually I tried which one which is "accepted". However, it didn't work).

Have to return Page<Entity> instead of List<Entity>:

public interface EmployeeRepository 
                          extends PagingAndSortingRepository<Employee, Integer> {
    Page<Employee> findAllByNameIgnoreCaseStartsWith(String name, Pageable pageable);
}

IgnoreCase part was critical for achieving this!

Upvotes: 0

Ashu
Ashu

Reputation: 101

Try this.

@Query("Select c from Registration c where c.place like '%'||:place||'%'")

Upvotes: 10

Sulaymon Hursanov
Sulaymon Hursanov

Reputation: 476

answer exactly will be 

-->` @Query("select u from Category u where u.categoryName like %:input%")
     List findAllByInput(@Param("input") String input);

Upvotes: 1

user9290654
user9290654

Reputation: 31

when call funtion, I use: findByPlaceContaining("%" + place);

or: findByPlaceContaining(place + "%");

or: findByPlaceContaining("%" + place + "%");

Upvotes: 3

amanzoor
amanzoor

Reputation: 399

You can also implement the like queries using Spring Data JPA supported keyword "Containing". 

List<Registration> findByPlaceContaining(String place);

Upvotes: 33

Nitin Pawar
Nitin Pawar

Reputation: 936

You can have one alternative of using placeholders as:

@Query("Select c from Registration c where c.place LIKE  %?1%")
List<Registration> findPlaceContainingKeywordAnywhere(String place);

Upvotes: 7

Hille
Hille

Reputation: 4196

The spring data JPA query needs the "%" chars as well as a space char following like in your query, as in

@Query("Select c from Registration c where c.place like %:place%").

Cf. http://docs.spring.io/spring-data/jpa/docs/current/reference/html.

You may want to get rid of the @Queryannotation alltogether, as it seems to resemble the standard query (automatically implemented by the spring data proxies); i.e. using the single line

List<Registration> findByPlaceContaining(String place);

is sufficient.

Upvotes: 271

Related Questions