从PostgreSQL流式传输行(具有提取大小) [英] Stream rows from PostgreSQL (with fetch size)

问题描述

我想从PostgreSQL 11.2流式传输结果，而不是一次将所有结果读到内存中.我使用最新的稳定版SpringBoot 2.1.4.RELEASE.

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.

我阅读了这篇文章如何在MySQL中做到这一点. http://knes1.github.io/blog/2015/2015-10-19-streaming-mysql-results-using-java8-streams-and-spring-data.html 我还阅读了文章如何在PostgreSQL中做到这一点: Java 8 JPA存储库流逐行在Postgresql中

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

我有这样的存储库:

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();
}

比我那样使用流:

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

为了简化示例，"export"方法完全为空.

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

当我调用该方法时，我看到了Hibernate查询

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

，过了一段时间，我遇到了OutOfMemoryError.查询提示没有帮助.

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

如何使用Spring Boot存储库(甚至EntityManager)读取数据，以及如何以最佳方式从数据库加载行.我知道我可以进行分页，但是正如在撰写文章中所述，这不是最佳方法.

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.

推荐答案

目前使用spring检索所有数据，并且Stream仅应用于已在内存中的数据.

At the moment using spring all the data are retrieved and the Stream is applied only to data already in memory.

如果您查看 org.springframework.data.jpa.provider.PersistenceProvider 的来源，似乎它使用了 ScrollableResults 来流化数据.

If you look at the source of org.springframework.data.jpa.provider.PersistenceProvider it seems that it uses a ScrollableResults to stream over the data.

通常 ScrollableResults 检索内存中的所有数据.

Generally a ScrollableResults retrieve all data in memory.

您可以使用MySql数据库找到有趣的完整分析

You can find an interesting complete analysis using a MySql database here, but probably the same works for a Postgres database.

因此，如果您认为使用的解决方案实际上不需要使用大量内存，那么它也会这样做，因为基础实现未使用最佳实现.

So also if you think to use a solution that doesn't need to use a lot memory in reality it does because the underlying implementation is not using an optimal implementation.

这篇关于从PostgreSQL流式传输行(具有提取大小)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！