How to make a thread try to reconnect to the database x times using JDBCTemplate

Question

I have one thread trying to connect to a database using JDBCTemplate as follows:

JDBCTemplate jdbcTemplate = new JdbcTemplate(dataSource); try{ jdbcTemplate.execute(new CallableStatementCreator() { @Override public CallableStatement createCallableStatement(Connection con) throws SQLException { return con.prepareCall(query); } }, new CallableStatementCallback() { @Override public Object doInCallableStatement(CallableStatement cs) throws SQLException { cs.setString(1, subscriberID); cs.execute(); return null; } }); } catch (DataAccessException dae) { throw new CougarFrameworkException( "Problem removing subscriber from events queue: " + subscriberID, dae); } 

I want to make sure that if the above code throws a DataAccessException or SQLException, the thread waits a few seconds and tries to connect again, say 5 more times, and then surrenders. How can i achieve this? Also, if at runtime the database drops and reappears, how can I guarantee that my program will recover from this and continue working instead of throwing an exception and quitting?

Thanks in advance.

Answer 1

Try it. My thoughts: run the loop until the statements are successful. If a failure occurs, allow the failure 5 times and each time it waits 2 seconds for the next execution.

 JDBCTemplate jdbcTemplate = new JdbcTemplate(dataSource); boolean successfullyExecuted = false; int failCount = 0; while (!successfullyExecuted){ try{ jdbcTemplate.execute(new CallableStatementCreator() { @Override public CallableStatement createCallableStatement(Connection con) throws SQLException { return con.prepareCall(query); } }, new CallableStatementCallback() { @Override public Object doInCallableStatement(CallableStatement cs) throws SQLException { cs.setString(1, subscriberID); cs.execute(); return null; } }); successfullyExecuted = true; } catch (DataAccessException dae) { if (failedCount < 5){ failedCount ++; try{java.lang.Thread.sleep(2 * 1000L); // Wait for 2 seconds }catch(java.lang.Exception e){} }else{ throw new CougarFrameworkException( "Problem removing subscriber from events queue: " + subscriberID, dae); } } catch (java.sql.SQLException sqle){ if (failedCount < 5){ failedCount ++; }else{ try{java.lang.Thread.sleep(2 * 1000L); // Wait for 2 seconds }catch(java.lang.Exception e){} throw new CougarFrameworkException( "Problem removing subscriber from events queue: " + subscriberID, dae); } } } 

Answer 2

Maybe you should take a look at Spring Aspect support. What you are describing is trying again (permanent) deferment, and most likely you will end up needing it somewhere else, whether it’s a conversation with a web service, an email server, or any other complex system prone to temporary failures .

For example, this simple method calls the base method up to maxAttempts whenever an exception is thrown, unless it is a Throwable subclass specified in noRetryFor.

 private Object doRetryWithExponentialBackoff(ProceedingJoinPoint pjp, int maxAttempts, Class<? extends Throwable>[] noRetryFor) throws Throwable { Throwable lastThrowable = null; for (int attempts = 0; attempts < maxAttempts; attempts++) { try { pauseExponentially(attempts, lastThrowable); return pjp.proceed(); } catch (Throwable t) { lastThrowable = t; for (Class<? extends Throwable> noRetryThrowable : noRetryFor) { if (noRetryThrowable.isAssignableFrom(t.getClass())) { throw t; } } } } throw lastThrowable; } private void pauseExponentially(int attempts, Throwable lastThrowable) { if (attempts == 0) return; long delay = (long) (Math.random() * (Math.pow(4, attempts) * 100L)); log.warn("Retriable error detected, will retry in " + delay + "ms, attempts thus far: " + attempts, lastThrowable); try { Thread.sleep(delay); } catch (InterruptedException e) { // Nothing we need to do here } } 

This tip can be applied to any bean that you want to use with Spring Aspect support. See http://static.springsource.org/spring/docs/2.5.x/reference/aop.html for more details.

Answer 3

something like that:

 private int retries; /** * Make this configurable. */ public void setRetries(final int retries) { Assert.isTrue(retries > 0); this.retries = retries; } public Object yourMethod() { final int tries = 0; Exception lastException = null; for (int i = 0; i < this.retries; i++) { try { return jdbcTemplate.execute ... (your code here); } catch (final SQLException e) { lastException = e; } catch (final DataAccessException e) { lastException = e; } } throw lastException; } 

Answer 4

How about writing an aspect (DBRetryAspect) over it, it will be more transparent.