title: Primary/Secondary failover DataSource in Spring Boot
body: <p>I have a Spring Boot app and need to have a primary &amp; 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.</p>
  
  <p>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:</p>
  
  <ol>
  <li>Connect to Primary</li>
  <li>If there is a connection problem, either resource is unavailable or connection timeout occurred, try to reconnect to primary.</li>
  <li>If Primary cannot connect, try to connect to Secondary</li>
  <li>If Secondary cannot connect, continue to retry steps 2 &amp; 3 for <em>X</em> minutes.</li>
  </ol>
  
  <p>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"?</p>
  
  <p>Here is an example of what I have where the Service only connects to the Primary.</p>
  
  <p><strong>DatasourcesConfig</strong></p>
  
  <pre><code>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);
      }
  
  }
  </code></pre>
  
  <p><strong>ExampleService</strong></p>
  
  <pre><code>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?
  
      }
  
  }
  </code></pre>
  
  <p>The <strong>SampleRowMapper</strong> and <strong>SampleDTO</strong> classes have been left out since they are pretty basic.</p>