带分页的Spring数据和本机查询 [英] Spring Data and Native Query with pagination

问题描述

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

更新:20180306现在已在可以从spring-data文档中使用@Query的示例50 ，可以指定查询本身和countQuery，如下所示:

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查询:

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，但是它也在queryString内寻找一个#pageable或#sort序列，我没有提供.

我尝试在查询末尾添加#pageable(这是一条注释)，这使验证通过，但随后执行失败，表示查询需要一个附加参数:3而不是2./p>

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

任何帮助将不胜感激.

更新01/30/2018 似乎spring-data项目的开发人员正在使用解决方案

我在此表示歉意，这基本上总结了原始问题和

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

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.

UPDATE: 20180306 This issue is now fixed in Spring 2.0.4 For those still interested or stuck with older versions check the related answers and comments for workarounds.

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);
}

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);
   }
}

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.

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.

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.

Any help would be much appreciated.

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

解决方案

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

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 \n#pageable\n", 
        /*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) \n#pageable\n",*/
        nativeQuery=true)
public List<Author> findAuthorsUpdatedAndNew(Pageable 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数据和本机查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！