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?

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.