What is the reason for OutOfMemoryError: Java heap heap in the following case?

Question

The following code example is inside a for loop, which runs about 2 million times.

List<String> parameters = new LinkedList<String>(); stmt2 = null; rs2= null; //This is line 472 stmt2 = con.prepareStatement("select NAME from TABLE_NAME where FIELD="+ strId); rs2 = stmt2.executeQuery(); while (rs2.next()) { parameters.add(rs2.getString("NAME")); } 

stack trace:

 Exception in thread "main" java.lang.OutOfMemoryError: Java heap space at com.mysql.jdbc.PreparedStatement.<init>(PreparedStatement.java:437) at com.mysql.jdbc.Connection.clientPrepareStatement(Connection.java:2185) at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4782) at com.mysql.jdbc.Connection.prepareStatement(Connection.java:4687) at consistencyCheck.ConsistencyCheck.parameterCheck(ConsistencyCheck.java:472) at consistencyCheck.ConsistencyCheck.performConsistencyCheck(ConsistencyCheck.java:316) at consistencyCheck.ConsistencyCheck.main(ConsistencyCheck.java:198) 

Please let me know if further information is required.

Thanks.

Thanks everyone for the answers. I agree with BalusC's answer as he answered first. Unfortunately, I cannot support any other answers due to insufficient reputation :(

Only one comment for anyone who suggested increasing the heap of memory. Increasing the memory heap is something you never need to do if you are 100% sure that this is the only solution to your problem. For example, in my problem, increasing the heap may "solve" the problem, but the main error still remains.

Answer 1

Based on the comments, you create a Statement and ResultSet inside the loop, but do not close them. You need to also close them in a loop. This will free up internal resources.

In addition, you do not use the database cache of a prepared statement. Right now, you are binding a string to a parameter in an SQL string that causes the creation of 2M String objects instead of 1 String object. Better prepare the instructions before the cycle.

 try { // ... statement = connection.prepareStatement("select NAME from TABLE_NAME where FIELD=?"); for ( /* 2M times? */ ) { statement.setInt(1, id); try { resultSet = statement.executeQuery(); // ... } finally { if (resultSet != null) try { resultSet.close(); } catch (SQLException ignore) {} } } } finally { if (statement != null) try { statement.close(); } catch (SQLException ignore) {} } 

Alternatively, you can also use the IN clause instead. For instance.

 WHERE field IN (1,2,3,4,5); 

This, however, is more complicated with placeholders. See Also: What is the best approach using JDBC to parameterize an IN clause?

Or as a completely different alternative, if necessary, with the help of a more experienced ninja DB / SQL administrator, rewrite the whole thing to get exactly the results you need, with just one SQL query. Ask, if necessary, a separate question about what's on sql here on SO.

Answer 2

After you finish the while loop, rs2.close() and stmt2.close() will help you.

Answer 3

I want to clarify some things regarding JDBC resources using.

• Best practice for all resources is to close them at the end of the block. You have already given examples with ResultSet and PreparedStatement .

• If your application does not use the connection pool and you call connecion.close (), it should actually close the ResultSet and PreparedStatement objects. Connection implementations, as a rule, keep all these objects inside, and even if you create them in a loop, they will be closed.

• If the connection pool is used, then when you call connection.close (), you do not close the physical connection, you just release it back to the pool. As a result, ResultSet and PreparedStatement objects are not closed. I highly recommend that you read the following article about this Memory Leak in Enterprise Java Applications

• In Java7, both Statement and ResultSet extend AutoCloseable, so you can also not worry about closing them. (Again, if you use connection pools, I don't think Java 7 will be able to close them automatically)

Answer 4

There is another solution if you do not want to increase the heap size of the JVM.

Firstly, your version of MySQL must be above 5.0.

Secondly, Statement.getResultSetType () should be TYPE_FORWARD_ONLY, and ResultSetConcurrency should be CONCUR_READ_ONLY (by default).

Third, include ONE of these lines: 1) .statement.setFetchSize (Integer.MIN_VALUE); 2) ((com.mysql.jdbc.Statement) stat) .enableStreamingResults () ;.

Now you will receive rows of results one by one