How to Configure Two Data Sources JDBC in a Spring Boot Application?

Question

JPA is not used. project structure: each database has its own class-configurator.

file application.properties is

app.datasource.h2.jdbc-url=jdbc:h2:~/database/tester
app.datasource.h2.username=usr
app.datasource.h2.password=pwd

app.datasource.pg.jdbc-url=jdbc:postgresql://localhost:5432/tester
app.datasource.pg.username=usr
app.datasource.pg.password=pwd

config db H2 is DataSourcesConfigurationH2:

@Configuration
@EnableTransactionManagement
public class DataSourcesConfigurationH2 {

    @Bean
    @Primary
    @ConfigurationProperties(prefix="app.datasource.h2")
    public HikariDataSource dataSourceH2(DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }

    @Bean
    @Primary
    public NamedParameterJdbcTemplate jdbcH2(HikariDataSource dataSource) {
        return new NamedParameterJdbcTemplate(dataSource);
    }

    @Bean
    @Primary
    TransactionManager transactionManagerH2(HikariDataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

}

config db postgres is DataSourcesConfigurationPg:

@Configuration
@EnableTransactionManagement
public class DataSourcesConfigurationPg {

    @Bean
    @ConfigurationProperties(prefix="app.datasource.pg")
    public HikariDataSource dataSourcePg(DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }

    @Bean
    public NamedParameterJdbcTemplate jdbcPg(HikariDataSource dataSource) {
        return new NamedParameterJdbcTemplate(dataSource);
    }

    @Bean
    TransactionManager transactionManagerPg(HikariDataSource dataSource) {
        return new DataSourceTransactionManager(dataSource);
    }

}

however, when the application starts, the dataSourcePg value is not initialized.

dataSourceH2.getDriverClassName()  is "org.h2.Driver" 

and

dataSourcePg.getDriverClassName()  is "org.h2.Driver" 

how to initialize the value of dataSourcePg correctly ?

Answer 1

Just use three application.properties files. First one is to select the correct properties file. The other two is to have the two different data sourses.

Answer 2

the solution works for the environment: JDK 11, Spring Boot 2.5.4

fragment application.yml

app:
  datasourceh2:
    jdbcUrl: jdbc:h2:~/database/tester
    driverClassname: org.h2.Driver
    username: usr
    password: pwd

  datasourcepg:
    jdbcUrl: jdbc:postgresql://localhost:5432/tester
    driverClassname: org.postgresql.Driver
    username: usr
    password: pwd

I repeat, each database has its own class-configurator. config db H2 is DataSourcesConfigurationH2:

@Configuration
public class DataSourcesConfigurationH2 {
    @Primary
    @Bean
    @ConfigurationProperties("app.datasourceh2")
    public DataSourceProperties dataSourcePropertiesH2() {
        return new DataSourceProperties();
    }

    @Primary
    @Bean("dataSourceH2")
    @ConfigurationProperties(prefix = "app.datasourceh2")
    public HikariDataSource dataSourceH2() {
        return dataSourcePropertiesH2()
                .initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }

    @Primary
    @Bean
    public NamedParameterJdbcTemplate jdbcTemplateH2()
    {
        DataSource ds = dataSourceH2();
        return new NamedParameterJdbcTemplate(ds);
    }
    
// JdbcRepository - is my class for data manipulation 
    @Primary
    @Bean
    JdbcRepository jdbcRepositoryH2() {
        NamedParameterJdbcTemplate jdbc = jdbcTemplateH2();
        return new JdbcRepository(jdbc);
    };
}

config db postgres is DataSourcesConfigurationPg:

@Configuration
public class DataSourcesConfigurationPg {
    @Bean
    @ConfigurationProperties("app.datasourcepg")
    public DataSourceProperties dataSourcePropertiesPg() {
        return new DataSourceProperties();
    }

    @Bean("dataSourcePg")
    @ConfigurationProperties(prefix = "app.datasourcepg")
    public HikariDataSource dataSourcePg() {
        return dataSourcePropertiesPg()
                .initializeDataSourceBuilder().type(HikariDataSource.class).build();
    }

    @Bean("jdbcTemplatePg")
    public NamedParameterJdbcTemplate jdbcTemplatePg()
    {
        HikariDataSource ds = dataSourcePg();
        return new NamedParameterJdbcTemplate(ds);
    }
    @Bean("jdbcRepositoryPg")
    JdbcRepository jdbcRepositoryPg() {
        NamedParameterJdbcTemplate jdbc = jdbcTemplatePg();
        return new JdbcRepository(jdbc);
    };
}

the dependency injection should be as follows: for db H2 Service:

@Service
public class DataAccessServiceH2 {
    @Autowired
    @Qualifier("jdbcRepositoryH2")
    private JdbcRepository jdbcRepository;
...
}

for db Pg Service:

@Service
public class DataAccessServicePg {
    @Autowired
    @Qualifier("jdbcRepositoryPg")
    private JdbcRepository jdbcRepository;
...
}

next you can call the db H2 methods from DataAccessServiceH2 or postgres methods from DataAccessServicePg. to find the answer, I used: http://docs.spring.io/spring-boot/docs/current/reference/htmlsingle/#howto-two-datasources https://docs.spring.io/spring-boot/docs/current/reference/htmlsingle/#howto.data-access.configure-two-datasources