MySQL 流式读取_Java 流式查询数据库

Java 的 MySQL JDBC 封装了游标操作。通过简单设置几个参数(固定值)，就能实现流式查询，避免一次返回数据过大导致 OOM。

PreparedStatement ps = connection.prepareStatement(sql, ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY);

// 如果给 JDBC URL 设置 defaultFetchSize 参数那么将使流式获取全局生效

ps.setFetchSize(Integer.MIN_VALUE);

ResultSet rs = ps.executeQuery();

while (rs.next()) {

// do sth.

}

MySQL 官方文档 对 Streaming ResultSet 描述如下：

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.

默认情况下，ResultSet 是被完整获取并存储到内存里的——基于 MySQL 网络协议的设计，多数情况下这是最有效的方式去操作和实现。如果你的 ResultSet 会有大量行或者大量数值以至于 JVM 内存不足无法分配足够的堆空间，你可以让 driver 流式(一次一个地)返回结果。

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

开启这个特性，需要以如下方式创建 Statement 对象：

stmt = conn.createStatement(java.sql.ResultSet.TYPE_FORWARD_ONLY,

java.sql.ResultSet.CONCUR_READ_ONLY);

stmt.setFetchSize(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.

【ResultSet 设置为 CONCUR_READ_ONLY、TYPE_FORWARD_ONLY 而且 fetchSize 为 Integer.MIN_VALUE】的 Statement 是给 driver 的一种特定信号，用于启用流式查询。该 Statement 返回的所有 ResultSet 都是一行一行获取的。

There are some caveats with this approach. You must read all of the rows in the result set (or close it) before you can issue any other queries on the connection, or an exception will be thrown.

注意：在使用该 Connection 发起其他请求前，你必须在读完结果集中所有的行(或者关闭它)，否则将会抛出异常。

The earliest the locks these statements hold can be released (whether they be MyISAM table-level locks or row-level locks in some other storage engine such as InnoDB) is when the statement completes.

无论是 MyISAM 表级锁还是其他存储引擎(如 InnoDB)的行级锁，当 Statement 完成，它所持有的锁才能被释放。

If the statement is within scope of a transaction, then locks are released when the transaction completes (which implies that the statement needs to complete first). As with most other databases, statements are not complete until all the results pending on the statement are read or the active result set for the statement is closed.

如果语句在事务内，锁会在事务完成时释放(这也意味着 Statement 需要先完成)。与大多数其他数据库一样，Statement 在读取完语句的所有结果或 Statement 的当前 ResultSet 关闭之后被认为是完成的。

Therefore, if using streaming results, process them as quickly as possible if you want to maintain concurrent access to the tables referenced by the statement producing the result set.

因此，如果你想维持被【正在输出结果集的 Statement】所引用的表的并发访问性能，那么就尽快处理掉那些流式返回的结果集。