How to Handle Exceptions in JDBC

Before we can understand the exceptions a segment of code might throw, we have to have a good understanding of what it does. So let's quickly review what happens in a standard JDBC operation.

The basic scheme of any JDBC operation is pretty straightforward:

  1. Get a connection.
  2. Create a statement.
  3. Execute the statement and get a ResultSet.
  4. Process the ResultSet.
  5. Close everything: the ResultSet, the Statement, and the Connection.

The code will look something like this:

public void handleJDBC() {
    DataSource dataSource = getDataSource();
    Connection connection = dataSource.getConnection();
    String query = "SELECT * FROM book";
    PreparedStatement preparedStatement 
            = connection.prepareStatement(query);
    ResultSet resultSet = preparedStatement.executeQuery();

    //process resultSet

    resultSet.close();
    preparedStatement.close();
    connection.close();
}

This code doesn't compile, and I bet you know why - we haven't included any exception handling, and pretty much every line here can throw an exception.

In Java you have to either declare or handle exceptions. But handling of exceptions in JDBC is surprisingly difficult.

Today we'll show you how to handle exceptions related to the Connection object. The other exceptions can be handled in a similar way.

We will abbreviate this code

String query = "SELECT * FROM book";
PreparedStatement preparedStatement 
      = connection.prepareStatement(query);
ResultSet resultSet = preparedStatement.executeQuery();

//process resultSet

resultSet.close();
preparedStatement.close();

as

operation(connection)

An inexperienced programmer might try something like this:

Bad Solution 1: Just add the "throws" declaration

public void handleJDBC() throws SQLException {
    operation(connection)
}

Or this:

Bad Solution 1A: Catch everything

public void handleJDBC()  {
    try {
        DataSource dataSource = getDataSource();
        Connection connection = dataSource.getConnection();
        operation(connection)
        connection.close();
    } catch (SQLException e) {
        //do something here
    }
}

The inability of many novice programmers to write something sensible in the catch block (the do something here) is a topic for another time.

So this code compiles, but it doesn't work. We could have resource leaks because, if an exception is thrown during the operation part of the code, the close statements will never be executed. The connection, and probably also the statement and resultSet, will stay open.

Rule 1: Remember to close your resources.

Bad Solution 2: Add "finally" clause

Here, the connection is closed in the finally clause.

public void handleJDBC() throws SQLException {
    Connection connection = null;
    try {
        connection = dataSource.getConnection();
        operation(connection);
    } finally {
        connection.close();
    } 
}

There are a couple of problems here. First, we had to move the connection variable out of the try-catch block, and we had to give it an initial value.

Second, this code won't actually handle every type of exception. If an exception is thrown in getConnection(), then we'll get a NullPointerException in the finally clause. This is very tricky to debug because the NullPointerException supresses the real cause of the problem. Just a month ago I saw a beginner programmer staring into his code trying to figure out why a NullPointerException had anything to do with the wrong connection configuration. Don't let that happen to you.

Bad Solution 3: Check for null value

If the null value causes a problem, we'll just check for it, right?

public void handleJDBC() throws SQLException {
    Connection connection = null;
    try {
        connection = dataSource.getConnection();
    operation(connection);
    } finally {
        if (connection != null) {
            connection.close();
        }
    } 
}

But connection.close() can also throw an exception. We have the same problem as before: the connection.close() exception supresses the real exception. So we have to catch and handle a connection.close() exception.

Solution?

We need something like this:

public void handleJDBC() throws SQLException, DoubleException {
    Connection connection = null;
    Exception exception = null;
    try {
        connection = dataSource.getConnection();
        operation(connection);
    } catch (SQLException e) {
        exception = e;
    } finally {
        if (connection != null) {
            try {
            connection.close();
        } catch (SQLException e) {
            if (exception != null) {
                exception = new DoubleException(exception, e);
            } else {
                exception = e;
            }
        }
        }
    }
    if (exception != null) {
        throw exception;
    }
}

Disgusting. But it works.

Solution 2: Java 7

Java 7 has a try-with-resources clause.

public void handleJDBC() throws SQLException {
    try (Connection connection = dataSource.getConnection()) {
        operation(connection);
    } 
}

Much better. Unfortunately, Java 7 won't work for those who have to be pre-Java-7 compatible. And anyway, it's better to know how to handle exceptions correctly.