Connection Retry Logic Examples
Reference code examples for reconnection logic.
See Step 4: Ensure Application Implements Reconnection Logic for more information.
Simple Retry (SANITY CHECK)
Connection jdbcConnection = getConnection();
int iterationCount = 0;
int maxIterations = 10;
for (int i = 0; i < maxIterations, i++)) {
try {
// apply the raise (DML + commit):
giveRaiseToAllEmployees(jdbcConnection, i * 5);
// no exception, the procedure completed:
iterationCount++;
Thread.sleep(1000);
} catch (SQLRecoverableException recoverableException) {
// Get a new connection only if the error was recoverable.
System.out.println("SQLRecoverableException on iteration " + iterationCount )
System.out.println("DB Connection lost - will attempt to get a new connection to continue with the other iterations")
// IF its OK to lose this work and move onto the next
// iteration you could now try to get a new connection
// This depends on what the code is doing; in many use
// cases you must stop working, in others you can proceed
// after logging a message to a log file
// In our example, we assume we can proceed with the rest
// of the loop if possible.
// Using Transaction Guard, we can know if the work
// committed and move on safely (covered in another example).
try {
jdbcConnection.close(); // close old connection:
System.out.println("Connection closed - getting a new one")
jdbcConnection = getConnection(); // reconnect to continue with other iterations
} catch (Exception ex) {
System.out.println("Unable to close or get a new connection - giving up")
throw ex;
}
} catch (SQLException nonRecoverableException) {
// This is not a recoverable exception, so give up
System.out.println("SQL UN-recoverable exception...give up the rest of the iterations")
throw nonRecoverableException;
}
}
Connection Retry Logic with Transaction Guard
Connection jdbcConnection = getConnection();
boolean isJobDone = false;
while (!isJobDone) {
try {
// apply the raise (DML + commit):
giveRaiseToAllEmployees(jdbcConnection, 5);
// no exception, the procedure completed:
isJobDone = true;
} catch (SQLRecoverableException recoverableException) {
// Retry only if the error was recoverable.
try {
jdbcConnection.close(); // close old connection:
} catch (Exception ex) {} // pass through other exceptions
Connection newJDBCConnection = getConnection(); // reconnect to allow retry
// Use Transacton Guard to force last request: committed or uncommitted
LogicalTransactionId ltxid
= ((OracleConnection) jdbcConnection).getLogicalTransactionId();
isJobDone = getTransactionOutcome(newJDBCConnection, ltxid);
jdbcConnection = newJDBCConnection;
}
}
void giveRaiseToAllEmployees(Connection conn, int percentage) throws SQLException {
Statement stmt = null;
try {
stmt = conn.createStatement();
stmt.executeUpdate("UPDATE emp SET sal=sal+(sal*" + percentage + "/100)");
} catch (SQLException sqle) {
throw sqle;
} finally {
if (stmt != null)
stmt.close();
}
// At the end of the request we commit our changes:
conn.commit(); // commit can succeed but the commit outcome is lost
}
/**
* GET_LTXID_OUTCOME_WRAPPER wraps DBMS_APP_CONT.GET_LTXID_OUTCOME
*/
private static final String GET_LTXID_OUTCOME_WRAPPER =
"DECLARE PROCEDURE GET_LTXID_OUTCOME_WRAPPER(" +
" ltxid IN RAW," +
" is_committed OUT NUMBER ) " +
"IS " +
" call_completed BOOLEAN; " +
" committed BOOLEAN; " +
"BEGIN " +
" DBMS_APP_CONT.GET_LTXID_OUTCOME(ltxid, committed, call_completed); " +
" if committed then is_committed := 1; else is_committed := 0; end if; " +
"END; " +
"BEGIN GET_LTXID_OUTCOME_WRAPPER(?,?); END;";
/**
* getTransactionOutcome returns true if the LTXID committed or false otherwise.
* note that this particular version is not considering user call completion
*/
boolean getTransactionOutcome(Connection conn, LogicalTransactionId ltxid)
throws SQLException {
boolean committed = false;
CallableStatement cstmt = null;
try {
cstmt = conn.prepareCall(GET_LTXID_OUTCOME_WRAPPER);
cstmt.setObject(1, ltxid); // use this starting in 12.1.0.2
cstmt.registerOutParameter(2, OracleTypes.BIT);
cstmt.execute();
committed = cstmt.getBoolean(2);
} catch (SQLException sqlexc) {
throw sqlexc;
} finally {
if (cstmt != null)
cstmt.close();
}
return committed;
}