SpringBoot and SpringJDBC multiple datasources

Question

I am trying to use two datasources with my SpringBoot application and can't get the second datasource to autowire. I have tried many things but this is the closest I have come:

My Yaml file:

spring:
  first-datasource:
    url: MyURLString1
    username: User
    password: Password
    driver-class-name: oracle.jdbc.OracleDriver
  second-datasource:
    url: MyURLString2
    username: User
    password: Password
    driver-class-name: oracle.jdbc.OracleDriver

My Application Class:

@SpringBootApplication
public class MyApplication {

    public static void main(String[] args) {
        SpringApplication.run(MyApplication.class, args);
    }

    @Bean
    @Primary
    @ConfigurationProperties(prefix = "spring.first-datasource")
    public DataSource firstDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.second-datasource")
    public DataSource secondDataSource() {
        return DataSourceBuilder.create().build();
    }
}

And Finally my DAO:

@Repository
public class MyDao {
    private static final String FIRST_SELECT = "select * from SomeTableInDB1";
    private static final String SECOND_SELECT = "select * from AnotherTableInDB2";

    @Autowired
    private JdbcTemplate firstJdbcTemplate;
    @Autowired
    @Qualifier("secondDataSource")
    private JdbcTemplate secondJdbcTemplate;

    List<DB1Entity> getDB1Entity(Long id) {
        return firstJdbcTemplate.query(FIRST_SELECT, new Object[] {id}, new BeanPropertyRowMapper(DB1Entity.class));
    }

    List<DB2Entity> getDB2Entity(Long id) {
        return secondJdbcTemplate.query(SECOND_SELECT, new Object[] {id}, new BeanPropertyRowMapper(DB2Entity.class));
    }
}

This is the closest I have come so far. I say it is closest because if I remove the @Qualifier then both of my dao methods actually work, assuming that the SECOND_SELECT statement is valid SQL for my DB1. Once I put in the @Qualifier for my non-primary datasouce then I get an autowire error because Spring is expecting a Datasouce object, not a JdbcTemplate object. That is weird to me as it does work with the primary datasource.

Here is my error:

Could not autowire field: private org.springframework.jdbc.core.JdbcTemplate org.my.classpath.secondJdbcTemplate; nested exception is org.springframework.beans.factory.NoSuchBeanDefinitionException: No qualifying bean of type [org.springframework.jdbc.core.JdbcTemplate] found for dependency: expected at least 1 bean which qualifies as autowire candidate for this dependency. Dependency annotations: {@org.springframework.beans.factory.annotation.Autowired(required=true), @org.springframework.beans.factory.annotation.Qualifier(value=secondDataSource)}

Measurable

You create the bean of type DataSource but Autowire JdbcTemplate. Your probably should have something like this private JdbcTemplate jdbcTemplate1; private JdbcTemplate jdbcTemplate2; @Autowired @Qualifier("firstDataSource") public void setDataSource(DataSource dataSource) { this.jdbcTemplate1 = new JdbcTemplate(dataSource); } @Autowired @Qualifier("secondDataSource") public void setDataSource(DataSource dataSource) { this.jdbcTemplate2 = new JdbcTemplate(dataSource); } – Meyers

I had tried something almost identical to this but was getting an error about a null URL. I must have mistyped something because I copied your code and it worked. Thanks. I am sure it was something dumb. – Measurable

@Meyers This is the right solution and I think the question is good. Post an answer and I will mark it as the correct one. – Measurable

I am glad it helped! I will post it as an answer, thx – Meyers

Answer 1

You create the bean of type DataSource, but try to Autowire JdbcTemplate which is a mismatch. Your probably should have something like this

private JdbcTemplate jdbcTemplate1;
private JdbcTemplate jdbcTemplate2;

@Autowired
@Qualifier("firstDataSource")
public void setDataSource(DataSource dataSource){
    this.jdbcTemplate1=new JdbcTemplate(dataSource);
}

@Autowired
@Qualifier("secondDataSource")
public void setDataSource(DataSource dataSource){
    this.jdbcTemplate2=new JdbcTemplate(dataSource);
}

Meyers

Answer 2

Ideally, but not a mandate, one of the datasources should be marked PRIMARY for most of the default wiring via Annotations to work. Also, we need to create TransactionManagers for each of the datasources separately otherwise Spring would not know how to enforce Transactions. Following is a complete example of how this should be done

@Primary
@Bean(name = "dataSource")
@ConfigurationProperties(prefix="datasource.mysql")
public DataSource dataSource() {
    return DataSourceBuilder.create().build();
}

@Primary
@Bean(name = "transactionManager")
public DataSourceTransactionManager transactionManager(@Qualifier("dataSource") DataSource dataSource) {
    return new DataSourceTransactionManager();
}

@Bean(name = "postGresDataSource")
@ConfigurationProperties(prefix="datasource.postgres")
public DataSource postgresDataSource() {
    return DataSourceBuilder.create().build();
}

@Bean(name = "postGresTransactionManager")
public DataSourceTransactionManager transactionManager(@Qualifier("postGresDataSource") DataSource dataSource) {
    return new DataSourceTransactionManager();
}

@Transactional(transactionManager="postGresTransactionManager")
public void createCustomer(Customer cust) {
    customerDAO.create(cust);
}

// specifying a transactionManager attribute is optional if we 
// want to use the default transactionManager since we 
// already marked one of the TM above with @Primary
@Transactional
public void createOrder(Order order) {
    orderDAO.create(order);
}

Hope this helps.

Jaimeejaimes

Answer 3

Here also provide another 'not-working' situation that confused me for several days:

when configurating two data sources of same type in Springboot application, the @Qualifier doesn't work as expected to pick up right beans. It behaves like it's not recognized by Spring framework.

the reason is when using @SpringbootApplication annotation which contains @EnableAutoConfiguration annotation, which, in Springboot, will auto-configurate data sources it provides for users.

this would terribly affect @Qualifier's behavior.

Rondure

Answer 4

In my case, following the @Aman Tuladhar answer, worked like that: (Spring Boot 2.1.3.RELEASE)

@Configuration
public class JDBCConfig {

    @Bean("first-datasource")
    public JdbcTemplate paymentsJDBCTemplate(@Qualifier("first-db") DataSource paymentsDataSource){
        return new JdbcTemplate(paymentsDataSource);
    }

    @Bean("second-datasource")
    public JdbcTemplate parametersJDBCTemplate(@Qualifier("second-db") DataSource paramsDataSource){
        return new JdbcTemplate(paramsDataSource);
    }

    @Bean("first-db")
    public DataSource paymentsDataSource(Environment env) {
        return buildDataSource(env, "first");
    }

    @Bean("second-db")
    public DataSource paramsDataSource(Environment env) {
        return buildDataSource(env, "second");
    }

    private DataSource buildDataSource(Environment env, String prop) {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(env.getProperty("spring."+prop+"-datasource.driver-class-name"));
        dataSource.setUrl(env.getProperty("spring."+prop+"-datasource.url"));
        dataSource.setUsername(env.getProperty("spring."+prop+"-datasource.username"));
        dataSource.setPassword(env.getProperty("spring."+prop+"-datasource.password"));

        return dataSource;
    }
}

... and using like that:

@Autowired @Qualifier("first-datasource")
private JdbcTemplate jdbcTemplate1;

@Autowired @Qualifier("second-datasource")
private JdbcTemplate jdbcTemplate2;

... application.yml:

spring:
    datasource:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: ${DATABASE_1_URL}
      username: ${DATABASE_1_USERNAME}
      password: ${DATABASE_1_PASSWORD}
    second-datasource:
      driver-class-name: com.mysql.cj.jdbc.Driver
      url: ${DATABASE_2_URL}
      username: ${DATABASE_2_USERNAME}
      password: ${DATABASE_2_PASSWORD}

Translation