本文介绍了如何在Spring Boot JPA中将%searchKey%转换为本地查询的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在尝试将以下查询转换为本机查询,当查询返回2个元组时,我得到了空列表
I'm trying to convert the following query to native query, I'm getting empty list while the query is returning 2 tuples
以下是查询:
SELECT
s.id AS shopID,
s.shop_name AS shopName
FROM
shop s
WHERE s.shop_name LIKE '%store%' ;
以下是我创建的方法,该方法返回空列表,而它应该发送包含两个对象的列表(这是m存储库的方法)
following is the method I've created which is returning empty list while it's supposed to send a list containing two objects(this is the method of m repository)
@Query(value = "SELECT \n" +
" s.id AS shopID,\n" +
" s.shop_name AS shopName \n" +
"FROM\n" +
" shop s \n" +
"WHERE s.shop_name LIKE %:searchKey%", nativeQuery = true)
List<Object[]> buyerDashboardSearchSuggestion(@Param("searchKey") String searchKey);
查询结果
以下是我的邮递员请求
{
"searchKey": "store"
}
依赖关系与数据库相关
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-jdbc</artifactId>
</dependency>
<dependency>
<groupId>org.springframework.boot</groupId>
<artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>8.0.21</version>
</dependency>
推荐答案
我怀疑%:searchKey%
在本机查询中的组合,我认为%
必须是参数的一部分.
I doubt the combination of %:searchKey%
in a native query, I think %
must be part of the parameter.
@Query(value = "SELECT \n" +
" s.id AS shopID,\n" +
" s.shop_name AS shopName \n" +
"FROM\n" +
" shop s \n" +
"WHERE s.shop_name LIKE :searchKey", nativeQuery = true)
List<Object[]> buyerDashboardSearchSuggestion_internal(@Param("searchKey") String searchKey);
default List<Object[]> buyerDashboardSearchSuggestion(String searchKey) {
return buyerDashboardSearchSuggestion_internal("%"+searchKey+"%");
//todo escape special chars like % and _ in "searchKey"
}
这篇关于如何在Spring Boot JPA中将%searchKey%转换为本地查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!