带有分页的 Spring Data 和 Native Query
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/38349930/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Spring Data and Native Query with pagination
提问by Lasneyx
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.InvalidJpaQueryMethodExceptionat startup.
在一个 web 项目中，使用最新的 spring-data (1.10.2) 和 MySQL 5.6 数据库，我试图使用带有分页的本机查询，但我org.springframework.data.jpa.repository.query.InvalidJpaQueryMethodException在启动时遇到了一个。
UPDATE: 20180306 This issue is now fixed in Spring 2.0.4For those still interested or stuck with older versions check the related answers and comments for workarounds.
更新：20180306 此问题现已在Spring 2.0.4 中修复对于那些仍然对旧版本感兴趣或坚持使用旧版本的人，请查看相关答案和评论以获取解决方法。
According to Example 50 at Using @Query from spring-data documentationthis is possible specifying the query itself and a countQuery, like this:
根据使用 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);
}
Out of curiosity, In NativeJpaQueryclass I can see that it contains the following code to check if it's a valid jpa query:
出于好奇，在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);
}
}
My query contains a Pageableparameter, so hasPagingOrSortingParameteris true, but it's also looking for a #pageableor #sortsequence inside the queryString, which I do not provide.
我的查询包含一个Pageable参数，hasPagingOrSortingParameter也是true，但它也在 中寻找#pageable或#sort序列queryString，我没有提供。
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.
我尝试#pageable在我的查询末尾添加（这是一个注释），这使得验证通过，但是在执行时失败，说查询需要一个附加参数：3 而不是 2。
Funny thing is that, if I manually change containsPageableOrSortInQueryExpressionfrom falseto truewhile running, the query works fine so I don't know why it's checking for that string to be at my queryStringand I don't know how to provide it.
有趣的是，如果我在运行时手动更改containsPageableOrSortInQueryExpressionfromfalse到true，查询工作正常，所以我不知道为什么它会检查该字符串是否在我的位置queryString，我不知道如何提供它。
Any help would be much appreciated.
任何帮助将非常感激。
Update 01/30/2018It seems that developers at spring-data project are working on a fix for this issue with a PR by Jens Schauder
2018年 1 月 30 日更新似乎 spring-data 项目的开发人员正在通过 Jens Schauder的PR解决此问题
回答by Emanuele Fusco
My apologies in advance, this is pretty much summing up the original question and the comment from Janar, however...
我提前道歉，这几乎是对原始问题和Janar评论的总结，但是......
I run into the same problem: I found the Example 50 of Spring Dataas 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.
我遇到了同样的问题：我发现Spring Data的示例 50作为我需要使用分页的本机查询的解决方案，但 Spring 在启动时抱怨我无法对本机查询使用分页。
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...
需要将 countQuery（在代码块中注释掉）Page<Author>用作查询的返回类型，需要“#pageable”注释周围的换行符来避免预期参数数量上的运行时错误（解决方法解决方法）。希望这个bug能早点修复。。。
回答by Dmitry Stolbov
This is a hack for program using Spring Data JPA before Version 2.0.4.
这是使用Spring Data JPA 版本 2.0.4 之前的程序的黑客。
Code has worked with PostgreSQL and MySQL :
代码已与 PostgreSQL 和 MySQL 一起使用：
public interface UserRepository extends JpaRepository<User, Long> {
@Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1 ORDER BY ?#{#pageable}",
countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
nativeQuery = true)
Page<User> findByLastname(String lastname, Pageable pageable);
}
ORDER BY ?#{#pageable}is for Pageable.
countQueryis for Page<User>.
ORDER BY ?#{#pageable}是为了Pageable。
countQuery是为了Page<User>。
回答by maricn
Just for the record, using H2 as testing database, and MySQL at runtime, this approach works (example is newest object in group):
只是为了记录，使用 H2 作为测试数据库，并在运行时使用 MySQL，这种方法有效（例如group 中的最新对象）：
@Query(value = "SELECT t.* FROM t LEFT JOIN t AS t_newer " +
"ON t.object_id = t_newer.object_id AND t.id < t_newer.id AND o_newer.user_id IN (:user_ids) " +
"WHERE t_newer.id IS NULL AND t.user_id IN (:user_ids) " +
"ORDER BY t.id DESC \n-- #pageable\n",
countQuery = "SELECT COUNT(1) FROM t WHERE t.user_id IN (:user_ids) GROUP BY t.object_id, t.user_id",
nativeQuery = true)
Page<T> findByUserIdInGroupByObjectId(@Param("user_ids") Set<Integer> userIds, Pageable pageable);
Spring Data JPA 1.10.5, H2 1.4.194, MySQL Community Server 5.7.11-log (innodb_version 5.7.11).
Spring Data JPA 1.10.5、H2 1.4.194、MySQL Community Server 5.7.11-log (innodb_version 5.7.11)。
回答by 689
I have exact same symptom like @Lasneyx. My workaround for Postgres native query
我有与@Lasneyx 完全相同的症状。我对 Postgres 本机查询的解决方法
@Query(value = "select * from users where user_type in (:userTypes) and user_context='abc'--#pageable\n", nativeQuery = true)
List<User> getUsersByTypes(@Param("userTypes") List<String> userTypes, Pageable pageable);
回答by XmasPiano
Try this:
尝试这个：
public interface UserRepository extends JpaRepository<User, Long> {
@Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1 ORDER BY /*#pageable*/",
countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
nativeQuery = true)
Page<User> findByLastname(String lastname, Pageable pageable);
}
("/* */"for Oracle notation)
("/* */"对于Oracle notation)
回答by Level_Up
I use oracle database and I did not get the result but an error with generated comma which d-man speak about above.
我使用 oracle 数据库，但没有得到结果，但生成的逗号错误，d-man 在上面谈到了这个错误。
Then my solution was:
然后我的解决方案是：
Pageable pageable = new PageRequest(current, rowCount);
As you can see without order by when create Pagable.
正如您在创建 Pagable 时无序所见。
And the method in the DAO:
以及 DAO 中的方法：
public interface UserRepository extends JpaRepository<User, Long> {
@Query(value = "SELECT * FROM USERS WHERE LASTNAME = ?1 /*#pageable*/ ORDER BY LASTNAME",
countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
nativeQuery = true)
Page<User> findByLastname(String lastname, Pageable pageable);
}
回答by Muhamed Saudi
Using "ORDER BY id DESC \n-- #pageable\n " instead of "ORDER BY id \n#pageable\n" worked for me with MS SQL SERVER
使用 "ORDER BY id DESC \n-- #pageable\n " 而不是 "ORDER BY id \n#pageable\n" 对我的 MS SQL SERVER 有用
回答by wmao
Both the following approaches work fine with MySQL for paginating native query. They doesn't work with H2 though. It will complain the sql syntax error.
以下两种方法都适用于 MySQL 对本机查询进行分页。不过，它们不适用于 H2。它会抱怨 sql 语法错误。
- ORDER BY ?#{#pageable}
- ORDER BY a.id \n#pageable\n
- ORDER BY ?#{#pageable}
- 按 a.id 排序 \n#pageable\n
回答by Fonda Wu
It does work as below:
它的工作原理如下：
public interface UserRepository extends JpaRepository<User, Long> {
@Query(value = "select * from (select (@rowid\:=@rowid+1) as RN, u.* from USERS u, (SELECT @rowid\:=0) as init where LASTNAME = ?1) as total"+
"where RN between ?#{#pageable.offset-1} and ?#{#pageable.offset + #pageable.pageSize}",
countQuery = "SELECT count(*) FROM USERS WHERE LASTNAME = ?1",
nativeQuery = true)
Page<User> findByLastname(String lastname, Pageable pageable);
}
回答by amit kumar
For me below worked in MS SQL
对我来说，下面在 MS SQL 中工作
@Query(value="SELECT * FROM ABC r where r.type in :type ORDER BY RAND() \n-- #pageable\n ",nativeQuery = true)
List<ABC> findByBinUseFAndRgtnType(@Param("type") List<Byte>type,Pageable pageable);
