如何使用 JDBCTemplate 让线程尝试重新连接数据库 x 次

Question

我有一个线程尝试使用 JDBCTemplate 连接到数据库，如下所示：

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

我想确保如果上面的代码抛出 DataAccessException 或 SQLException，该线程会等待几秒钟并尝试重新连接，例如 5 次以上，然后放弃。我怎样才能实现这个目标？另外，如果在执行过程中数据库出现故障并再次出现，我如何确保我的程序从中恢复并继续运行而不是抛出异常并退出？

提前致谢。

Answer 1

试试这个。我的考虑是：运行循环直到语句成功执行。如果失败，则容忍失败5次，每次等待2秒进行下一次执行。

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

您可能值得研究一下 Spring 的 Aspect 支持。您所描述的是使用（恒定）退避重试，并且您最终可能会在其他地方需要它，无论是与 Web 服务、电子邮件服务器还是任何其他容易出现短暂故障的复杂系统进行通信。

例如，只要抛出异常，这个简单方法就会调用底层方法最多 maxAttempts 次，除非它是 noRetryFor 中列出的 Throwable 的子类。

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

这个建议可以应用于您希望使用 Spring 的 Aspect 支持的任何 bean。请参阅http://static.springsource.org/spring/docs /2.5.x/reference/aop.html 了解更多详细信息。

Answer 3

像这样的东西：

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

不如在上面写一个切面（DBRetryAspect）；这样会更透明。