# JDBC读取数据优化-fetch size

conn = getConnection();
long start = System.currentTimeMillis();
ps = conn.prepareStatement(sql);
rs = ps.executeQuery();
long mid_end = System.currentTimeMillis();
while (rs.next()) {
}
long end = System.currentTimeMillis();
rs.close();
System.out.println("Interval1=" + (mid_end - start));
System.out.println("Interval2=" + (end - mid_end));

SQL语句读取10000条记录，其中，

Interval1=160ms
Interval2=29252ms

By default, when Oracle JDBC runs a query, it retrieves a result set of 10 rows at a time from the database cursor. This is the default Oracle row fetch size value. You can change the number of rows retrieved with each trip to the database cursor by changing the row fetch size value.

Standard JDBC also enables you to specify the number of rows fetched with each database round-trip for a query, and this number is referred to as the fetch size. In Oracle JDBC, the row-prefetch value is used as the default fetch size in a statement object. Setting the fetch size overrides the row-prefetch setting and affects subsequent queries run through that statement object.

Fetch size is also used in a result set. When the statement object run a query, the fetch size of the statement object is passed to the result set object produced by the query. However, you can also set the fetch size in the result set object to override the statement fetch size that was passed to it.

Changes made to the fetch size of a statement object after a result set is produced will have no affect on that result set.

JDBC默认每执行一次检索，会从游标中提取10行记录，10就是默认的row fetch size值，通过设置row fetch size，可以改变每次和数据库交互，提取出来的记录行总数。需要注意的是，需要在获得检索结果集之前，设置fetch size，否则就是无效。

### Setting the Fetch Size

The following methods are available in all Statement, PreparedStatement, CallableStatement, and ResultSet objects for setting and getting the fetch size:

• void setFetchSize(int rows) throws SQLException

• int getFetchSize() throws SQLException

rs = ps.executeQuery();
rs.setFetchSize(10000);

ps = conn.prepareStatement(sql);
ps.setFetchSize(10000);

After you have run the query, you can call setFetchSize on the result set object to override the statement object fetch size that was passed to it. This will affect any subsequent trips to the database to get more rows for the original query, as well as affecting any later refetching of rows.

To set the fetch size for a query, call setFetchSize on the statement object prior to running the query. If you set the fetch size to N, then N rows are fetched with each trip to the database.

PrepareStatement

• setFetchSize

void setFetchSize(int rows)
throws SQLException

Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for ResultSetobjects generated by this Statement. If the value specified is zero, then the hint is ignored. The default value is zero.

• Parameters:

• rows - the number of rows to fetch

• Throws:

• SQLException - if a database access error occurs, this method is called on a closed Statement or the condition rows >= 0 is not satisfied.

• Since:

• 1.2

• getFetchSize()

ResultSet

• setFetchSize

void setFetchSize(int rows)
throws SQLException

Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for this ResultSet object. If the fetch size specified is zero, the JDBC driver ignores the value and is free to make its own best guess as to what the fetch size should be.  The default value is set by the Statement object that created the result set.  The fetch size may be changed at any time.

• Parameters:

• rows - the number of rows to fetch

• Throws:

• SQLException - if a database access error occurs; this method is called on a closed result set or the condition rows >= 0 is not satisfied

• Since:

• 1.2

• getFetchSize()

Statement

• setFetchSize

void setFetchSize(int rows)
throws SQLException

Gives the JDBC driver a hint as to the number of rows that should be fetched from the database when more rows are needed for ResultSetobjects generated by this Statement. If the value specified is zero, then the hint is ignored. The default value is zero.

• Parameters:

• rows - the number of rows to fetch

• Throws:

• SQLException - if a database access error occurs, this method is called on a closed Statement or the condition rows >= 0 is not satisfied.

• Since:

• 1.2

• getFetchSize()

1. Fetch相当于读缓存，如果使用setFetchSize设置Fetch Size为10000，本地缓存10000条记录，每次执行rs.next，只是内存操作，不会有数据库网络消耗，效率就会高些。但需要注意的是，Fetch Size值越高则占用内存越高，要避免出现OOM错误。

2. 建议执行SQL语句之前设置，即ps.executeQuery();之前使用setFetchSize()函数设置。

©️2019 CSDN 皮肤主题: 大白 设计师: CSDN官方博客