2

I have a background thread that is querying an Oracle database via a Select statement. The statement is populating a ResultSet Java object. If the query returns a lot of rows, the ResultSet object might get very large. If it's too large, I want to both cancel the background thread, but more importantly I want to cancel the thread that is creating the ResultSet object and eating up a lot of Java memory.

From what I have read so far online, java.sql.Statement cancel(), seems to be the best way to get this done, can anyone confirm this? is there a better way?

java.sql.Statement close() also works, I could probably catch the ExhaustedResultset exception, but maybe that's not safe.

To clarify, I do not want the ResultSet or the thread - I want to discard both completely from memory.

Alexander Mills
  • 90,741
  • 139
  • 482
  • 817

3 Answers3

1

This depends on the JDBC implementation: Statement.cancel() is a request to the JDBC driver class, that may or may not do what you need or expect.

However, seeing as you are performing a select (normally non-transactional) and seeing as the default row Prefetch property for JDBC is 10, this should probably do the trick. See this answer for similar/related information:

When I call PreparedStatement.cancel() in a JDBC application, does it actually kill it in an Oracle database?

Community
  • 1
  • 1
davek
  • 22,499
  • 9
  • 75
  • 95
1

Canceling the thread doesn't solve your problem, if you really need the query results.

If you are concerned about using up too much memory you can set the fetch size on the resultSet, which will limit the number of rows you get back at a time. Then you would have to consume the resultSet as you go (if the data piles up in the data structure you're copying the resultSet rows into then you're back to eating up memory).

Nathan Hughes
  • 94,330
  • 19
  • 181
  • 276
1

Oracle has a great document on memory management depending on your driver version.

Philippe Marschall
  • 4,452
  • 1
  • 34
  • 52