Spring JPA @Query with LIKE
JavaJquerySqlSpringJpaJava Problem Overview
I'm trying to make a method in CrudRepository that will be able to give me list of users, whose usernames are LIKE the input parameter(not only begin with, but also contains it). I tried to use method "findUserByUsernameLike(@Param("username") String username)"
but as it is told in Spring documentation, this method is equal to
"where user.username like ?1
". It is not good for me, as I already told that I'm trying to get all users whose username contains ...
I wrote a queryto the method but it even doesn't deploy.
@Repository
public interface UserRepository extends CrudRepository<User, Long> {
@Query("select u from user u where u.username like '%username%'")
List<User> findUserByUsernameLike(@Param("username") String username);
}
Can anybody help me with this?
Java Solutions
Solution 1 - Java
Try to use the following approach (it works for me):
@Query("SELECT u.username FROM User u WHERE u.username LIKE CONCAT('%',:username,'%')")
List<String> findUsersWithPartOfName(@Param("username") String username);
Notice: The table name in JPQL must start with a capital letter.
Solution 2 - Java
> Using Query creation from method names, check table 4 where they explain some keywords.
-
Using Like:
select ... like :username
List<User> findByUsernameLike(String username);
-
StartingWith:
select ... like :username%
List<User> findByUsernameStartingWith(String username);
-
EndingWith:
select ... like %:username
List<User> findByUsernameEndingWith(String username);
-
Containing:
select ... like %:username%
List<User> findByUsernameContaining(String username);
Notice that the answer that you are looking for is number 4. You don't have to use @Query
Solution 3 - Java
Another way: instead CONCAT
function we can use double pipe: :lastname || '%'
@Query("select c from Customer c where c.lastName LIKE :lastname||'%'")
List<Customer> findCustomByLastName( @Param("lastname") String lastName);
You can put anywhere, prefix, suffix or both
:lastname ||'%'
'%' || :lastname
'%' || :lastname || '%'
Solution 4 - Java
Easy to use following (no need use CONCAT or ||):
@Query("from Service s where s.category.typeAsString like :parent%")
List<Service> findAll(@Param("parent") String parent);
Documented in: http://docs.spring.io/spring-data/jpa/docs/current/reference/html.
Solution 5 - Java
List<User> findByUsernameContainingIgnoreCase(String username);
in order to ignore case issues
Solution 6 - Java
For your case, you can directly use JPA methods. That is like bellow:
Containing: select ... like %:username%
List<User> findByUsernameContainingIgnoreCase(String username);
here, IgnoreCase will help you to search item with ignoring the case.
Here are some related methods:
-
Like
findByFirstnameLike
… where x.firstname like ?1
-
StartingWith
findByFirstnameStartingWith
… where x.firstname like ?1 (parameter bound with appended %)
-
EndingWith
findByFirstnameEndingWith
… where x.firstname like ?1 (parameter bound with prepended %)
-
Containing
findByFirstnameContaining
… where x.firstname like ?1 (parameter bound wrapped in %)
More info , view this link and this link
Hope this will help you :)
Solution 7 - Java
This way works for me, (using Spring Boot version 2.0.1. RELEASE):
@Query("SELECT u.username FROM User u WHERE u.username LIKE %?1%")
List<String> findUsersWithPartOfName(@Param("username") String username);
Explaining: The ?1, ?2, ?3 etc. are place holders the first, second, third parameters, etc. In this case is enough to have the parameter is surrounded by % as if it was a standard SQL query but without the single quotes.
Solution 8 - Java
You Missed a colon(:) before the username parameter. therefore your code must change from:
@Query("select u from user u where u.username like '%username%'")
to :
@Query("select u from user u where u.username like '%:username%'")
Solution 9 - Java
this @Query("select u from user u where u.username like '%:username%'")
does not work in some cases!
working solution for me was
@Query("SELECT u.username FROM User u WHERE u.username LIKE CONCAT('%',:username,'%')")
Solution 10 - Java
@Query("select u from user u where u.username LIKE :username")
List<User> findUserByUsernameLike(@Param("username") String username);
Solution 11 - Java
@Query("select b.equipSealRegisterId from EquipSealRegister b where b.sealName like %?1% and b.deleteFlag = '0'" )
List<String>findBySeal(String sealname);
I have tried this code and it works.