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?

score 12 · Accepted Answer · answered Nov 13 '10 at 09:53

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.

don't forget to put the close() into a try ... finally. – Hasan Tuncay Aug 07 '14 at 11:54 — Hasan Tuncay, Aug 07 '14 at 11:54

JanKanis · Answer 2 · 2017-02-23T10:08:58.300

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

score 4 · Answer 3 · answered Apr 01 '14 at 14:02

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

score 1 · Answer 4 · answered Nov 16 '10 at 14:50

1

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

answered Nov 16 '10 at 14:50

Pau

803
1
6
12

5

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). – extraneon May 05 '11 at 18:40
1

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... – Sergey Ushakov Mar 15 '14 at 05:03

score 0 · Answer 5 · answered Oct 31 '13 at 16:44

0

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.

answered Oct 31 '13 at 16:44

frandevel

747
7
22

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) – Bogdan Mart Aug 19 '22 at 12:56

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

5 Answers5

10.4.1.6. Scrollable iteration

Linked