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?

Solution 1

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.

Solution 2

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

Solution 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.

Comments

[Devanshu Mevada]

The OP is using Firebird, why would he use Oracle's driver? Then, I wouldn't implement connection validation logic in my code, I'd rather use a pool that implements this check (and there is no need to use a JDBC 4.0 driver for this).

[Devanshu Mevada]

If you loose the connection while running a query, you'll probably loose it again during next attempt. Fix the real problem if this happen.

[Gandalf]

Assuming Firebird (which I admit I know zero about) has a JDBC driver then he can still use these classes. Notice they are called UNIVERSAL Connection Pool - it's Oracle's version of C3P0 or Proxool. They work with any JDBC driver. And yes, this pool will do the validation logic on it's own.

[Devanshu Mevada]

I agree about the "Universal" Connection Pool. However, "JBDC 4.0 compliant" refers to a JDBC driver (Oracle specific this time), not a pool. That was my point.

[Gandalf]

But UCP uses features of JDBC 4.0 (specifically the new isValid() call in the java.sql.PooledConnection class) to do some of it's validation. I don't think all of the pool's features will work if the driver is not 4.0 compliant. But yes you could always do the checks yourself, or it may offer another validation mechanism for other drivers.

[Devanshu Mevada]

Can you provide a reference for this? To my knowledge, Jaybird is a JDBC 2.0 driver so it would be interesting to know what would work or not when using Oracle's UCP. BTW, I noticed that UCP provides oracle.ucp.jdbc.ValidConnection but, TBH, I don't think that using vendor specific classes is a good idea and wouldn't recommend it.

[Gandalf]

A lot of the docs are inside the Oracle Developers site, which requires a login - but this article does a pretty good job. oracle.com/technology/pub/articles/vasiliev-oracle-jdbc.html