I have poked around this problem for a while. The best solution I've been able to come up with is below. I mostly wrote this for deadlock retry, but would work for lost connections as well. I haven't actually had a chance to try it yet, so YMMV.
- Create a new exception RetryException (or whatever you like) which is a sublcass of a DataAccessException.
- Create a an exception tranlator (I subclassed mine from SQLErrorCodeSQLExceptionTranslator and overrode customTranslate) which can convert the error code indicating a to a RetryException. Use this with your JdbcTemplate. I use a JdbcDaoSupport object to subclass which lets you specify the translator but delegating the JdbcTemplate creation to the class.
- Create a MethodInterceptor to implement the retry logic. Here is one that I wrote which can be configured to retry a RetryException a set number of times before it gives up and throws the RetryException:
Code:
public class RetryInterceptor
implements MethodInterceptor {
private int retry = 0; // Defaults to no retries, bombs out on first failure.
private long delay = 0;
public void setDelay(long delay) {
this.delay = delay;
}
public void setRetry(int retry) {
this.retry = retry;
}
public Object invoke(MethodInvocation invocation) throws Throwable {
for (int i = 0; i < retry; i++) {
try {
return invocation.proceed();
}
catch (RetryException e) {
// Escape out of # of retries has been reached
if (i >= retry)
throw e;
if (delay > 0)
Thread.currentThread().sleep(delay);
}
}
}
}
- Use this interceptor in your proxy. You should make sure this interceptor wraps the transaction interceptor. This is to make sure that when invocation.proceed() is invoked that the code it calls attempts to get a new connection.
recovering from SQL connection loses...
Reply With Quote
Originally Posted by croco
)