Impact of java.sql.Connection.close() on java.sql.Statement objects and the like

Question

Does closing a java.sql.Connection also close all the statements, prepared statements, etc. obtained from that connection? Or is there going to be memory leak if I close the connection but leave the statements, etc. unclosed?

Answer 1

Does closing a java.sql.Connection also close all the statements, prepared statements, etc. obtained from that connection? Or is there going to be memory leak if I close the connection but leave the statements, etc. unclosed?

You should not depend on it.

The spec reads as follows:

An application calls the method Statement.close to indicate that it has finished processing a statement. All Statement objects will be closed when the connection that created them is closed. However, it is good coding practice for applications to close statements as soon as they have finished processing them. This allows any external resources that the statement is using to be released immediately.

The best practice is to close ALL ResultSets, Statements, and Connections in a finally block, each enclosed in their own try/catch, in reverse order of acquisition.

Write a class like this:

public class DatabaseUtils
{
    public static void close(Statement s)
    {
        try
        {
            if (s != null)
            {
                s.close();
            }
        }
        catch (SQLException e)
        {
            // log or report in someway
            e.printStackTrace();
        }
    }

    // similar for ResultSet and Connection
}

Call like this:

Statement s;
try
{
    // JDBC stuff here
}
finally
{
    DatabaseUtils.close(s);
}