jbdc 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 they 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

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.

Answer 2

Let a connection pool handle this for you, many of them can validate a connection. So does DBPC 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

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