java - “.count”在可分页查询中的 spring jpa-named-queries.properties 文件中不起作用

Question

我正在使用 Spring Batch，由于需求，我必须将我的项目正在使用的查询外部化。我遇到了以下问题，涉及使用分页的 JPA 存储库中的计数查询：

Hibernate: select * from ( SELECT DISTINCT column1 AS RID, column2 AS TYPE FROM mytable WHERE TYPE = 'X' AND TRIM(column3) IN (?, ?, ?) order by RID asc ) where rownum <= ?
Hibernate: select count(DISTINCT WHERE) FROM mytable WHERE TYPE = 'X' AND TRIM(column3) IN (?, ?, ?)
2020-04-20 09:45:43,464 ERROR: o.h.e.j.s.SqlExceptionHelper [main] ORA-00936: missing expression

如您所见，已形成的计数查询没有意义。

jpa-named-queries.properties 文件 (\src\main\resources\META-INF) 包含以下内容：

Operation.getItems=SELECT DISTINCT column1 AS RID, column2 AS TYPE FROM mytable WHERE TYPE = 'X' AND TRIM(column3) IN :list
Operation.getItems.count

存储库定义如下所示：

@Repository
public interface ItemsRepository extends JpaRepository<MyClass, String> {

    @Query(
            name = "Operation.getItems",
            nativeQuery = true
            )
    public Page<MyClass> findAllItems(@Param("list") List<String> myList, Pageable pageable);
} 

如果我在 @Query 注释中使用“countName”，我会遇到同样的错误：

jpa-named-queries.properties（注意 B 中的不同而不是 DISTINCT）：

Operation.getItems=SELECT DISTINCT column1 AS RID, column2 AS TYPE FROM mytable WHERE TYPE = 'X' AND TRIM(column3) IN :list
Operation.getItemsB=SELECT COUNT(*) FROM (SELECT distinct column1 AS RID, column2 AS TYPE FROM mytable WHERE TYPE = 'X' AND TRIM(column3) IN :list)

存储库：

@Repository
public interface ItemsRepository extends JpaRepository<MyClass, String> {

    @Query(
            name = "Operation.getItems",
            nativeQuery = true,
            countName = "Operation.getItemsB"
            )
    public Page<MyClass> findAllItems(@Param("list") List<String> myList, Pageable pageable);
} 

错误：

Hibernate: select count(distinct WHERE) FROM mytable WHERE TYPE = 'X' AND TRIM(COLUMN3) IN (?, ?, ?)
2020-04-20 10:36:39,936 ERROR: o.h.e.j.s.SqlExceptionHelper [main] ORA-00936: missing expression

如果我在存储库文件中编写计数查询，它工作正常，但这不是目标。

知道发生了什么吗？

感谢和问候。

标签： javaspringspring-bootjpaspring-batch