Handling all exceptions when executing SQL in Java

Question

There are many steps involved in executing one SQL statement in Java:

Create connection
Create statement
Execute statement, create resultset
Close resultset
Close statement
Close connection

At each of these steps SQLException can be thrown. If we to handle all exception and release all the resources correctly, the code will will look like this with 4 levels of TRY stacked on the top of each other.

try {
     Connection connection = dataSource.getConnection();
     try {
           PreparedStatement statement = connection.prepareStatement("SELECT 1 FROM myTable");
           try {
                ResultSet result = statement.executeQuery();
                try {
                     if (result.next()) {
                           Integer theOne = result.getInt(1);
                     }
                }
                finally {
                     result.close();
                }
           }
           finally {
                statement.close();
           }
     }
     finally {
           connection.close();
     }
}
catch (SQLException e) {
// Handle exception
}

Can you propose a better (shorter) way to execute a statement while still release all the consumed resources?

Wrap the entire thing in a try catch? Unless you want to know where specifically it failed. — Max
– Max, Commented Dec 5, 2012 at 14:38
If you wrap the whole block into try/catch and an exception gets thrown - you won't know which resources were consumed and need to be closed. — udachny
– udachny, Commented Dec 5, 2012 at 14:41

djechlin · Accepted Answer · 2012-12-05 14:44:30Z

8

If you are using Java 7, the try with resources statement will shorten this quite a bit, and make it more maintainable:

try (Connection conn = ds.getConnection(); PreparedStatement ps = conn.prepareStatement(queryString); ResultSet rs = ps.execute()) {

} catch (SQLException e) {
    //Log the error somehow
}

Note that closing the connection closes all associated Statements and ResultSets.

edited Dec 5, 2012 at 14:44

djechlin

61.1k40 gold badges173 silver badges300 bronze badges

answered Dec 5, 2012 at 14:40

Thorn G

12.8k2 gold badges48 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

tibtof Over a year ago

If you are using a connection pool, closing the connection may not actually close the Statement and the ResultSet.

Thorn G Over a year ago

Interesting -- I did not know that. Thanks for the tip.

Brian Agnew · Accepted Answer · 2012-12-05 14:49:44Z

4

Check out Apache Commons DbUtils, and in particular the closeQuietly() method. It will handle the connection/statement/result set closing correctly, including the cases where one or more are null.

An alternative is Spring JdbcTemplate, which abstracts a lot of work away from you, and you handle your database queries in a much more functional fashion. You simply provide a class as a callback to be called on for every row of a ResultSet. It'll handle iteration, exception handling and the correct closing of resources.

edited Dec 5, 2012 at 14:49

answered Dec 5, 2012 at 14:40

Brian Agnew

273k38 gold badges342 silver badges443 bronze badges

1 Comment

Thorn G Over a year ago

JdbcTemplate is rather nice, +1

duffymo · Accepted Answer · 2012-12-05 14:39:43Z

I create a utility class with static methods I can call:

package persistence;

// add imports.

public final class DatabaseUtils {

    // similar for the others Connection and Statement
    public static void close(ResultSet rs) {
        try {
            if (rs != null) {
                rs.close();
            }
        } catch (Exception e) {
            LOGGER.error("Failed to close ResultSet", e);
        }
    }
}

So your code would be:

     Integer theOne = null;
     Connection connection = null;
     PreparedStatement statment = null;
     ResultSet result = null;
     try {
         connection = dataSource.getConnection();
         statement = connection.prepareStatement("SELECT 1 FROM myTable");
         result = statement.executeQuery();
         while (result.next()) {
             theOne = result.getInt(1);
         }
    } catch (SQLException e) {
        // do something
    } finally {
        DatabaseUtils.close(result);
        DatabaseUtils.close(statement);
        DatabaseUtils.close(connection);
    }
    return theOne;

I'd recommend instantiating the Connection outside this method and passing it in. You can handle transactions better that way.

xagyg · Accepted Answer · 2012-12-05 14:48:34Z

0

Connection connection = null;
PreparedStatement statement = null;
ResultSet result = null;
try {
     connection = dataSource.getConnection(); 
     statement = connection.prepareStatement("SELECT 1 FROM myTable");
     result = statement.executeQuery();
     if (result.next()) {
         Integer theOne = result.getInt(1);
     }
}
catch (SQLException e) { /* log error */ }
finally {           
     if (result != null) try { result.close(); } catch (Exception e) {/*log error or ignore*/}
     if (statement != null) try { statement.close(); } catch (Exception e) {/*log error or ignore*/}
     if (connection != null) try { connection.close(); } catch (Exception e) {/*log error or ignore*/}
}

answered Dec 5, 2012 at 14:48

xagyg

9,7712 gold badges34 silver badges31 bronze badges

Comments

Qwerky · Accepted Answer · 2012-12-05 14:48:48Z

0

Just close the Connection, this releases all resources*. You don't need to close Statement and ResultSet.

*just make sure you don't have any active transactions.

edited Dec 5, 2012 at 14:48

answered Dec 5, 2012 at 14:43

Qwerky

18.5k6 gold badges48 silver badges84 bronze badges

Comments

mtk · Accepted Answer · 2012-12-05 14:48:58Z

0

Your code can be shortened and written in this way...

Connection connection = dataSource.getConnection();
PreparedStatement statement = null;
ResultSet result = null;
try {
    statement= connection.prepareStatement("SELECT 1 FROM myTable");
    result = statement.executeQuery();
    if (result.next()) {
        Integer theOne = result.getInt(1);
    }
} catch (SQLException e) {
    // Handle exception

} finally {
    if(result != null) result.close();
    if(statement != null) statement.close();
    if(connection != null) connection.close();
}

answered Dec 5, 2012 at 14:48

mtk

13.8k16 gold badges75 silver badges117 bronze badges

2 Comments

udachny Over a year ago

This solution has a little issue. connection.close may also throw SQLException.

Brian Agnew Over a year ago

Good comment. This illustrate nicely that a simple resource mgmt issue is anything but

Collectives™ on Stack Overflow

Handling all exceptions when executing SQL in Java

6 Answers 6

2 Comments

1 Comment

Comments

Comments

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

1 Comment

Comments

Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related