138Better SQLExceptions in Java 6

Author: Dr. Heinz M. KabutzDate: 2007-01-31Java Version: 6Category: Exceptions

Abstract: Java 6 has support for JDBC 4, which, amongst other things, gives you better feedback of what went wrong with your database query. In this newsletter we demonstrate how this can be used.

Welcome to the 138th edition of The Java(tm) Specialists' Newsletter, where we will look at new features of JDBC 4 that will help you to understand what went wrong with your database queries. I remember cornering Mark Hapner in 1999 when he visited Cape Town and presenting this problem to him. Mark Hapner was the architect for the first JDBC version and went on to architect J2EE and EJB. At the time, there was no easy solution to this problem.

javaspecialists.teachable.com: Please visit our new self-study course catalog to see how you can upskill your Java knowledge.

Better SQLExceptions in Java 6

Many years ago, I mentioned in a newsletter that when a SQLException occurs, we do not necessarily know what went wrong. It could be a temporary failure or a permanent fault. The SQL could contain a syntax error (permanent fault) or the database could be rebooting (temporary fault). I promised to write up a newsletter on how to solve this, but never got round to it.

Previously, these faults could be determined by looking at the SQLState contained in the exception. However, I thought that the JDBC driver should do this analysis and give me more specific information about what went wrong.

In JDBC 4.0, which ships as part of JDK 6, we now have a solution that will make it easier to write robust code for communicating with the database.

Instead of just having a single SQLException telling us that there is "a problem", we have three new subclasses, namely SQLNonTransientException, SQLTransientException and SQLRecoverableException. These sub-exceptions are called "categorised exceptions". It can now be possible, if the driver supports JDBC 4, to decide whether we should retry immediately, retry later or give up altogether.

The hierarchy of SQLException now looks like this (I left out some subclasses that are not relevant to our discussion):

  SQLException
    +---> SQLNonTransientException
    |    +---> SQLDataException
    |    +---> SQLFeatureNotSupportedException
    |    +---> SQLIntegrityConstraintViolationException
    |    +---> SQLInvalidAuthorizationException
    |    +---> SQLNonTransientConnectionException
    |    +---> SQLSyntaxErrorException
    +---> SQLTransientException
    |    +---> SQLTimeoutException
    |    +---> SQLTransactionRollbackException
    |    +---> SQLTransientConnectionException
    +---> SQLRecoverableException

The non-transient exceptions represent permanent failures that are the result of some condition that must be corrected before retrying. Simply retrying the query would almost certainly cause it to fail again. Examples are problems with the data, constraint violations (such as with foreign keys) and syntax errors in the actual SQL query.

The transient exceptions are thrown when an error condition might go away within a short time, without changing any application logic. A typical exception here is the transient SQL connection. However, I have found in my example that I had to start a new connection if we got this exception. With a transient exception, this should typically not be necessary. A good example of a transient exception is when a deadlock occurs in the database, which will cause a SQLTransactionRollbackException.

The recoverable exception means that it might succeed if the application does some recovery steps and then retries the transaction. This will require to at least close the connection and to open a new one.

Apache Derby

Another "feature" with the JDK 6 (not JRE 6) is that it ships standard with the Derby embedded database. I have still not met anyone who thinks this is a good idea. However, it is nice to be able to immediately start testing these new exceptions without having to find the correct drivers for your database.

However, before I show you the test code, there was another annoying problem with database connections in the past. You did not know whether they were still active, unless you sent down a "known good query". This could be something as simple as "SELECT 1". However, if you added a caching JDBC driver, it could happen that the answer would come back immediately for something as simple as that.

Since Java 6, we now have a standard mechanism for checking connectivity to the database. We can call the isValid(int timeout) method on a connection. This sends through a known good query and if it does not get a response within the given timeout (in seconds), then we know that the database connection is not valid anymore.

To run the DbTest below, all you need to do is make sure that the derby.jar file is in your classpath. This file is now in your JDK6/db/lib directory. Note that you do not need to do Class.forName() to load the driver - another feature of JDBC 4. How that works, is left as an exercise to the reader :-)

import java.sql.*;

public class DbTest {
  private static final String dburl = "jdbc:derby:tjsnTest";

  public static void main(String[] args) throws SQLException {
    Connection con = getNewConnection();
    Statement s = con.createStatement();

    try {
      s.execute("hello world - this should not work");
    } catch (SQLSyntaxErrorException ex) {
      System.out.println("Permanent problem with syntax");
    }

    s.execute("create table testTable(id int, name varchar(10))");
    try {
      s.execute("insert into testTable values (1, 'Heinz Kabutz')");
    } catch (SQLDataException ex) {
      System.out.println("Permanent problem with the data input");
    }

    System.out.println("Is connection valid? " + con.isValid(10));

    shutdownDB();

    System.out.println("Is connection valid? " + con.isValid(10));

    try {
      s.execute("drop table testTable");
    } catch (SQLTransientConnectionException ex) {
      System.out.println("Temporary problem connecting to db");
    }

    // restarting the database
    con = getNewConnection();
    s = con.createStatement();
    try {
      s.execute("drop table testTable");
    } catch (SQLTransientConnectionException ex) {
      System.out.println("Temporary problem connecting to db");
    }

    try {
      s.executeQuery("SELECT id, name FROM testTable");
    } catch (SQLSyntaxErrorException ex) {
      System.out.println("Permanent syntax problem with query");
    }
  }

  // shutting down the database
  private static void shutdownDB() throws SQLException {
    try {
      DriverManager.getConnection(dburl + ";shutdown=true");
    } catch (SQLTransientConnectionException ex) {
      // this should not happen - but it does ...
      System.out.println("Temporary problem connecting to db");
    }
  }

  private static Connection getNewConnection() throws SQLException {
    return DriverManager.getConnection(dburl + ";create=true");
  }
}

You should be able to run the program quite easily, like this:

    java -cp %JDK_HOME%/db/lib/derby.jar;. DbTest

    Permanent problem with the data input
    Is connection valid? true
    Temporary problem connecting to db
    Is connection valid? false
    Temporary problem connecting to db
    Permanent syntax problem with query

This is all very nice, but something like this should have been available in Java 1.0. To now go back and fix all the legacy code is just not practical. New code is usually done with the Java Persistence API or with the Spring Framework (which already has support for more sophisticated SQL Exceptions), not direct JDBC calls. Still, I am pleased to see this finally being added to JDBC.

Kind regards from the Island of Crete

Heinz

Comments

We are always happy to receive comments from our readers. Feel free to send me a comment via email or discuss the newsletter in our JavaSpecialists Slack Channel (Get an invite here)

Related Articles

Browse the Newsletter Archive