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?

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

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

Solution 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

Solution 4

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.

Solution 5

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

View more solutions

Comments

[extraneon]

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

[Sergey Ushakov]

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

[Hasan Tuncay]

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