Primary/Secondary failover DataSource in Spring Boot

Question

I have a Spring Boot app and need to have a primary & secondary DataSource. I need to implement some logic on how to reconnect when there are connection problems. Since Spring makes the connection for you, I can't seem to tell it to reconnect when there are issues.

I know how to make 2 data sources, but where is the best place to handle the logic on when it will use which one. The logic needs to work this way:

1. Connect to Primary
2. If there is a connection problem, either resource is unavailable or connection timeout occurred, try to reconnect to primary.
3. If Primary cannot connect, try to connect to Secondary
4. If Secondary cannot connect, continue to retry steps 2 & 3 for X minutes.

Would it be best/possible to handle this within the Spring Service? Should I have a different service that just deals with this logic and my other services use that? Would it be better to not connect to DB's the "spring way" and use the "plain old java way"?

Here is an example of what I have where the Service only connects to the Primary.

DatasourcesConfig

package com.helloworld.config;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;

import oracle.jdbc.pool.OracleDataSource;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.sql.SQLException;

@Configuration
public class DatasourcesConfig {

    @Primary
    @Bean(name = "primaryDataSource")
    DataSource primaryDataSource() throws SQLException {

        OracleDataSource dataSource = new OracleDataSource();

        dataSource.setUser("user");
        dataSource.setPassword("pass");
        dataSource.setURL("jdbc:oracle:thin:@(...primary connection...)");

        return dataSource;
    }

      @Bean(name = "secondaryDataSource")
      DataSource secondaryDataSource() throws SQLException {

          OracleDataSource dataSource = new OracleDataSource();

          dataSource.setUser("user");
          dataSource.setPassword("pass");
          dataSource.setURL("jdbc:oracle:thin:@(...secondary connection...)");

          return dataSource;
      }

    @Bean(name = "jdbcPrimary")
    @Autowired
    public JdbcTemplate primaryJdbcTemplate(@Qualifier("primaryDataSource") DataSource ds) {
        return new JdbcTemplate(ds);
    }

    @Bean(name = "jdbcSecondary")
    @Autowired
    public JdbcTemplate secondaryJdbcTemplate(@Qualifier("secondaryDataSource") DataSource ds) {
        return new JdbcTemplate(ds);
    }

}

ExampleService

package com.helloworld.service;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Service;

@Service
public class ExampleService {

    @Autowired
    @Qualifier("jdbcPrimary")
    private JdbcTemplate jdbcTemplatePrimary;

    @Autowired
    @Qualifier("jdbcSecondary")
    private JdbcTemplate jdbcTemplateSecondary;

    public SampleDTO getData(String a, String b){

        final String sql = "select a, b from TABLE_A where a=? and b=?";

        // Only checking Primary
        return jdbcTemplatePrimary.queryForObject(sql,
                new Object[]{a,b},
                new SampleRowMapper());

        // Is this the best place to catch exceptions and connect to Secondary?

    }

}

The SampleRowMapper and SampleDTO classes have been left out since they are pretty basic.

Answer 1

I was able to implement this sort of a mechanism using this article from DZone: https://dzone.com/articles/using-ha-jdbc-with-spring-boot using enter link description here

HA-JDBC is quite configurable and has several different failover strategies.

In our case, we setup connectivity to two different databases, our primary is backed up to the secondary so may not be current.

We are using this database as read-only so do not need to worry about transactions so set it up as follows:

1. Balancer Factory: Simple - It will always try the primary unless it is not available.
2. The weight of the primary database was set as 2, the weight of the secondary set as 1. With the balancer factory set as 'simple' this should force the driver to go to the primary unless we have a problem with it.
3. Default Sync Strategy: Simple (we don't need to worry about this because it is read-only.
4. Database Metadata Cache Factory: Simple
5. State Manager factory: Simple

I was unable to get password obfuscation working at this point but will revisit that someday.

I also, initially, had problems getting logged in but recognized that I did need a username/password on the ha-jdbc driver.

The example above is written in Groovy, not Java.

Answer 2

i think you can use circuit-breaker pattern; When primary data source fail, then circuit breaker fallback method runs and it uses secondary data source. In fallback method you can retry fail statuses. Hystrix is the good option for circuit-breaker pattern, you can use it. Hope helps:)

Spring circuit breaker impl; https://spring.io/guides/gs/circuit-breaker/