无法使用ScrollableResults获取批处理中的结果集 [英] Unable to fetch result set in Batches using ScrollableResults

问题描述

我需要从Postgres数据库表中过滤掉数据.该查询可以返回数百万个数据，因此无法将数据保留在内存中，因为这将导致OOME.我尝试使用ScrollableResult批量获取数据，但是正在触发单个查询，如在日志中看到的那样.以下代码段用于生成.

I need to filter out data from Postgres database table . The query can return millions of data so it is not possible to keep data in memory as it will cause OOME . I tried using ScrollableResult to fetch data in batches but a single query is being fired as seen in logs . The following snippet is used to generate .

@Transactional
public void getObjectUsingScrollable() {
    String statement = "select t1.name,t2.address from table1 t1 JOIN table2 t2 ON t1.id=t2.table1Id WHERE t1.status='OPEN' ORDER BY t1.date DESC "
    StatelessSession session = ((Session) entityManager.getDelegate()).getSessionFactory().openStatelessSession();
    org.hibernate.query.Query query = session
            .createNativeQuery(statement);
    query.setFetchSize(5);
    query.setReadOnly(true);
    query.setLockMode("t1", LockMode.READ);
    query.setCacheable(false);
    ScrollableResults results = query.scroll(ScrollMode.FORWARD_ONLY);
    while (results.next()) {
        Object obj=results.get(0);
    }
    results.close();
    session.close();
}

我尝试使用LockMode.NONE.为什么在while循环每第5次迭代后就不触发查询?休眠版本-5.4.18Postgres版本-9.4-1200-jdbc41

I have tried using LockMode.NONE . Why doesn't this fire up a query after every 5th iteration of while loop? Hibernate Version- 5.4.18 Postgres Version - 9.4-1200-jdbc41

推荐答案

这不是滚动结果的工作方式，您只会看到一个查询.提取大小只是JDBC驱动程序每次往返数据库仅提取那么多行的提示.

That's not how scrollable results work, you will see only one query. The fetch size is just a hint for the JDBC driver to fetch only that many rows per round-trip to the database.

本质上，可滚动结果就像在数据库上打开N个(提取大小)元素的打开游标一样.驱动程序将为您提供这些行，并且当您请求更多行时，它可能会通过游标获取接下来的N行.

Essentially, a scrollable result is like an open cursor on the database where you fetch N (fetch size) elements. The driver serves you these rows and when you request more, it might fetch the next N rows through the cursor.

使用显示的代码，除非将数据保留在某个地方，否则不应获得OOME.至少Hibernate不会这样保留数据.

Using the code you have shown, you shouldn't get an OOME unless you keep the data around somewhere. At least Hibernate will not hold on to the data this way.

这篇关于无法使用ScrollableResults获取批处理中的结果集的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！