Out of memory while executing a large query?

Question

I got this error while trying to make a big request.

java.lang.OutOfMemoryError: Java heap space 

I searched and found that using the setAutoCommit (false) and setFetchSize methods for my prepared statement can help handle a large request. However, when I used it, I got this error.

 java.sql.SQLException: Illegal value for setFetchDirection(). 

What is the correct and easy way to handle a large request?

What is the correct way to use setFetchSize?

Answer 1

Assuming you are using the Connector/J driver provided by MySQL, I believe the solution is found in this manual page (note the 1 Connection::createStatement() parameter):

If you work with ResultSets that have a large number of rows or large values ​​and cannot allocate a lot of space in your JVM for memory required, you can tell the driver to pass the results back one row at a time.

To enable this functionality, create an instance of Statement as follows:

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

Answer 2

maybe does a lazy search, pulls, for example, only an identifier or something when you want to use / display your data, transfer the request to only one ID?

or maybe instead run it in a thread so that it just disconnects and does it in the background