CODEWITHSUNDEEP
javasql
udachny
udachny

Reputation: 415

Handling all exceptions when executing SQL in Java

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

  1. Create connection
  2. Create statement
  3. Execute statement, create resultset
  4. Close resultset
  5. Close statement
  6. 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?

Upvotes: 2

Views: 9866

Answers (6)

Brian Agnew
Brian Agnew

Reputation: 272417

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.

Upvotes: 4

mtk
mtk

Reputation: 13717

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();
}

Upvotes: 0

Qwerky
Qwerky

Reputation: 18455

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.

Upvotes: 0

xagyg
xagyg

Reputation: 9721

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*/}
}

Upvotes: 0

Thorn G
Thorn G

Reputation: 12776

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.

Upvotes: 8

duffymo
duffymo

Reputation: 309008

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.

Upvotes: 0

Related Questions