结果集中的fetchsize默认设置为0 [英] fetchsize in resultset set to 0 by default

问题描述

我必须查询数据库，结果集非常大。我使用 MySQL 作为数据库。为了避免在经过大量搜索后出现OutOfMemoryError，我有两个选择：一个使用 LIMIT （特定于数据库），另一个使用 jdbc fetchSize属性。

I have to query a database and result set is very big. I am using MySQL as data base. To avoid the "OutOfMemoryError" after a lot of search I got two options: One using LIMIT(specific to database) and other is using jdbc fetchSize attribute.

我已经测试了它正在运行的选项1（ LIMIT ）但是这不是理想的解决方案。我不想这样做。

I have tested the option 1(LIMIT) an it is working but it is not the desired solution. I do not want to do it.

使用 jdbc 我发现 ResultSet 默认情况下，size设置为0。如何将其更改为其他值。我尝试了以下方法：

Using jdbc I found out that ResultSet size is set to 0 by default. How can I change this to some other value. I tried the following:

a）首先尝试：

rs = preparedStatement.executeQuery();
rs.setFetchSize(1000);  //Not possible as exception occurs before.

b）秒T即使不存在，我也需要与数据库通信多个timry：

b) Second T Even if this is not there then also I need to communicate to databse multiple timry:

rs.setFetchSize(1000);  //Null pointer exception(rs is null).
rs = preparedStatement.executeQuery();

c）第三次尝试：

preparedStatement = dbConnection.createStatement(query);
preparedStatement.setFetchSize(1000);

这些都不起作用。任何帮助表示感谢！

None of this is working. Any help appreciated!

编辑：

我不想使用解决方案限制因为：
a）我的结果集中有数百万行。现在做多个查询很慢。我的假设是数据库需要多个查询，例如

I do not want a solution using limit because: a) I have millions of rows in my result set. Now doing multiple query is slow. My assumption is that database takes multiple queries like

SELECT *  FROM a LIMIT 0, 1000
SELECT *  FROM a LIMIT 1000, 2000

作为两个不同的查询。

b）代码看起来很乱，因为你需要有额外的计数器。

b) The code is looks messy because you need to have additional counters.

推荐答案

MySQL JDBC驱动程序总是提取所有行，除非获取大小设置为 Integer.MIN_VALUE 。

The MySQL JDBC driver always fetches all rows, unless the fetch size is set to Integer.MIN_VALUE.

请参阅MySQL Connector / J JDBC API实施说明：

默认情况下，ResultSets被完全检索并存储在内存中。
在大多数情况下，这是最有效的操作方式，由于
，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.

要启用此功能，请按以下方式以
创建Statement实例：

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

只进，只读结果集与fetch
的组合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.

这篇关于结果集中的fetchsize默认设置为0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！