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);
}
This question is related to
java
jpa
spring-data-jpa
answer exactly will be
-->` @Query("select u from Category u where u.categoryName like %:input%") List findAllByInput(@Param("input") String input);
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);
}
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:
Like findByPlaceLike
… where x.place like ?1
StartingWith findByPlaceStartingWith
… where x.place like ?1 (parameter bound with appended %)
EndingWith findByPlaceEndingWith
… where x.place like ?1 (parameter bound with prepended %)
Containing findByPlaceContaining
… where x.place like ?1 (parameter bound wrapped in %)
More info, view this link , this link and this
Hope this will help you :)
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.
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);
when call funtion, I use:
findByPlaceContaining("%" + place);
or:
findByPlaceContaining(place + "%");
or:
findByPlaceContaining("%" + place + "%");
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!
Try this.
@Query("Select c from Registration c where c.place like '%'||:place||'%'")
You can also implement the like queries using Spring Data JPA supported keyword "Containing".
List<Registration> findByPlaceContaining(String place);
Source: Stackoverflow.com