大型数据集的JDBC ResultSet内部机制 [英] JDBC ResultSet internal mechanism of fetching large datasets

问题描述

JDBC结果集是否可以在一个网络调用中获取所有数据以进行SQL查询?考虑查询select * from table where timestamp > 1597937895.现在，此查询有超过一百万行.结果集是否可以在一个网络调用中提取所有行?还是在读取结果集时获取一批行?因为我还需要查看内存使用情况.因此澄清.众所周知，ResultSet在一个网络调用中获取所有数据.这是唯一的行为，还是有其他方法可以告诉结果集批量获取数据?

Does JDBC result set fetch all data in one network call for a SQL query? Consider the query select * from table where timestamp > 1597937895. Now there are more than 1 million rows for this query. Does result set fetch all the rows in one network call? Or does it fetch batch of rows as and when the result set is read? Because I need to look at memory usage as well. Hence clarifying. I am known by the fact that ResultSet fetches all data in one network call. Is this is the only behaviour or is there any other way to tell result set to fetch data in batches?

推荐答案

获取行和批处理的确切行为因数据库系统和驱动程序而异.有些将始终进行批处理，某些默认情况下将一次获取所有行，而某些则取决于结果集类型或其他因素.

The exact behaviour for fetching rows and batching varies by database system and driver. Some will always batch, some will - by default - fetch all rows at once, and for some it depends on the result set type or other factors.

默认情况下，MySQL Connector/J驱动程序将在执行时获取内存中的所有行.如

By default, the MySQL Connector/J driver will fetch all rows in memory on execute. This can be changed to either a row-streaming or a cursor-based fetch using a batch size, as documented on JDBC API Implementation Notes under Resultset:

默认情况下，完全检索ResultSet并将其存储在内存中. 在大多数情况下，这是最有效的操作方式，并且由于 MySQL网络协议的设计更易于实现.如果 您正在使用具有大量行的ResultSet或 较大的值，并且无法在JVM中为内存分配堆空间 必需，您可以告诉驱动程序将结果流回一行 一次.

By default, ResultSets are completely retrieved and stored in memory. In most cases this is the most efficient way to operate and, due to the design of the MySQL network protocol, is easier to implement. If you are working with ResultSets that have a large number of rows or large values and cannot allocate heap space in your JVM for the memory required, you can tell the driver to stream the results back one row at a time.

要启用此功能，请在 如下方式:

To enable this functionality, create a Statement instance in the following manner:

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,
              java.sql.ResultSet.CONCUR_READ_ONLY); 
stmt.setFetchSize(Integer.MIN_VALUE); 

只读，只读结果集与访存的组合 Integer.MIN_VALUE的大小是向驱动程序发送信号的信号 结果集逐行显示.之后，使用 语句将逐行检索.

The combination of a forward-only, read-only result set, with a fetch size of Integer.MIN_VALUE serves as a signal to the driver to stream result sets row-by-row. After this, any result sets created with the statement will be retrieved row-by-row.

[..但是请阅读警告..]

另一种选择是使用基于游标的流来检索集合 每次的行数.可以通过设置连接来完成 将属性useCursorFetch设置为true，然后调用setFetchSize(int) 其中int是每次要获取的所需行数:

Another alternative is to use cursor-based streaming to retrieve a set number of rows each time. This can be done by setting the connection property useCursorFetch to true, and then calling setFetchSize(int) with int being the desired number of rows to be fetched each time:

conn = DriverManager.getConnection("jdbc:mysql://localhost/?useCursorFetch=true", "user", "s3cr3t");
stmt = conn.createStatement();
stmt.setFetchSize(100);
rs = stmt.executeQuery("SELECT * FROM your_table_here");

这篇关于大型数据集的JDBC ResultSet内部机制的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！