jdbc: when can i close what

Question

currently i have jdbc code with the following basic stucture:

get Connection

(do the next 4 lines several times, never closing statement)
get statement
get result set
process result set
close result set

close connection

It occurred to me after writing this code that i need to close the statement.
1 what are the effects of not closing the statement.
2 will the following work, this si will closing the statement prevent me from processing the result set as normal?

get Connection

(do the next 5 lines several times)
get statement
get result set
close statement
process result set
close result set

close connection

Answer 1

The answer depends on your JDBC driver unfortunately. What you wrote there might work.

However, the general rule is that you close your statement only when you are done with the corresponding resultset.

EDIT: I realize that you had a second question where you asked about the effects of not closing the statements/Resultsets and so on. The effects also depend on your JDBC driver but, it could lead to significant resource leaks.

Answer 2

The close behavior is specified in the JDBC Specification. Closing the Connection releases all JDBC resources associated with the connection and will implicitly close all Statements, ResultSets, etc. Closing the statement will close the ResultSets.

Example 2 is not guaranteed to work. The statement should be closed AFTER the ResultSet has been used. The spec says that you should get an SQLException if you try to use that ResultSet (some JDBC drivers don't follow the spec strictly--and MS is not biggest offender).

If you forget to close a ResultSet or Statement, the worse case that happens is you consume database and JVM resources for longer than necessary. On a resource constrained or high load system, this could cause memory/connection/resource errors or reduced performance.

Answer 3

If you're using Oracle and forget to close the statements you'll get

ORA-01000: maximum open cursors exceeded

after a while.