java - Spring boot - how to configure multiple datasources

Question

I am trying to setup multiple data sources(MySql, Postgres & Oracle) using Spring boot. I am not using JPA. Setting up with a JdbcTemplate.

I have tried setting up something like this.

application.properties

spring.datasource.test-oracle.username=test-oracle
spring.datasource.test-oracle.password=test-password
spring.datasource.test-oracle.url=dburl/test
spring.datasource.test-oracle.driver-class-name=oracle.jdbc.OracleDriver

spring.datasource.int-oracle.username=int-oracle
spring.datasource.int-oracle.password=int-password
spring.datasource.int-oracle.url=dburl/int
spring.datasource.int-oracle.driver-class-name=oracle.jdbc.driver.OracleDriver

spring.datasource.d.int-mysql.username=user
spring.datasource.d.int-mysql.password=password
spring.datasource.d.int-mysql.url=dburl/d
spring.datasource.d.int-mysql.driver-class-name=com.mysql.jdbc.Driver

spring.datasource.m.int-mysql.username=user
spring.datasource.m.int-mysql.password=password
spring.datasource.m.int-mysql.url=dburl/m
spring.datasource.m.int-mysql.driver-class-name=com.mysql.jdbc.Driver

spring.datasource.d.test-mysql.username=user
spring.datasource.d.test-mysql.password=password
spring.datasource.d.test-mysql.url=dburl/d
spring.datasource.d.test-mysql.driver-class-name=com.mysql.jdbc.Driver

spring.datasource.m.test-mysql.username=user
spring.datasource.m.test-mysql.password=password
spring.datasource.m.test-mysql.url=dburl/m
spring.datasource.m.test-mysql.driver-class-name=com.mysql.jdbc.Driver

MySqlConfiguration.java

@Configuration
public class MySqlConfiguration() {

   @Bean(name = "dMySql")
   @ConfigurationProperties(prefix = "spring.datasource.d.int-mysql")
   public DataSource mysqlDrupalDataSource() {
     return DataSourceBuilder.create().build();
   }

   @Bean(name = "dJdbc")
   public JdbcTemplate drupalJdbcTemplate(DataSource dMySql) {
      return new JdbcTemplate(dMySql);
   }

   @Bean(name = "mMySql")
   @ConfigurationProperties(prefix = "spring.datasource.m.int-mysql")
   public DataSource mysqlDrupalDataSource() {
      return DataSourceBuilder.create().build();
   }

   @Bean(name = "mJdbc")
   public JdbcTemplate drupalJdbcTemplate(DataSource mMySql) {
      return new JdbcTemplate(mMySql);
   }
}

OracleConfiguration.java

@Configuration
public class OracleConfiguration {

   @Primary
   @Bean(name = "tOracle")
   @ConfigurationProperties(prefix = "spring.datasource.test-oracle")
   public DataSource heOracleDataSource() {
      return DataSourceBuilder.create().build();
   }

   @Bean(name = "tOracleJdbc")
   public JdbcTemplate jdbcTemplate(DataSource tOracle) {
      return new JdbcTemplate(tOracle);
   }

   @Bean(name = "iOracle")
   @ConfigurationProperties(prefix = "spring.datasource.int-oracle")
   public DataSource heOracleDataSource() {
      return DataSourceBuilder.create().build();
   }

   @Bean(name = "iOracleJdbc")
   public JdbcTemplate jdbcTemplate(DataSource iOracle) {
      return new JdbcTemplate(iOracle);
   }
}

I am not sure if the above is the correct way to go about this. When I use @Primary as per the boot docs, the Bean that has @Primary is always used. Then I use the configurations in my DAO implementations like this

One of the DAO Implementation

@Repository
public class DAOImpl implements DAOInterface {

    @Autowired
    @Qualifier("dJdbc")
    private JdbcTemplate jdbc;

    @Override
    public Map<String, Object> getBasicStudentInfo(String MAIL) {
        return jdbc.queryForMap(GET_BASIC_STUDENT_INFO, new Object[]{MAIL});
}

How do I go about doing this.? I did see many articles which is about mutliple datasources but unfortunately the examples or solutions don't suite me.

Further to this I need to be able to query against the DB's based on some user input. So if a user provides an environment e.g., "test" or "int", how can I trigger the correct properties based on that input.

I understand that Environment is @Autowired into Spring boot and I can intercept the user input, but unsure how I should provide the plumbing between the user input and the DAO configurations.

If something is unclear or needs a bit more explanation from my side or need more code I can provide that. Any help to resolve this situation would be appreciated.Thanks

Answer 1

Here is complete solution to your problem ...

Your configuration classes will look like this :

MySqlConfiguration.java

@Configuration
public class MySqlConfiguration {

   @Bean(name = "dMySql")
   @ConfigurationProperties(prefix = "spring.datasource.d.int-mysql")
   public DataSource mysqlDrupalDataSource() {
     return DataSourceBuilder.create().build();
   }

   @Bean(name = "dJdbc")
   public JdbcTemplate drupalJdbcTemplate(@Qualifier("dMySql") DataSource dMySql) {
      return new JdbcTemplate(dMySql);
   }

   @Bean(name = "mMySql")
   @ConfigurationProperties(prefix = "spring.datasource.m.int-mysql")
   public DataSource mysqlDrupalDataSource() {
      return DataSourceBuilder.create().build();
   }

   @Bean(name = "mJdbc")
   public JdbcTemplate drupalJdbcTemplate(@Qualifier("mMySql") DataSource mMySql) {
      return new JdbcTemplate(mMySql);
   }
}

OracleConfiguration.java

@Configuration
public class OracleConfiguration {

   @Primary
   @Bean(name = "tOracle")
   @ConfigurationProperties(prefix = "spring.datasource.test-oracle")
   public DataSource heOracleDataSource() {
      return DataSourceBuilder.create().build();
   }

   @Bean(name = "tOracleJdbc")
   public JdbcTemplate jdbcTemplate(@Qualifier("tOracle") DataSource tOracle) {
      return new JdbcTemplate(tOracle);
   }

   @Bean(name = "iOracle")
   @ConfigurationProperties(prefix = "spring.datasource.int-oracle")
   public DataSource heOracleDataSource() {
      return DataSourceBuilder.create().build();
   }

   @Bean(name = "iOracleJdbc")
   public JdbcTemplate jdbcTemplate(@Qualifier("iOracle") DataSource iOracle) {
      return new JdbcTemplate(iOracle);
   }
}

and in your DAO class , you can autowire the JdbcTemplate like this :

@Repository
public class DAOImpl implements DAOInterface {

    @Autowired
    @Qualifier("dJdbc")
    private JdbcTemplate dJdbc;

    @Autowired
    @Qualifier("mJdbc")
    private JdbcTemplate mJdbc;

    @Autowired
    @Qualifier("tOracleJdbc")
    private JdbcTemplate tOracleJdbc;

    @Autowired
    @Qualifier("iOracleJdbc")
    private JdbcTemplate iOracleJdbc;

    @Override
    public Map<String, Object> getBasicStudentInfo(String MAIL) {
        return dJdbc.queryForMap(GET_BASIC_STUDENT_INFO, new Object[]{MAIL});
    }

    .
    .
    .
}

Note: Make Sure to annotate one of DataSource with @Primary annotation