Java, ResultSet.close (), PreparedStatement.close () - why?

Question

In my web application, I use the database extensively.

I have an abstract servlet from which all servlets that require a database connection are inherited. This abstract servlet creates a connection to the database, calls an abstract method that must be overridden by the inheriting servlets to execute its logic, and then closes the connection. I do not use pooling because my application will have a very limited number of users and operations.

My question is what is the worst that can happen if I never close ResultSet s, PreparedStatement and Statement , which my inheriting servlets create, if the Connection they create are always closed

Answer 1

The javadoc for Statement # close () says:

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

Thus, you do not need to worry about closing ResultSets if you always close applications in a timely manner.

javadoc for Connection # close () does not provide an appropriate guarantee, but it says:

Releases the database of connection objects and JDBC resources immediately, rather than waiting for them to exit automatically.

What you can reasonably interpret as implying that any statements will be closed. Looking at the open source jTDS driver and looking at the driver for a well-known and expensive commercial database, I see that they do just that.

Answer 2

I am sure that closing the connection will close related statements, ResultSets and other related objects. However, all this will consume resources both on the client and on the database server until the connection is closed.

If in your case you know that you will close the connection soon, you are probably not at risk, although I do not think this should be considered best practice.

However, this only applies in your current setup. If when your application changes, you may run into problems because you did not close your applications and ResultSets.

Although you do not want to use the connection pool, I think this is a bad idea even with a small number of users / operations, since opening a database connection is not cheap. Thus, even in your context, a connection pool can help you respond more quickly to your system.

Just a garbage collection note. Before closing the connection, unused statements or ResultSets may be GCed. But , when it comes to freeing system resources, such as files or, as a rule, non-java resources (for example, cursors on a database server), you should not rely on the JVM GC. For example, if your client application opens a lot of ResultSets but uses only a small fraction of the allocated heap memory, the GC will never fire while the database server is suffocating with open cursors.

Answer 3

AFAIK, you will run out of resources on the database server due to the binding of file descriptors, the resources needed to store the result set associated with this statement, etc. Intelligent drivers / databases can be implemented there that ensure that as soon as the connection is closed, all associated resources will be freed, but this is not part of the specification, so it may eventually come and bite you in the end. Any reason your overriding classes cannot close the result sets and statements they use?

Answer 4

Its a bit Api shit - ends up writing a load of boiler plate code.

I believe the best way is to wrap the classes and make the utilization of the connection dispose of different material (since you can keep track of what happens when you exit wrapped calls).

As long as you have an IDE that can generate delegation methods in a class, then this is a trivial task to port such things.

I didn’t understand that all the extra things needed to be disposed of, but I just noticed that someone is doing it here, but I’m lucky as we are already completing the base classes to turn all annoying exceptions into RuntimeExceptions and provide a higher level of SQL level.

I made a small class to track various bits of material:

 public class CleanupList { private final ArrayList<AutoCloseable> _disposables; public CleanupList() { _disposables = new ArrayList<>(); } public void cleanup() { for(AutoCloseable closeable : _disposables){ //it sucks that they put an exception on this interface //if anyone actually throws an exception in a close method then there something seriously wrong going on //they should have copied the c# more closely imo as it has nicer syntax aswell try { closeable.close(); } catch (Exception e) { throw new RuntimeException(e); } } _disposables.clear(); } public <T extends AutoCloseable> T track(T statement) { _disposables.add(statement); return statement; } } 

And then, for example, in Wrapped Connection (something that wraps the database connection):

 public class WrappedConnection implements AutoCloseable { private final CleanupList _cleanupList; private Connection _connection; public WrappedConnection(Connection connection) { _connection = connection; _cleanupList = new CleanupList(); } public void close() { try { _connection.close(); _cleanupList.cleanup(); } catch (SQLException e) { throw new RuntimeException(e); } } public PreparedStatement prepareStatement(String sql) { try { return trackForDisposal(_connection.prepareStatement(sql)); } catch (SQLException e) { throw new RuntimeException(e); } } private <T extends AutoCloseable> T trackForDisposal(T statement) { return _cleanupList.track(statement); } .... lots more methods } 

Then you can also pass the same list to certified versions of the PreparedStatement / Result sets (which I did not show here), etc. and use it in a similar way.

I don’t know what other people use, but in IDEA you can enable a warning for automatically closed material that is not used in the block (or I should say try-with-resources):

 try(SomethingThatNeedsClosing somethingThatNeedsClosing = new SomethingThatNeedsClosing()){ //do stuff } 

Using these blocks, you finally close auto-close and can only use things that have the AutoClosable interface type

I do not know why this warning is not included by default in IDEA, but there you go.

Answer 5

A database connection is not the only thing that connects your application. At stake are other resources.

They will be released at some point in time if you do not release them yourself, but if you can do it, you must.