JDBC connection with auto reconnect

Question

I am using JDBC to connect to a database server. The connection is over a wireless network and can be dodgy at times. At the moment when the connection is lost I need to close and restart the application.

Does anyone have some examples of code where I could write some sort of wrapper to automatically reconnect and rerun the last query? This would save a lot of hassles.

I am just not sure how it should/could be implemented. Maybe there is already something available?

Answer 1

Even if you use JDBC connection pool either application server provided or apache commons pooling, it is worthwhile to code a retry logic. Based on the configuration of your application server, the app server would purge all the pooled connections and recreate a fresh set of connections. Here is a sample:

Connection conn = null;
Statement stmt = null;
ResultSet rs = null;
//
// How many times do you want to retry the transaction
// (or at least _getting_ a connection)?
//
int retryCount = 5;
boolean transactionCompleted = false;
do {

  try {
    conn = getConnection(); // assume getting this from a
    // javax.sql.DataSource, or the
    // java.sql.DriverManager

    retryCount = 0;
    stmt = conn.createStatement();
    String query = "Some sample SQL";
    rs = stmt.executeQuery(query);
    while (rs.next()) {
    }
    rs.close();
    rs = null;
    stmt.close();
    stmt = null;

    conn.close();
    conn = null;
    transactionCompleted = true;
  } catch (SQLException sqlEx) {
    //
    // The two SQL states that are 'retry-able' 
    // for a communications error.
    //
    // Only retry if the error was due to a stale connection,
    // communications problem 
    //
    String sqlState = sqlEx.getSQLState();
    if ("Substitute with Your DB documented sqlstate number for stale connection".equals(sqlState) ) {
      retryCount--;
    } else {
      retryCount = 0;
    }
  } finally {
    if (rs != null) {
      try {
        rs.close();
      } catch (SQLException sqlEx) {
        // log this
      }
    }
    if (stmt != null) {
      try {
        stmt.close();
      } catch (SQLException sqlEx) {
        // log this
      }
    }
    if (conn != null) {
      try {
        //
        // If we got here, and conn is not null, the
        // transaction should be rolled back, as not
        // all work has been done
        try {
          conn.rollback();
        } finally {

          conn.close();
        }
      } catch (SQLException sqlEx) {
        //
        // If we got an exception here, something
        // pretty serious is going on, so we better
        // pass it up the stack, rather than just
        // logging it. . .
        throw sqlEx;
      }
    }
  }
} while (!transactionCompleted && (retryCount > 0));

Answer 2

Let a connection pool handle this for you, many of them can validate a connection. So does DBCP which has a testOnBorrow parameter that forces a sanity check on every connection before it's used. The default value of this parameter is true, it just needs validationQuery to be set to a non-null string to have any effect. So set the validationQuery and there you go! Check out the documentation.

Answer 3

Check out Oracle's Universal Connection Pool (UCP) libraries. They are fully JDBC 4.0 compliant and implement the isValid() call to check if a connection is live. It's easy to do this check, if false reconnect, then run your query.

Oracle UCP Download Page

While I know you didn't ask about connection pools, you should probably be using one anyway so this will help you twofold.