带有分页的 Spring Data 和 Native Query [英] Spring Data and Native Query with pagination

问题描述

在一个 web 项目中，使用最新的 spring-data (1.10.2) 和 MySQL 5.6 数据库，我试图使用带有分页的本机查询，但我遇到了 org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodException 在启动时.

In a web project, using latest spring-data (1.10.2) with a MySQL 5.6 database, I'm trying to use a native query with pagination but I'm experiencing an org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodException at startup.

根据使用 spring-data 文档中的 @Query 的示例 50 可以指定查询本身和 countQuery，如下所示:

According to Example 50 at Using @Query from spring-data documentation this is possible specifying the query itself and a countQuery, like this:

public interface UserRepository extends JpaRepository<User, Long> {
  @Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1",
    countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
    nativeQuery = true)
  Page<User> findByLastname(String lastname, Pageable pageable);
}

出于好奇，在 NativeJpaQuery 类中，我可以看到它包含以下代码来检查它是否是有效的 jpa 查询:

Out of curiosity, In NativeJpaQuery class I can see that it contains the following code to check if it's a valid jpa query:

public NativeJpaQuery(JpaQueryMethod method, EntityManager em, String queryString, EvaluationContextProvider evaluationContextProvider, SpelExpressionParser parser) {
   super(method, em, queryString, evaluationContextProvider, parser);
   JpaParameters parameters = method.getParameters();
   boolean hasPagingOrSortingParameter = parameters.hasPageableParameter() || parameters.hasSortParameter();
   boolean containsPageableOrSortInQueryExpression = queryString.contains("#pageable") || queryString.contains("#sort");
   if(hasPagingOrSortingParameter && !containsPageableOrSortInQueryExpression) {
       throw new InvalidJpaQueryMethodException("Cannot use native queries with dynamic sorting and/or pagination in method " + method);
   }
}

我的查询包含一个 Pageable 参数，所以 hasPagingOrSortingParameter 是 true，但它也在寻找一个 #pageable 或 #sort 内的 queryString 序列，我不提供.

My query contains a Pageable parameter, so hasPagingOrSortingParameter is true, but it's also looking for a #pageable or #sort sequence inside the queryString, which I do not provide.

我尝试在我的查询末尾添加 #pageable(这是一个注释)，这使得验证通过，但是在执行时失败，说查询需要一个附加参数:3 而不是 2.

I've tried adding #pageable (it's a comment) at the end of my query, which makes validation to pass but then, it fails at execution saying that the query expects one additional parameter: 3 instead of 2.

有趣的是，如果我在运行时手动将 containsPageableOrSortInQueryExpression 从 false 更改为 true，查询工作正常，所以我不知道为什么它在我的 queryString 中检查该字符串，但我不知道如何提供它.

Funny thing is that, if I manually change containsPageableOrSortInQueryExpression from false to true while running, the query works fine so I don't know why it's checking for that string to be at my queryString and I don't know how to provide it.

任何帮助将不胜感激.

2018 年 1 月 30 日更新spring-data 项目的开发人员似乎正在使用 Jens Schauder 公关

Update 01/30/2018 It seems that developers at spring-data project are working on a fix for this issue with a PR by Jens Schauder

推荐答案

我提前道歉，这几乎是对原始问题和来自 Janar，但是...

My apologies in advance, this is pretty much summing up the original question and the comment from Janar, however...

我遇到了同样的问题:我发现了 Spring Data 的示例 50 作为我需要使用分页进行本机查询的解决方案，但 Spring 在启动时抱怨我无法将分页与本机查询一起使用.

I run into the same problem: I found the Example 50 of Spring Data as the solution for my need of having a native query with pagination but Spring was complaining on startup that I could not use pagination with native queries.

我只是想报告我成功地运行了我需要的本地查询，使用分页，代码如下:

I just wanted to report that I managed to run successfully the native query I needed, using pagination, with the following code:

    @Query(value="SELECT a.* "
            + "FROM author a left outer join mappable_natural_person p on a.id = p.provenance_id "
            + "WHERE p.update_time is null OR (p.provenance_name='biblio_db' and a.update_time>p.update_time)"
            + "ORDER BY a.id 
#pageable
", 
        /*countQuery="SELECT count(a.*) "
            + "FROM author a left outer join mappable_natural_person p on a.id = p.provenance_id "
            + "WHERE p.update_time is null OR (p.provenance_name='biblio_db' and a.update_time>p.update_time) 
#pageable
",*/
        nativeQuery=true)
public List<Author> findAuthorsUpdatedAndNew(Pageable pageable);

使用Page需要countQuery(在代码块中注释掉)作为查询的返回类型，需要#pageable"注释周围的换行符，以避免出现预期参数数量的运行时错误(变通方法的解决方法).我希望这个错误能尽快修复...

The countQuery (that is commented out in the code block) is needed to use Page<Author> as the return type of the query, the newlines around the "#pageable" comment are needed to avoid the runtime error on the number of expected parameters (workaround of the workaround). I hope this bug will be fixed soon...

这篇关于带有分页的 Spring Data 和 Native Query的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！