Something like cursor or recordset iterator in Hibernate or JPA?

Question

Are there any means to work with long query results in Hibernate? What if I want to draw table with million of records and allow user to navigate over it?

The goal is not to transfer all data to client at the time and handle the current position.

Swanhildas

Answer 1

You can use simple mechanism of FirstResult and MaxResults in a Query object.

query.setFirstResult(5);
query.setMaxResults(5);

Above will fetch 5 records from fifth record.

You can use ScrollableResults but it will be slower compared to above one for large results.

Ensphere

Answer 2

You may try with Hibernate ScrollableResults, I quote:

A result iterator that allows moving around within the results by arbitrary increments. The Query / ScrollableResults pattern is very similar to the JDBC PreparedStatement/ ResultSet pattern and the semantics of methods of this interface are similar to the similarly named methods on ResultSet.

Koger

Answer 3

To improve queries with large dataset, use a keyset pagination and avoid offset pagination if possible because using an offset needs to read all data while using a keyset pagination (filter) reduces the dataset (improvements can be significant).

Here is a good article https://use-the-index-luke.com/no-offset

Hibbert