Correct use of MySQL jdbc Setfetchsize () method to resolve JDBC processing large result set Java.lang.OutOfMemoryError:Java heap space

Yesterday in the project you need to export the query results of the log.

The implementation of the log export function is like this, enter the query criteria, and then export the query results. Because of the large amount of log data. Many times, there are hundreds of millions of records.

The previous solution was to query multiple times, and then limit the number of bars per query using limit. and then export. As a result, efficiency is relatively inefficient.

So can we just make a single query and pour out all the results? So I used the query once, not using the limit paging. As a result, the Java.lang.OutOfMemoryError:Java heap space problem occurs.

It appears that the DB server will send the result set of the query to the Java side in memory all at once. The Oom problem occurs because the result set is large.

The first thing I think about is the cursor function. So is it possible to use a cursor and take it slowly from the server side? Surf the internet for a bit, we all say that MySQL does not support cursor function and so on.

Then I went to see the JDBC code. Found the Setfetchsize () method, the result is set, but it does not take effect, or there is an oom problem.

My settings are as follows

[Java]View PlainCopy

1. Ps=conn.con.preparestatement ("select * from bigTable");
2. Ps.setfetchsize (1000);

Later, in MySQL, I saw this approach:

[Java]View PlainCopy

1. PS = (preparedstatement) con.preparestatement ("select * from BigTable",
2. Resultset.type_forward_only, resultset.concur_read_only);
3. Ps.setfetchsize (Integer.min_value);
4. Ps.setfetchdirection (Resultset.fetch_reverse);

Correct use of MySQL jdbc Setfetchsize () method to resolve JDBC processing large result set Java.lang.OutOfMemoryError:Java heap space