Direct JDBC calls, auto commit
This is for single statement transactions. A segment of sample code is shown in Example 12-1.
Example 12-1 StaleConnectionException with direct JDBC calls and auto commit
...
javax.sql.DataSource ds=null;
java.sql.Connection conn=null;
java.sql.Statement stmt1=null;
java.sql.ResultSet rs=null;
int maxRetries=5; // set maximum number of retries
int noRetries=0;
int bal=0;
boolean retry=false;
do { try { //already lookup datasource object ds, see above sectionconn=ds.getConnection();
conn.setAutoCommit(true);
stmt1=conn.createStatement();
stmt1.executeUpdate(
"UPDATE SAVINGS set balance=500 where accountID=10001");
} catch (com.ibm.websphere.ce.cm.StaleConnectionException staleex) { if (noRetries <= maxRetries) {// retry upto maxRetries times
try {Thread.sleep(1000 + 20000*noRetries);
// with increasing retry interval
// preserve cpu cycle
} catch (InterruptedException iex) { System.out.println("Sleep Interruppted" + iex.getMessage());}
noRetries++;
retry=true;
} else {retry=false;
// don't retry after maxRetries times
}
} catch (java.sql.SQLException sqlex) {//other sql exception, don't retry
retry=false;
} catch ( Exception ex) {// generic exception, don't retry
retry=false;
} finally { try {if (rs!=null) rs.close();
if (stmt1!=null) stmt1.close();
if (conn!=null) conn.close();
} catch (Exception ex) {}}
} while (retry);
...
There is another WebSphere connection pool exception called ConnectionWaitTimeoutException (com.ibm.ejs.cm.pool.ConnectionWaitTimeoutException) that extends SQLException. This exception indicates that the application has waited for the connection timeout (CONN_TIMEOUT, by default 180) number of seconds and has not been returned a connection. We do not suggest that you retry when this exception occurs. Instead, you can increase your connection pool size and/or CONN_TIMEOUT value.
WebSphere is a trademark of the IBM Corporation in the United States, other countries, or both.
IBM is a trademark of the IBM Corporation in the United States, other countries, or both.