Java 带有 LIKE 的 Spring JPA @Query

Spring JPA @Query with LIKE

提问by Viktoriia

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 ...

我正在尝试在 CrudRepository 中创建一个方法，该方法能够为我提供用户列表，其用户名类似于输入参数（不仅以开头，还包含它）。我尝试使用方法，"findUserByUsernameLike(@Param("username") String username)"但正如 Spring 文档中所说，此方法等于“ where user.username like ?1”。这对我不利，因为我已经告诉过我正在尝试获取用户名包含...的所有用户

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?

有人可以帮我解决这个问题吗？

采纳答案by Mark

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.

注意：JPQL 中的表名必须以大写字母开头。

回答by Mannekenpix

@Query("select u from user u where u.username LIKE :username")
List<User> findUserByUsernameLike(@Param("username") String username);

回答by remat_br

Another way: instead CONCATfunction we can use double pipe: :lastname || '%'

另一种方式：CONCAT我们可以使用双管道代替函数：: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 || '%'  

回答by jmgoyesc

Using Query creation from method names, check table 4 where they explain some keywords.

使用从方法名称创建查询，检查表 4，它们解释了一些关键字。

1. Using Like: select ... like :username

   List<User> findByUsernameLike(String username);
   

2. StartingWith: select ... like :username%

   List<User> findByUsernameStartingWith(String username);
   

3. EndingWith: select ... like %:username

   List<User> findByUsernameEndingWith(String username);
   

4. Containing: select ... like %:username%

   List<User> findByUsernameContaining(String username);
   

1. 使用 Like: select ... like :username

   List<User> findByUsernameLike(String username);
   

2. 起始方式：选择...如：用户名%

   List<User> findByUsernameStartingWith(String username);
   

3. EndingWith: 选择 ... 像 %:username

   List<User> findByUsernameEndingWith(String username);
   

4. 包含：选择 ... 像 %: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

请注意，您正在寻找的答案是数字 4。你不必使用@Query

回答by derrick.yang

@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.

我已经尝试过这段代码并且它有效。

回答by Yusuf

List<User> findByUsernameContainingIgnoreCase(String username);

List<User> findByUsernameContainingIgnoreCase(String username);

in order to ignore case issues

为了忽略案例问题

回答by Míra

Easy to use following (no need use CONCAT or ||):

易于使用以下（无需使用 CONCAT 或 ||）：

@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.

记录在：http: //docs.spring.io/spring-data/jpa/docs/current/reference/html。

回答by Md. Sajedul Karim

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

对于您的情况，您可以直接使用 JPA 方法。就像下面这样：

Containing: select ... like %:username%

包含：选择 ... 像 %:username%

List<User> findByUsernameContainingIgnoreCase(String username);

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

在这里，IgnoreCase将帮助您忽略大小写来搜索项目。

Here are some related methods:

下面是一些相关的方法：

1. LikefindByFirstnameLike

   … where x.firstname like ?1

2. StartingWithfindByFirstnameStartingWith

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

3. EndingWithfindByFirstnameEndingWith

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

4. ContainingfindByFirstnameContaining

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

1. 喜欢findByFirstnameLike

   ... x.firstname 像 ?1

2. 从...开始findByFirstnameStartingWith

   ... 其中 x.firstname 像 ?1 （参数绑定附加 %）

3. 结尾findByFirstnameEndingWith

   ... 其中 x.firstname 像 ?1 （参数绑定了前置 %）

4. 含findByFirstnameContaining

   ... where x.firstname like ?1（参数绑定在 % 中）

More info , view this linkand this link

更多信息，查看这个链接和这个链接

Hope this will help you :)

希望能帮到你 ：）

回答by Uisleandro

This way works for me, (using Spring Boot version 2.0.1. RELEASE):

这种方式对我有用，（使用 Spring Boot 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.

解释：?1、?2、?3 等是第一个、第二个、第三个参数等的占位符。在这种情况下，参数被 % 包围就足够了，就好像它是一个标准的 SQL 查询，但没有单引号。