How to initialize secondary / other data sources programmatically without reading application.properties

Question

I have developed a multitasking application for loading spring, where in the data sources the databases stored in application.properties are initialized through the credentials, 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 with the values ​​stored in the 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 launch application launch

 @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 the primary data source by reading application.properties and other data sources (dataSource2, dataSource3) programmatically, reading the database credentials stored in the table of the primary data source.

Answer 1

Assuming you have a DB in the original data source named DATABASECONFIG and the following schema:

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

You can change the 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(); } } 

Answer 2

I would do the following:

Properties file

 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 Configuration

 @Configuration public class DataSourceConfig { @ConfigurationProperties(prefix = "spring.multitenancy.datasource1") private DataSourceProperties ds; @Bean public DataSource dataSource1() { DataSourceBuilder factory = DataSourceBuilder .create(ds.getClassLoader()) .driverClassName(ds.getDriverClassName()) .username(ds.getUsername()) .password(ds.getPassword()) .url(ds.getUrl()); return factory.build(); } @Bean public DataSource dataSource2() { createFromDataSource1Conf("key2"); } @Bean public DataSource dataSource3() { createFromDataSource1Conf("key3"); } private DataSource createFromDataSource1Conf(Object configPrefix) { // Query db and create datasources } } 

I'm not quite sure that using @ConfigurationProperties will work, but you can always use it as it is now.

 @ConfigurationProperties(prefix = "spring.multitenancy.datasource1") private DataSourceProperties ds; 

Answer 3

Divide the @Configuration class into two. Create a primary data source in the first, then auto-install it in the second ; making it available to sub-methods of the @ Bean data source.

Answer 4

You can import commons-dbcp2.jar first, then you can create org.apache.commons.dbcp.BasicDataSource, for example

  BasicDataSource ds = new BasicDataSource(); ds.setDriverClassName("com.mysql.jdbc.Driver"); ds.setUrl("jdbc:mysql://localhost:3306/housesearch"); ds.setUsername("root"); ds.setPassword(""); ds.setInitialSize(50); ds.setMaxIdle(30); Connection connection = ds.getConnection(); Statement statement = connection.createStatement(); ResultSet resultSet = statement.executeQuery("select * from pay_info"); if(resultSet.next()){ System.out.println(resultSet.getInt(5)); } connection.close(); ds.close(); 

The driver name, URL name, password, and other properties are stored as a table.

I want my answer to help you.