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