JDBC: Does the connection break if i lose reference to the Connection object?

Question

Hi.

If i have the following method -

public static void C()  {
    Connection con = DriverManager.getConnection();

    .... // code

    return;
}

and i dont call con.close() , will the connection terminate automatically once the method returns?

Answer 1

It will not close immediately when the method returns. Eventually the Connection object will be garbage collected, and probably a finalizer will then close the connection. But there's no guarantee for when this will happen.

Don't write programs that rely on the connection being closed automatically somehow if you lose all references to the Connection object. Always close the connection from a finally block (so that it happens even in case of an exception):

Connection conn = DriverManager.getConnection(...);
try {
    // ... code that uses the connection
}
finally {
    // Close the connection
    conn.close();
}

Answer 2

...will the connection terminate automatically once the method returns?

No, it won't. It may or may not eventually close, but it's going to be a long time before it does, if ever. The connection class's finalizer probably closes the connection if it's open, but there are lots of situations where finalizers aren't ever run. It's essential to call con.close() explicitly.

Here's how I usually handle it (although I've factored a lot of this logic out into helpers, since this is verbose otherwise):

public static void C()
throws SQLException
{
    Connection con = DriverManager.getConnection();
    try {
        .... // code

        // done with the connection
        con.close();
        con = null;
    }
    finally {
        if (con != null) {
            try {
                con.close();
            }
            catch (Exception e) {
                // Eat it to avoid masking any exception that
                // got us here
            }
        }
    }
}

Note that having detected the unclosed connection in the finally clause, I close it but don't allow any exception doing so may cause to get thrown. This is because the main logic closes the connection correctly, which means that if I've found an open connection in the finally block, an exception has already been thrown and we're handling it, so I don't want to mask that by throwing a different exception from con.close().

With decent helpers, that gets a lot shorter and easier to write:

public static void C()
throws SQLException
{
    Connection con = DriverManager.getConnection();
    try {
        .... // code

        // done with the connection
        con = JDBCHelper.close(con);      // <== This one *allows* any exception that occurs
    }
    finally {
        con = JDBCHelper.quietClose(con); // <== This one *eats* any exception that occurs
    }
}

...where JDBCHelper (a hypothetical class) contains:

public static final Connection close(Connection con)
throws SQLException
{
    con.close();
    return null;
}

public static final Connection quietClose(Connection con)
{
    if (con != null) {
        try {
            con.close();
        }
        catch (Exception e) {
        }
    }
    return null;
}

Answer 3

http://java.sun.com/j2se/1.4.2/docs/api/java/sql/Connection.html#close%28%29

Note: A Connection object is automatically closed when it is garbage collected. Certain fatal errors also close a Connection object.

That said, you should probably close your connections explicitly.