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

AFAIK, you'll end up exhausting resources on your database server due to tied up file handles, resources needed for holding the result set associated with a given statement etc. There might be smart driver/database implementations out there which make sure that as soon as the connection is closed all the related resources are freed up but that isn't part of the specification so might eventually come and bite you in the long run. Any reason why your overriding classes can't close the result sets and statements they use?

Answer 3

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.

Answer 4

A connection to the database isn't the only thing your application is tying up. There are other resources at stake.

They will get freed up at some point in time if you don't release them yourself, but if can do that, you should.

Answer 5

Its a bit of crap Api - ends up with you writing a load of boiler plate code.

I reckon the best way to go is wrap the classes and make it so that disposing the connection disposes the other stuff (as you can track what gets made on the way out of the wrapped calls).

As long as you've got an IDE that can generate you delegating methods in a class then it's a trivial job to wrap stuff like this.

I didn't realise all the extra stuff needed disposing but just spotted someone doing it here, however I'm in luck as we are already wrapping the basic classes to turn all the annoying exceptions into RuntimeExceptions and to provide some more high level sql operations.

I made a little class for tracking the different bits of stuff:

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's 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 (which is something that wraps a 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
}

You can then also pass the same list into wrapped versions of PreparedStatement/Result sets (which I've not shown here) etc and use it in a similar way.

I don't know what other people are using but in IDEA you can switch on a warning for auto-closable stuff that isn't in a using (or should I say try-with-resources) block:

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

These using blocks do you a try finally close automatically and can only be used with things of the AutoClosable interface type

I dont know why this warning isn't switched on by default in IDEA but there you go.