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