Java, ResultSet.close(), PreparedStatement.close() -- what for?

Question

In my web-application, i make extensive use of a database.

I have an abstract servlet, from which all the servlets that need a database connection, inherit. That abstract servlet creates a database connection, calls the abstract method which must be overriden by the inheriting servlets to do their logic, and then closes the connection. I do not use connection pooling, because my application will have a very limited number of users and operations.

My question is, what's the worst that can happen if i don't ever close the ResultSets, PreparedStatements and Statements that my inheriting servlets create, if the Connections that create them are always closed?

Answer 1

The javadoc for Statement#close() says:

Note:When a Statement object is closed, its current ResultSet object, if one exists, is also closed.

So you don't need to worry about closing ResultSets, as long as you always close Statements in a timely manner.

The javadoc for Connection#close() does not make a corresponding guarantee, but it does say:

Releases this Connection object's database and JDBC resources immediately instead of waiting for them to be automatically released.

Which you might reasonably construe as implying that any statements will be closed. Looking at the open-source jTDS driver, and peeking into the driver for a well-known and expensive commercial database, i can see that they do exactly that.

Answer 2

I'm pretty sure closing the connection will close the associated Statements, ResultSets and other associated objects. However all this will consume resources on both the client and may be on the database server until the connection is closed.

If in your case you know that you'll close the connection really soon you probably don't risk much although I don't think this should be considered as a best practice.

However this is only valid in your current setting. If When your application will change you can face issues because you didn't close your Statements and ResultSets.

Although you don't want to use connection pooling I think it is a bad idea even with few users/operations as opening a database connection is not cheap. So even in your context connection pool may help to get your system more responsive.

Just a note on garbage collection. Before closing the connection, the unused Statements or ResultSets may be GCed. But when it comes to freeing system resources such as files or more generally non-java resources (such as the cursors on a database server) the JVM GC should not be relied upon. For instance if your client application opens a lot of ResultSets but only use a small fraction of the allocated heap memory, the GC will never kicks in while the database server is suffocated by the opened cursors.