How to initialize secondary/other datasources programatically without reading application.properties

Question

I have a developed a multi-tenancy spring boot application where in the datasources are initialized via database credentials stored in application.properties as shown below :

application.properties

spring.multitenancy.datasource1.url=jdbc:mysql://localhost:3306/db1
spring.multitenancy.datasource1.username=root
spring.multitenancy.datasource1.password=****
spring.multitenancy.datasource1.driver-class-name=com.mysql.jdbc.Driver
spring.jpa.hibernate.ddl-auto=update

spring.multitenancy.datasource2.url=jdbc:mysql://localhost:3306/db2
spring.multitenancy.datasource2.username=root
spring.multitenancy.datasource2.password=****
spring.multitenancy.datasource2.driver-class-name=com.mysql.jdbc.Driver
spring.jpa.hibernate.ddl-auto=update

spring.multitenancy.datasource3.url=jdbc:mysql://localhost:3306/db3
spring.multitenancy.datasource3.username=root
spring.multitenancy.datasource3.password=****
spring.multitenancy.datasource3.driver-class-name=com.mysql.jdbc.Driver
spring.jpa.hibernate.ddl-auto=update

DataSourceConfig.java

@Configuration
public class DataSourceConfig {

    @Autowired
    private MultitenancyProperties multitenancyProperties;

    @Bean(name = { "dataSource", "dataSource1" })
    @ConfigurationProperties(prefix = "spring.multitenancy.datasource1")
    public DataSource dataSource1() {
        DataSourceBuilder factory = DataSourceBuilder
                .create(this.multitenancyProperties.getDatasource1().getClassLoader())
                .driverClassName(this.multitenancyProperties.getDatasource1().getDriverClassName())
                .username(this.multitenancyProperties.getDatasource1().getUsername())
                .password(this.multitenancyProperties.getDatasource1().getPassword())
                .url(this.multitenancyProperties.getDatasource1().getUrl());
        return factory.build();
    }

    @Bean(name = "dataSource2")
    @ConfigurationProperties(prefix = "spring.multitenancy.datasource2")
    public DataSource dataSource2() {
        DataSourceBuilder factory = DataSourceBuilder
                .create(this.multitenancyProperties.getDatasource2().getClassLoader())
                .driverClassName(this.multitenancyProperties.getDatasource2().getDriverClassName())
                .username(this.multitenancyProperties.getDatasource2().getUsername())
                .password(this.multitenancyProperties.getDatasource2().getPassword())
                .url(this.multitenancyProperties.getDatasource2().getUrl());
        return factory.build();
    }

    @Bean(name = "dataSource3")
    @ConfigurationProperties(prefix = "spring.multitenancy.datasource3")
    public DataSource dataSource3() {
        DataSourceBuilder factory = DataSourceBuilder
                .create(this.multitenancyProperties.getDatasource3().getClassLoader())
                .driverClassName(this.multitenancyProperties.getDatasource3().getDriverClassName())
                .username(this.multitenancyProperties.getDatasource3().getUsername())
                .password(this.multitenancyProperties.getDatasource3().getPassword())
                .url(this.multitenancyProperties.getDatasource3().getUrl());
        return factory.build();
    }
}

Here, data sources are initialized via values stored in application properties.

MultitenancyProperties.java

@ConfigurationProperties("spring.multitenancy")
public class MultitenancyProperties {

    @NestedConfigurationProperty
    private DataSourceProperties datasource1;

    @NestedConfigurationProperty
    private DataSourceProperties datasource2;

    @NestedConfigurationProperty
    private DataSourceProperties datasource3;

    public DataSourceProperties getDatasource1() {
        return datasource1;
    }

    public void setDatasource1(DataSourceProperties datasource1) {
        this.datasource1 = datasource1;
    }

    public DataSourceProperties getDatasource2() {
        return datasource2;
    }

    public void setDatasource2(DataSourceProperties datasource2) {
        this.datasource2 = datasource2;
    }

    public DataSourceProperties getDatasource3() {
        return datasource3;
    }

    public void setDatasource3(DataSourceProperties datasource3) {
        this.datasource3 = datasource3;
    }
}

Spring boot application launcher

@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)
@EnableConfigurationProperties(MultitenancyProperties.class)
public class Application {

    public static void main(String[] args) {
        ApplicationContext ctx = SpringApplication.run(Application.class, args);
    }
}

How to initialize only primary datasource by reading the application.properties and other datasources(dataSource2,dataSource3) programatically by reading the database credentials stored in a table of primary datasource.

Answer 1

Assuming you have a DB in your primay datasource of the name DATABASECONFIG and the following schema:

+-----------+-----------+-----------+-----------+-----------+
|                         DATABASECONFIG                    |
+-----------+-----------+-----------+-----------+-----------+
| DB_NAME   |     URL   |  USERNAME | PASSWORD  |  DRIVER   |
+-----------+-----------+-----------+-----------+-----------+

You can change DataSourceConfig class to something like:

@Configuration
public class DataSourceConfig {

    @Autowired
    private MultitenancyProperties multitenancyProperties;

    @Bean(name = { "dataSource", "dataSource1" })
    @ConfigurationProperties(prefix = "spring.multitenancy.datasource1")
    public DataSource dataSource1() throws SQLException {
        ClassLoader classLoader = this.multitenancyProperties.getDatasource1().getClassLoader();
        DataSourceBuilder factory = DataSourceBuilder
                .create(this.multitenancyProperties.getDatasource1().getClassLoader())
                .driverClassName(this.multitenancyProperties.getDatasource1().getDriverClassName())
                .username(this.multitenancyProperties.getDatasource1().getUsername())
                .password(this.multitenancyProperties.getDatasource1().getPassword())
                .url(this.multitenancyProperties.getDatasource1().getUrl());
        return factory.build();
    }

    @Bean(name = "dataSource2")
    @ConfigurationProperties(prefix = "spring.multitenancy.datasource2")
    public DataSource dataSource2()
            throws Exception {
        String dataSourceName = "datasource2";
        return this.getSecondaryDataSource(dataSourceName);
    }

    @Bean(name = "dataSource3")
    @ConfigurationProperties(prefix = "spring.multitenancy.datasource3")
    public DataSource dataSource3()
            throws Exception {
        String dataSourceName = "datasource3";
        return this.getSecondaryDataSource(dataSourceName);
    }

    private DataSource getSecondaryDataSource(String dataSourceName)
            throws Exception {
        DataSource d = this.dataSource1();
        PreparedStatement preparedStatement = d.getConnection().prepareStatement("SELECT * FROM DATABASECONFIG WHERE DB_NAME = ?");
        preparedStatement.setString(1, dataSourceName);
        ResultSet resultSet = preparedStatement.executeQuery();
        if (!resultSet.next()) {
            // No result found --> throw exception
            throw new Exception("Error Finding DB Config for DataSource [" + dataSourceName + "].");
        }
        DataSourceBuilder factory = DataSourceBuilder
                .create()
                .driverClassName(resultSet.getString("DRIVER"))
                .username(resultSet.getString("USERNAME"))
                .password(resultSet.getString("PASSWORD"))
                .url(resultSet.getString("URL"));
        return factory.build();
    }
}