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…