java 有没有办法使用 JPA/hibernate 滚动结果？

Is there are way to scroll results with JPA/hibernate?

提问by yura

I found some hint in Toplink

我在 Toplink 中找到了一些提示

Query query = em.createQuery("SELECT e FROM Employee e ORDER BY e.lastName ASC, e.firstName ASC");
query.setHint("eclipselink.cursor.scrollable", true);
ScrollableCursor scrollableCursor = (ScrollableCursor)query.getSingleResult();
List<Employee> emps = scrollableCursor.next(10);

is there are jpa/hibernate alternative?

是否有 jpa/hibernate 替代方案？

回答by Pascal Thivent

To my knowledge, there is nothing standard in JPA for that.

据我所知，在 JPA 中没有任何标准。

With Hibernate, the closest alternative I'm aware of would be the Query/ ScrollableResultsAPIs. From the documentation:

对于 Hibernate，我所知道的最接近的替代方案是Query/ ScrollableResultsAPI。从文档：

10.4.1.6. Scrollable iteration

If your JDBC driver supports scrollable ResultSets, the Query interface can be used to obtain a ScrollableResults object that allows flexible navigation of the query results.

Query q = sess.createQuery("select cat.name, cat from DomesticCat cat " +
                            "order by cat.name");
ScrollableResults cats = q.scroll();
if ( cats.first() ) {

    // find the first name on each page of an alphabetical list of cats by name
    firstNamesOfPages = new ArrayList();
    do {
        String name = cats.getString(0);
        firstNamesOfPages.add(name);
    }
    while ( cats.scroll(PAGE_SIZE) );

    // Now get the first page of cats
    pageOfCats = new ArrayList();    
    cats.beforeFirst();    
    int i=0;    
    while( ( PAGE_SIZE > i++ ) && cats.next() ) pageOfCats.add( cats.get(1) );

}

cats.close()

Note that an open database connection and cursor is required for this functionality. Use setMaxResult()/setFirstResult()if you need offline pagination functionality.

10.4.1.6. 可滚动迭代

如果您的 JDBC 驱动程序支持可滚动 ResultSet，则可以使用 Query 接口获取 ScrollableResults 对象，该对象允许灵活导航查询结果。

Query q = sess.createQuery("select cat.name, cat from DomesticCat cat " +
                            "order by cat.name");
ScrollableResults cats = q.scroll();
if ( cats.first() ) {

    // find the first name on each page of an alphabetical list of cats by name
    firstNamesOfPages = new ArrayList();
    do {
        String name = cats.getString(0);
        firstNamesOfPages.add(name);
    }
    while ( cats.scroll(PAGE_SIZE) );

    // Now get the first page of cats
    pageOfCats = new ArrayList();    
    cats.beforeFirst();    
    int i=0;    
    while( ( PAGE_SIZE > i++ ) && cats.next() ) pageOfCats.add( cats.get(1) );

}

cats.close()

请注意，此功能需要打开的数据库连接和游标。如果您需要离线分页功能，请使用 setMaxResult()/ setFirstResult()。

回答by JanKanis

Judging from the other answers JPA does not support scrolling directly, but if you use Hibernate as JPA implementation you can do

从其他答案来看，JPA 不支持直接滚动，但是如果您使用 Hibernate 作为 JPA 实现，则可以执行

javax.persistence.Query query = entityManager.createQuery("select foo from bar");
org.hibernate.Query hquery = query.unwrap(org.hibernate.Query);
ScrollableResults results = hquery.scroll(ScrollMode.FORWARD_ONLY);

That accesses the underlying Hibernate api for the scrolling but you can use all the features of JPA querying. (At least for criteria queries the JPA api has some features that are not in the old Hibernate api.)

它访问底层 Hibernate api 以进行滚动，但您可以使用 JPA 查询的所有功能。（至少对于标准查询，JPA api 有一些旧的 Hibernate api 没有的功能。）

回答by Yves Martin

When processing large number of entities in a large project code based on List<E>instances, I has to write a really limited List implementation with only Iteratorsupport to browse a ScrollableResultswithout refactoring all services implementations and method prototypes using List<E>.

在基于List<E>实例的大型项目代码中处理大量实体时，我必须编写一个非常有限的 List 实现，仅Iterator支持浏览ScrollableResults而不使用List<E>.

This implementation is available in my IterableListScrollableResults.java Gist

这个实现在我的IterableListScrollableResults.java Gist 中可用

It also regularly flushes Hibernate entities from session. Here is a way to use it, for instance when exporting all non archived entities from DB as a text file with a forloop:

它还定期从会话中刷新 Hibernate 实体。这是一种使用它的方法，例如将所有未归档的实体从数据库导出为带有for循环的文本文件时：

Criteria criteria = getCurrentSession().createCriteria(LargeVolumeEntity.class);
criteria.add(Restrictions.eq("archived", Boolean.FALSE));
criteria.setReadOnly(true);
criteria.setCacheable(false);
List<E> result = new IterableListScrollableResults<E>(getCurrentSession(),
        criteria.scroll(ScrollMode.FORWARD_ONLY));
for(E entity : result) {
    dumpEntity(file, entity);
}

With the hope it may help

希望它可以帮助

回答by Pau

In JPA you can use query.setFirstResult and query.setMaxResults

在 JPA 中，您可以使用 query.setFirstResult 和 query.setMaxResults

回答by frandevel

Also using Spring Data would be an option. There you can specify the query and pass, as a parameter, a "PageRequest" in which you indicate the page size and the page number:

也可以选择使用 Spring Data。在那里您可以指定查询并传递作为参数的“PageRequest”，您可以在其中指示页面大小和页码：

Page<User> users = repository.findAll(new PageRequest(1, 20));

For this you need to extend a PagingAndSortingRepository.

为此，您需要扩展 PagingAndSortingRepository。

Just as another alternative for paging over the results.

就像对结果进行分页的另一种选择一样。

Of course, underneath, it's using Hibernate, Toplink or whatever JPA implementation you configure.

当然，在底层，它使用 Hibernate、Toplink 或您配置的任何 JPA 实现。