Stream rows from PostgreSQL (with fetch size)

Question

I would like to stream results from PostgreSQL 11.2 and not read all results to memory at once. I use the newest stable SpringBoot 2.1.4.RELEASE.

I read the article how to do it in MySQL. http://knes1.github.io/blog/2015/2015-10-19-streaming-mysql-results-using-java8-streams-and-spring-data.html I also read article how to do it in PostgreSQL: Java 8 JPA Repository Stream row-by-row in Postgresql

I have repository like that:

public interface ProductRepository extends JpaRepository<Product, UUID> {
    @Query("SELECT p from Product p")
    @QueryHints(value = @QueryHint(name = HINT_FETCH_SIZE, value = "50"))
    Stream<Product> streamAll();
}

Than I use the stream that way:

  productRepository.streamAll().forEach(product -> export(product));

To make the example easier, 'export' method is completely empty.

When I call the method I see Hibernate query

Hibernate: select product0_.id as id1_0_, product0_.created as created2_0_, product0_.description as descript3_0_, product0_.name as name4_0_, product0_.product_type_id as product_5_0_ from products product0_ order by product0_.id

and after some time I have OutOfMemoryError. The query hint didn't help.

How to read data using Spring Boot repository (or even EntityManager) and load rows from DB in optimal way. I know that I can make pagination, but as in articles was written, it is not the most optimal way.

Answer 1

You must detach the entity after your work finishes.

import javax.persistence.EntityManager;
...
@Autowired
private EntityManager entityManager;
... 
// Your business logic
productRepository.streamAll().forEach(product -> {
   export(product);
   // must detach so that garbage collector can reclaim the memory.
   entityManager.detach(product);
});