spring boot retry connecting to database untill connection works

Question

I'm trying to have a spring boot app retry to connect to a database when a connection fails and currently have this:

package backend.configuration.dbconnection;

import lombok.extern.java.Log;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.retry.annotation.EnableRetry;
import org.springframework.retry.support.RetryTemplate;

import javax.sql.DataSource;

@Log
@Configuration
@EnableRetry
public class CustomDataSourceConfiguration {
    @Autowired
    private RetryTemplate retryTemplate;

    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        //dataSource.setUrl("jdbc:mysql://localhost:3306/db");
        dataSource.setUrl("jdbc:mysql://localhost:3307/db");
        dataSource.setUsername("user");
        dataSource.setPassword("password");
        retryTemplate.execute(context -> {
            try {
                dataSource.getConnection();
                return dataSource;
            } catch (Exception e) {
                return null;
            }
        });
        return dataSource;
    }
}

and this:

package backend.configuration.dbconnection;

import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.retry.annotation.EnableRetry;
import org.springframework.retry.backoff.FixedBackOffPolicy;
import org.springframework.retry.policy.SimpleRetryPolicy;
import org.springframework.retry.support.RetryTemplate;

@Configuration
@EnableRetry
public class RetryConfiguration {
    @Bean
    public RetryTemplate retryTemplate() {
        RetryTemplate retryTemplate = new RetryTemplate();
        SimpleRetryPolicy retryPolicy = new SimpleRetryPolicy();
        retryPolicy.setMaxAttempts(5);
        retryTemplate.setRetryPolicy(retryPolicy);
        FixedBackOffPolicy backOffPolicy = new FixedBackOffPolicy();
        backOffPolicy.setBackOffPeriod(1000);
        retryTemplate.setBackOffPolicy(backOffPolicy);
        return retryTemplate;
    }
}

returning null upon not being able to connect, so that the retry will kick in. This seems to not be the case. How can I fix this?

Answer 1

if you are using spring-boot 3.0.0 / spring-framkework6.0.0 or higher , you can try to use the retry tools in reactor which is a non-blocking network framework.

the code snippet is like following

   @Bean
    public DataSource dataSource() {
         DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        //dataSource.setUrl("jdbc:mysql://localhost:3306/db");
        dataSource.setUrl("jdbc:mysql://localhost:3307/db");
        dataSource.setUsername("user");
        dataSource.setPassword("password");

        var connectionIsClosed = Mono.just(dataSource)
                .map(e -> {
                    Connection connection = null;
                    try {
                        connection = dataSource.getConnection();
                        var closed = connection.isClosed();
                        if (!closed) {
                            return connection;
                        }
                        throw new RuntimeException("connection is closed");
                    } catch (SQLException ex) {
                        throw new RuntimeException(ex);
                    }
                })
                .retryWhen(Retry.backoff(3, Duration.ofSeconds(1)) // retry 3 times, and backoff 1 second
                        .maxBackoff(Duration.ofSeconds(5))  // max backoff time
                        .filter(throwable -> throwable instanceof RuntimeException)  // it only retry when the exception is RuntimeException
                        .onRetryExhaustedThrow((spec, rs) -> new ConnectException("remote server is invalid !")) // if retry 3 times and still failed, then throw ConnectException
                        .doBeforeRetry(retrySignal -> System.out.println("Retrying... connecting to database..."))) // print log before retry
                .block();
return dataSource;

Answer 2

Got it to work using this slight modification of the code provided by @Peng

package backend.configuration.dbconnection;



import lombok.extern.java.Log;
import org.springframework.boot.autoconfigure.amqp.RabbitProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.retry.annotation.EnableRetry;
import reactor.core.publisher.Mono;
import reactor.util.retry.Retry;


import javax.sql.DataSource;
import java.net.ConnectException;
import java.sql.Connection;
import java.sql.SQLException;
import java.time.Duration;

@Log
@Configuration
@EnableRetry
public class CustomDataSourceConfiguration {

    @Bean
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName("com.mysql.cj.jdbc.Driver");
        //dataSource.setUrl("jdbc:mysql://localhost:3306/db");
        dataSource.setUrl("jdbc:mysql://localhost:3307/db");
        dataSource.setUsername("user");
        dataSource.setPassword("password");

        var connectionIsClosed = Mono.just(dataSource)
                .map(e -> {
                    Connection connection = null;
                    try {
                        connection = dataSource.getConnection();
                        var closed = connection.isClosed();
                        if (!closed) {
                            return connection;
                        }
                        throw new RuntimeException("connection is closed");
                    } catch (SQLException ex) {
                        throw new RuntimeException(ex);
                    }
                })
                .retryWhen(Retry.backoff(100, Duration.ofSeconds(1)) // retry 3 times, and backoff 1 second
                        .maxBackoff(Duration.ofSeconds(5))  // max backoff time
                        .filter(throwable -> throwable instanceof RuntimeException)  // it only retry when the exception is RuntimeException
                        .onRetryExhaustedThrow((spec, rs) -> new ConnectException("remote server is invalid !")) // if retry 3 times and still failed, then throw ConnectException
                        .doBeforeRetry(retrySignal -> log.warning("could not connect to database, retrying"))) // print log before retry
                .block();
        return dataSource;
    }
}