如何在spring boot jpa中将like%SearchKey%转换为本地查询

Question

我试图将以下查询转换为本机查询，当查询返回2个元组时，我得到了空列表

以下是查询：

SELECT 
  s.id AS shopID,
  s.shop_name AS shopName 
FROM
  shop s 
WHERE s.shop_name LIKE '%store%' ;

下面是我创建的方法，它返回空列表，而它应该发送一个包含两个对象的列表（这是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>

Answer 1

我怀疑%:SearchKey%在本机查询中的组合，我认为%一定是参数的一部分。

@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"
}