Java Spring Data - 为什么不能使用本机查询进行分页
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28529584/
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 - Why it's not possible to have paging with native query
提问by vtor
Let's say we have an entity called MyEntity. It is possible to query pageable results using @Queryand with named queries, e.g.
假设我们有一个名为 的实体MyEntity。可以使用@Query命名查询和使用命名查询来查询可分页结果，例如
@Query(value = "select e from MyEntity e where e.enabled = true")
Page<MyEntity> findAllEnabled(Pageable pageable);
However, it is not possible to achieve the same with native query, so this
但是，使用本机查询无法实现相同的效果，因此这
@Query(value = "select * from my_entity where enabled = true", nativeQuery = true)
Page<MyEntity> findAllEnabled(Pageable pageable);
won't work.
不会工作。
What are the reasons behind this? Is it possible to make Pageable working with native queries?
这背后的原因是什么？是否可以使 Pageable 与本机查询一起使用？
采纳答案by mavarazy
This is description, given in spring data jpa documentation (http://docs.spring.io/spring-data/jpa/docs/1.8.0.M1/reference/html/)
这是描述，在 spring 数据 jpa 文档（http://docs.spring.io/spring-data/jpa/docs/1.8.0.M1/reference/html/）中给出
Native queriesThe @Query annotation allows to execute native queries by setting the nativeQuery flag to true. Note, that we currently don't support execution of pagination or dynamic sorting for native queries as we'd have to manipulate the actual query declared and we cannot do this reliably for native SQL.
本机查询@Query 注释允许通过将 nativeQuery 标志设置为 true 来执行本机查询。请注意，我们目前不支持对本机查询执行分页或动态排序，因为我们必须操作声明的实际查询，而我们无法对本机 SQL 可靠地执行此操作。
JPQL abstracts SQL implementation and it's providers specifics, and makes it responsibility of ORM framework to generate correct SQL.
JPQL 抽象了 SQL 实现及其提供者的细节，并使 ORM 框架负责生成正确的 SQL。
So by using Pagination in JPQL form, Spring just needs to generate correct JPQL, and it will be interpreted on ORM level to correct SQL.
While doing so with SQL, would imply that Spring knows how to generated correct SQL for the vast majorities of RDBMS, duplicating ORM functionality, which is too much overhead.
所以通过使用 JPQL 形式的 Pagination，Spring 只需要生成正确的 JPQL，并将在 ORM 级别对其进行解释以纠正 SQL。
使用 SQL 这样做时，意味着 Spring 知道如何为绝大多数 RDBMS 生成正确的 SQL，复制 ORM 功能，这会带来太多开销。
回答by Michael Simons
I don't know if this is still relevant to you: At least in Spring Data JPA 1.9.4 you can specify two queries.
我不知道这是否仍然与您相关：至少在 Spring Data JPA 1.9.4 中，您可以指定两个查询。
Given a repository:
给定一个存储库：
interface FoobarEntityRepository extends JpaRepository<FoobarEntity, Integer> {
Page findFoobarsSpecialQuery(String someParameter, final Pageable pageable);
}
You can add 2 native queries to your entity, one for the query itself and one for the count statement:
您可以向实体添加 2 个本机查询，一个用于查询本身，另一个用于 count 语句：
@Entity
@SqlResultSetMappings({
@SqlResultSetMapping(name = "SqlResultSetMapping.count", columns = @ColumnResult(name = "cnt"))
})
@NamedNativeQueries({
@NamedNativeQuery(
name = "FoobarEntity.findFoobarsSpecialQuery",
resultClass = DailyPictureEntity.class,
query = "Select * from foobars f where someValue = :someParameter "
),
@NamedNativeQuery(
name = "FoobarEntity.findFoobarsSpecialQuery.count",
resultSetMapping = "SqlResultSetMapping.count",
query = "Select count(*) as cnt from foobars f where someValue = :someParameter "
)
})
FoobarEntity {
}
The trick is to specify the count query with the suffix .count. This works also with the Spring Data @Queryannotation.
诀窍是使用后缀指定计数查询.count。这也适用于 Spring Data@Query注释。
Notice that you need a SQL result set mapping for the count query, though.
但是请注意，您需要一个用于计数查询的 SQL 结果集映射。
This works actually pretty nice.
这实际上非常好。
回答by Stephane L
There is a way to use Pageable with native queries with the SpEL capacity of Spring data, it is mention here.
有一种方法可以将 Pageable 与具有 Spring 数据的 SpEL 容量的本机查询一起使用，这里提到了。
You can find an example in this repository.
您可以在此存储库中找到示例。
/**
* @see DATAJPA-564
*/
@Query(
value = "select * from (select rownum() as RN, u.* from SD_User u) where RN between ?#{ #pageable.offset -1} and ?#{#pageable.offset + #pageable.pageSize}",
countQuery = "select count(u.id) from SD_User u", nativeQuery = true)
Page<User> findUsersInNativeQueryWithPagination(Pageable pageable);
The sort functionnality will not work properly if there is a subquery in the fromclause or your native query and you wish to apply a dynamic sort on it. The way it can be done is to move the subquery in the whereclause.
Spring data will append to the endof your request " order by "if there is a Sortobject in the Pageable. (with Spring data 1.10.3)
如果from子句或您的本机查询中有子查询，并且您希望对其应用动态排序，则排序功能将无法正常工作。可以完成的方法是在where子句中移动子查询。
春天的数据将追加到年底你的要求" order by "，如果有一个Sort的对象Pageable。（使用 Spring 数据 1.10.3）
A better way is to convert the native query in jpql if possible.
如果可能，更好的方法是在 jpql 中转换本机查询。
