Primary / Secondary Failover DataSource in Spring Download

Question

I have a Spring boot application and you need to have a primary and 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 cannot say to reconnect when there are problems.

I know how to make 2 data sources, but where is the best way to process the logic when it will use any. Logic should work as follows:

• Connect to primary
• If a connection problem occurs, or the resource is unavailable, or the connection timed out, try connecting to the primary one.
• If Primary cannot connect, try connecting to Secondary
• If Secondary cannot connect, repeat steps 2 and 3 for X minutes.

Would it be better / possible to handle this in a Spring service? Should I have another service that deals only with this logic, and my other services use this? Would it be better not to connect to the database in the "spring" way and use the "plain old Java method"?

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 omitted since they are fairly simple.

Answer 1

, DZone: https://dzone.com/articles/using-ha-jdbc-with-spring-boot

HA-JDBC .

, , .

, , :

• Factory: - , .
• 2, 1. factory "", , .
• : ( , .
• Factory:
• Factory:

, , - .

, , / ha-jdbc.

Groovy, Java.