Is there a way to scroll through JPA / hibernate results?

Question

I found a tip on 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 an alternative to jpa / hibernate?

Answer 1

As far as I know, there is nothing standard for JPA in JPA.

With Hibernate, the closest alternative that I know of will be the Query / ScrollableResults API. From the documentation:

10.4.1.6. Scrollable iteration

If the JDBC driver supports scrollable ResultSets, the query interface can be used to retrieve the ScrollableResults object, which allows flexible navigation for querying 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() 

Please note that there is an open connection to the database and this requires cursor functionality. use setMaxResult() / setFirstResult() if you need a standalone pagination function.

Answer 2

When processing a large number of objects in large project code based on List<E> instances, I have to write a really limited List implementation with Iterator support to view ScrollableResults without reorganizing all service implementations and prototype methods using List<E> .

This implementation is available in my IterableListScrollableResults.java Gist

It also regularly flushes Hibernate objects from the session. Here is a way to use it, for example, when exporting all unarchived objects from the database 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 hope he can help.

Answer 3

Judging by other answers, JPA does not support scrolling directly, but if you use Hibernate as a 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); 

This is a reference to the underlying Hibernate api for scrolling, but you can use all the JPA query functions. (At least for criteria queries, the JPA api has some functions that are not in the old Hibernate api.)

Answer 4

Also using the Spring Data parameter would be an option. There you can specify the request and pass as the parameter "PageRequest", which indicates the page size and page number:

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

To do this, you need to extend the PagingAndSortingRepository.

Also as an alternative for paging results.

Of course, under it, it uses Hibernate, Toplink, or any other JPA implementation that you configure.

Answer 5

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