Java SQL .close() necesary if keep re-instantiating object?

user_8754958 0
Viewed 55

I know that if you create an SQL PreparedStatement or ResultSet, you should use .close() on the resource when you're finished with it. If however I'm looping and just recreating the PreparedStatement with each iteration, do I still have to call .close() on it during each iteration, or just when I'm finished?

For example, if i have this:

private Connection con;
private String[] queries;
private PreparedStatement stmt;

...
create connection
fill array queries with 10 queries in it
...

for (int i = 0; i < 10; i++) {

    stmt = con.prepareStatement(queries[i], Statement.RETURN_GENERATED_KEYS);
    stmt.executeUpdate();
    
    // necessary?
    stmt.close()
}

Is it necessary to .close() stmt with each iteration, or should I just do it when loop is complete? Why or why not? Thank you.

prepared-statement jdbc java
2 Answers

You’re creating a new object with each iteration. Each statement you leave open can keep resources open on the database server for longer than necessary. Closing the connection should clean up everything, but JDBC drivers have been known to have bugs where they failed to clean up.

Close them all in a finally block, or use try with resources.

user_2173240

Object lives on after going out of scope

When you reassign the reference variable to another object, the object previously referenced still lives on in memory. Their close method is not called.

If not otherwise referenced elsewhere, the orphaned object will become a candidate for garbage collection. But garbage collection does not call the object’s close method.

try-with-resources

You should learn to use try-with-resources syntax to automatically close resources such as JDBC objects, file I/O objects, etc. See tutorial by Oracle. Close such resources as soon as possible, as soon as you are done with them.

Example code

Your code should look something like following example code. Notice how each JDBC object is closed immediately after we finish using it: Connection, PreparedStatement, and ResultSet.

A DataSource object does not get closed as it is not actually “open”. A DataSource object merely holds information needed to connect to database (server address, user name, password, etc.).

String[] queries = new String[ 10 ];
// … populate queries array.

DataSource dataSource = this.fetchDataSource();
try (
        Connection conn = dataSource.getConnection() ;
)
{
    for ( int i = 0 ; i < 10 ; i++ )
    {
        try (
                PreparedStatement ps = conn.prepareStatement( queries[ i ] , Statement.RETURN_GENERATED_KEYS ) ;
        )
        {
            ps.setObject( 1 , LocalDate.of( 2021 , Month.JANUARY , 23 ) );
            int countOfRowsAffected = ps.executeUpdate();
            if ( countOfRowsAffected > 0 )
            {
                try ( ResultSet rs = ps.getGeneratedKeys() ; )
                {
                    while ( rs.next() )
                    {
                        // … process your keys.
                    }
                }
                // The `ResultSet` object is auto-closed by this point.
            }
        }
        // The `PreparedStatement` object is auto-closed by this point.
    }
} catch ( SQLFeatureNotSupportedException e )
{
    e.printStackTrace();
} catch ( SQLTimeoutException e )
{
    e.printStackTrace();
} catch ( SQLException e )
{
    e.printStackTrace();
}
// The `Connection` object is auto-closed by this point.

An extra benefit of using try-with-resources is that your intentions are quite clear. Anyone reading your code knows where you intend to dismiss each resource.

user_6427060
Related