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