如何在不读取 application.properties 的情况下以编程方式初始化辅助/其他数据源

Question

我开发了一个多租户 Spring 启动应用程序，其中数据源通过存储在 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

数据源配置.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();
    }
}

在这里，数据源通过存储在应用程序属性中的值进行初始化。

多租户属性.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 引导应用程序启动器

@SpringBootApplication(exclude = DataSourceAutoConfiguration.class)
@EnableConfigurationProperties(MultitenancyProperties.class)
public class Application {
    public static void main(String[] args) {
        ApplicationContext ctx = SpringApplication.run(Application.class, args);
    }
}

如何通过读取存储在主数据源表中的数据库凭据，以编程方式读取应用程序属性和其他数据源(数据源 2，数据源 3(，从而仅初始化主数据源。

Answer 1

假设您的 primay 数据源中有一个名称为 DATABASECONFIG 和以下架构的数据库：

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

您可以将DataSourceConfig类更改为以下内容：

@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

我会执行以下操作：

属性文件

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

弹簧配置

@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
    }
}

我不太确定以这种方式使用@ConfigurationProperties是否有效，但您可以随时像现在一样使用它。

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

Answer 3

将

@Configuration类一分为二。在第一个中创建主数据源，然后将其自动连接到第二个数据源;使其可用于从属数据源@Bean方法。

Answer 4

首先你可以导入commons-dbcp2.jar，然后你可以创建一个org.apache.commons.dbcp.BasicDataSource，例如，

 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();

驱动程序类名、url、密码和其他属性可以存储在表中。

我希望我的回答能帮助你。