java Spring boot - 如何配置多个数据源

Spring boot - how to configure multiple datasources

提问by user1126946

I am trying to setup multiple data sources(MySql, Postgres & Oracle) using Spring boot. I am not using JPA. Setting up with a JdbcTemplate.

我正在尝试使用 Spring Boot 设置多个数据源（MySql、Postgres 和 Oracle）。我没有使用 JPA。使用 JdbcTemplate 进行设置。

I have tried setting up something like this.

我试过设置这样的东西。

application.properties

应用程序属性

spring.datasource.test-oracle.username=test-oracle
spring.datasource.test-oracle.password=test-password
spring.datasource.test-oracle.url=dburl/test
spring.datasource.test-oracle.driver-class-name=oracle.jdbc.OracleDriver

spring.datasource.int-oracle.username=int-oracle
spring.datasource.int-oracle.password=int-password
spring.datasource.int-oracle.url=dburl/int
spring.datasource.int-oracle.driver-class-name=oracle.jdbc.driver.OracleDriver

spring.datasource.d.int-mysql.username=user
spring.datasource.d.int-mysql.password=password
spring.datasource.d.int-mysql.url=dburl/d
spring.datasource.d.int-mysql.driver-class-name=com.mysql.jdbc.Driver

spring.datasource.m.int-mysql.username=user
spring.datasource.m.int-mysql.password=password
spring.datasource.m.int-mysql.url=dburl/m
spring.datasource.m.int-mysql.driver-class-name=com.mysql.jdbc.Driver

spring.datasource.d.test-mysql.username=user
spring.datasource.d.test-mysql.password=password
spring.datasource.d.test-mysql.url=dburl/d
spring.datasource.d.test-mysql.driver-class-name=com.mysql.jdbc.Driver

spring.datasource.m.test-mysql.username=user
spring.datasource.m.test-mysql.password=password
spring.datasource.m.test-mysql.url=dburl/m
spring.datasource.m.test-mysql.driver-class-name=com.mysql.jdbc.Driver

MySqlConfiguration.java

MySqlConfiguration.java

@Configuration
public class MySqlConfiguration() {

   @Bean(name = "dMySql")
   @ConfigurationProperties(prefix = "spring.datasource.d.int-mysql")
   public DataSource mysqlDrupalDataSource() {
     return DataSourceBuilder.create().build();
   }

   @Bean(name = "dJdbc")
   public JdbcTemplate drupalJdbcTemplate(DataSource dMySql) {
      return new JdbcTemplate(dMySql);
   }

   @Bean(name = "mMySql")
   @ConfigurationProperties(prefix = "spring.datasource.m.int-mysql")
   public DataSource mysqlDrupalDataSource() {
      return DataSourceBuilder.create().build();
   }

   @Bean(name = "mJdbc")
   public JdbcTemplate drupalJdbcTemplate(DataSource mMySql) {
      return new JdbcTemplate(mMySql);
   }
}

OracleConfiguration.java

Oracle配置文件

@Configuration
public class OracleConfiguration {

   @Primary
   @Bean(name = "tOracle")
   @ConfigurationProperties(prefix = "spring.datasource.test-oracle")
   public DataSource heOracleDataSource() {
      return DataSourceBuilder.create().build();
   }

   @Bean(name = "tOracleJdbc")
   public JdbcTemplate jdbcTemplate(DataSource tOracle) {
      return new JdbcTemplate(tOracle);
   }

   @Bean(name = "iOracle")
   @ConfigurationProperties(prefix = "spring.datasource.int-oracle")
   public DataSource heOracleDataSource() {
      return DataSourceBuilder.create().build();
   }

   @Bean(name = "iOracleJdbc")
   public JdbcTemplate jdbcTemplate(DataSource iOracle) {
      return new JdbcTemplate(iOracle);
   }
}

I am not sure if the above is the correct way to go about this. When I use @Primary as per the boot docs, the Bean that has @Primary is always used. Then I use the configurations in my DAO implementations like this

我不确定以上是否是解决此问题的正确方法。当我按照引导文档使用 @Primary 时，始终使用具有 @Primary 的 Bean。然后我在我的 DAO 实现中使用这样的配置

One of the DAO Implementation

DAO实现之一

@Repository
public class DAOImpl implements DAOInterface {

    @Autowired
    @Qualifier("dJdbc")
    private JdbcTemplate jdbc;

    @Override
    public Map<String, Object> getBasicStudentInfo(String MAIL) {
        return jdbc.queryForMap(GET_BASIC_STUDENT_INFO, new Object[]{MAIL});
}

How do I go about doing this.? I did see many articles which is about mutliple datasources but unfortunately the examples or solutions don't suite me.

我该怎么做。？我确实看过很多关于多数据源的文章，但不幸的是，这些示例或解决方案并不适合我。

Further to this I need to be able to query against the DB's based on some user input. So if a user provides an environment e.g., "test" or "int", how can I trigger the correct properties based on that input.

除此之外，我需要能够根据一些用户输入查询数据库。因此，如果用户提供了一个环境，例如“test”或“int”，我如何根据该输入触发正确的属性。

I understand that Environment is @Autowired into Spring boot and I can intercept the user input, but unsure how I should provide the plumbing between the user input and the DAO configurations.

我知道 Environment 是 @Autowired 到 Spring boot 中，我可以拦截用户输入，但不确定我应该如何提供用户输入和 DAO 配置之间的管道。

If something is unclear or needs a bit more explanation from my side or need more code I can provide that. Any help to resolve this situation would be appreciated.Thanks

如果有什么不清楚或需要我方面的更多解释或需要更多代码，我可以提供。任何帮助解决这种情况将不胜感激。谢谢

回答by Shrikant Salgar

Here is complete solution to your problem ...

这是您问题的完整解决方案......

Your configuration classes will look like this :

您的配置类将如下所示：

MySqlConfiguration.java

MySqlConfiguration.java

@Configuration
public class MySqlConfiguration {

   @Bean(name = "dMySql")
   @ConfigurationProperties(prefix = "spring.datasource.d.int-mysql")
   public DataSource mysqlDrupalDataSource() {
     return DataSourceBuilder.create().build();
   }

   @Bean(name = "dJdbc")
   public JdbcTemplate drupalJdbcTemplate(@Qualifier("dMySql") DataSource dMySql) {
      return new JdbcTemplate(dMySql);
   }

   @Bean(name = "mMySql")
   @ConfigurationProperties(prefix = "spring.datasource.m.int-mysql")
   public DataSource mysqlDrupalDataSource() {
      return DataSourceBuilder.create().build();
   }

   @Bean(name = "mJdbc")
   public JdbcTemplate drupalJdbcTemplate(@Qualifier("mMySql") DataSource mMySql) {
      return new JdbcTemplate(mMySql);
   }
}

OracleConfiguration.java

Oracle配置文件

@Configuration
public class OracleConfiguration {

   @Primary
   @Bean(name = "tOracle")
   @ConfigurationProperties(prefix = "spring.datasource.test-oracle")
   public DataSource heOracleDataSource() {
      return DataSourceBuilder.create().build();
   }

   @Bean(name = "tOracleJdbc")
   public JdbcTemplate jdbcTemplate(@Qualifier("tOracle") DataSource tOracle) {
      return new JdbcTemplate(tOracle);
   }

   @Bean(name = "iOracle")
   @ConfigurationProperties(prefix = "spring.datasource.int-oracle")
   public DataSource heOracleDataSource() {
      return DataSourceBuilder.create().build();
   }

   @Bean(name = "iOracleJdbc")
   public JdbcTemplate jdbcTemplate(@Qualifier("iOracle") DataSource iOracle) {
      return new JdbcTemplate(iOracle);
   }
}

and in your DAO class , you can autowire the JdbcTemplate like this :

在你的 DAO 类中，你可以像这样自动装配 JdbcTemplate：

@Repository
public class DAOImpl implements DAOInterface {

    @Autowired
    @Qualifier("dJdbc")
    private JdbcTemplate dJdbc;

    @Autowired
    @Qualifier("mJdbc")
    private JdbcTemplate mJdbc;

    @Autowired
    @Qualifier("tOracleJdbc")
    private JdbcTemplate tOracleJdbc;

    @Autowired
    @Qualifier("iOracleJdbc")
    private JdbcTemplate iOracleJdbc;

    @Override
    public Map<String, Object> getBasicStudentInfo(String MAIL) {
        return dJdbc.queryForMap(GET_BASIC_STUDENT_INFO, new Object[]{MAIL});
    }

    .
    .
    .
}

Note: Make Sure to annotate one of DataSource with @Primary annotation

注意：确保使用 @Primary 注释来注释 DataSource 之一

回答by yugo

My setup: spring-boot version 1.2.5.RELEASE

我的设置：spring-boot 版本 1.2.5.RELEASE

I succeeded in running a setup like this, with the jdbc being created with the correct DataSources by adding a @Qualifier in each JDBC method creation

我成功地运行了这样的设置，通过在每个 JDBC 方法创建中添加 @Qualifier 使用正确的数据源创建了 jdbc

So, for every JDBC method you should match the qualifying datasource like this

因此，对于每个 JDBC 方法，您都应该像这样匹配符合条件的数据源

@Bean(name = "dJdbc")
public JdbcTemplate drupalJdbcTemplate(@Qualifier("dMySql") DataSource dMySql) {
    return new JdbcTemplate(dMySql);
}

No matter you choose for @Primary, using the @Qualifier for every JDBC should work good. Autowiring jdbcTemplates in repositories, and using @Qualifier for them is ok also.

无论您选择 @Primary，对每个 JDBC 使用 @Qualifier 都应该很好。在存储库中自动装配 jdbcTemplates，并为它们使用 @Qualifier 也可以。

回答by Jeffrey Ellin

In your DAO you could wire in additional jdbctemplates. Then at runtime you can pick which one to use.

在您的 DAO 中，您可以连接额外的 jdbctemplates。然后在运行时您可以选择使用哪一个。

@Repository
public class DAOImpl implements DAOInterface {

@Autowired
@Qualifier("tOracle")
private JdbcTemplate testJdbc;

@Autowired
@Qualifier("intOracle")
private JdbcTemplate intJdbc;

@Override
public Map<String, Object> getBasicStudentInfo(String MAIL, String source) {
    if ("TEST".equals(source)){
          return testJdbc.queryForMap(GET_BASIC_STUDENT_INFO, new Object[]{MAIL});
    }else {
          return intJdbc.queryForMap(GET_BASIC_STUDENT_INFO, new Object[]{MAIL});       
    }
}