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

Question

I found some hint in 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?

Aftershock

Answer 1

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

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

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.

Hillary

don't forget to put the close() into a try ... finally. – Clemons

Answer 2

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

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.)

Nonperishable

Answer 3

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 Iterator support to browse a ScrollableResults without refactoring all services implementations and method prototypes using List<E>.

This implementation is available in my 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 for loop:

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

Freighter

Answer 4

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

Polad

That's not scrolling. You fire a new query every time, and the overhead really adds up. Plus, you need to sort on something for reliable paging which also adds to query times. Unfortunately it does seem to be the only way to get something resembling scrolling (in functionality if not performance). – Modish

You can also get some records skipped or doubled, in case you are going to "scroll" a live database with data being added/deleted online, and additions/deletions happening near page boundaries... – Corpora

Answer 5

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:

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

For this you need to extend a PagingAndSortingRepository.

Just as another alternative for paging over the results.

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

Acidic

This is definitely not what is wanted. Pageable would create multiple requests. Scrollable result is single request that maintains result set opened, but loads into RAM only small batches. And furher more requests for pages beyond first couple would stress DB a bit (depending on actual query plan and amount of joins, ordering condition) – Font