Retrieving a few records at one time from database with JPA

Hibernate ORM
1

I am working on implementing DB records streaming service from PostgreSQL database. I need to retrieve some defined number of records and send them through the network, then retrieve next batch of records and send them again and again.
I would like to use Hibernate JPA for that purpose, but I cannot impose Hibernate to use defined fetch size. I tried to set “hibernate.jdbc.fetch_size” property in the persistence.xml file and set a query hint but without any success.

I know I can use pagination but it means that several queries will be invoked to the database. If in the meantime database table changes the pagination result will be inconsistent.

Is it possible to execute a single query to database and retrieve some number of records for one time, then use the same query to retrieve next records? I know I can achieve this using JDBC cursors bypassing Hibernate…

2

I would like to use Hibernate JPA for that purpose, but I cannot impose Hibernate to use defined fetch size. I tried to set “hibernate.jdbc.fetch_size” property in the persistence.xml file and set a query hint but without any success.

You have to set the hibernate.jdbc.fetch_size or the QueryHints.HINT_FETCH_SIZE since you want to fetch data in chunks. Without setting the fetch size, PostgreSQL will fetch the entire result set from the DB and cache it in the Driver.

You can use streaming or scrolling as explained in this article:

Stream<Post> postStream = entityManager
.createNativeQuery(
	"SELECT p " +
	"FROM post p " +
	"ORDER BY p.created_on DESC", Post.class)
.setHint( QueryHints.HINT_FETCH_SIZE, 50 )
.getResultStream();

However, a paginated query can better take advantage of indexes than a streaming query that might require a table scan, so keep that in mind.