Java 如何在 Spring Boot 中结合 Flyway 使用带有两个数据源的 HikariCP

How to use HikariCP in Spring Boot with two datasources in conjunction with Flyway

提问by ThomasW

I want to use HikariCP as JDBC connection pool in my Spring boot application. I have two datasources (MySQL database as the primary database and accessing those data through Hibernate and additionally an Oracle database for reading some other data through JDBCTemplate).

我想在 Spring boot 应用程序中使用 HikariCP 作为 JDBC 连接池。我有两个数据源（MySQL 数据库作为主数据库并通过 Hibernate 访问这些数据，另外还有一个 Oracle 数据库用于通过 JDBCTemplate 读取其他一些数据）。

I set the MySQL datasource as primary bean:

我将 MySQL 数据源设置为主要 bean：

@Bean
@Primary
@ConfigurationProperties("spring.datasource")
public DataSourceProperties mySQLDataSourceProperties() {
    return new DataSourceProperties();
}


@Bean
@Primary
@ConfigurationProperties("spring.datasource")
public DataSource mySQLDataSource() {
    return mySQLDataSourceProperties().initializeDataSourceBuilder().build();
}

@Bean
@ConfigurationProperties("oracle.datasource")
public DataSourceProperties oracleDataSourceProperties() {
    return new DataSourceProperties();
}

@Bean(name = "oracleDatabase")
@ConfigurationProperties("oracle.datasource")
public DataSource oracleDataSource() {
    return oracleDataSourceProperties().initializeDataSourceBuilder().build();
}

 @Bean
 public JdbcTemplate oracleJdbcTemplate(@Qualifier("oracleDatabase") DataSource oracleDb) {
     return new JdbcTemplate(oracleDb);
 }

and I put the following configurations in my application.properties :

我将以下配置放在我的 application.properties 中：

spring.datasource.type=com.zaxxer.hikari.HikariDataSource

spring.datasource.hikari.minimum-idle=7
spring.datasource.hikari.pool-name=Test-1

spring.datasource.hikari.data-source-properties.prepStmtCacheSize=250
spring.datasource.hikari.data-source-properties.prepStmtCacheSqlLimit=2048
spring.datasource.hikari.data-source-properties.cachePrepStmts=true
spring.datasource.hikari.data-source-properties.useServerPrepStmts=true

Unforuntately, these HikariCP configurations are not being read :

不幸的是，这些 HikariCP 配置没有被读取：

 HikariConfig - dataSourceJNDI..................none
 HikariConfig - dataSourceProperties............{password=<masked>}
 HikariConfig - driverClassName................."com.mysql.jdbc.Driver"
 HikariConfig - healthCheckProperties...........{}
 HikariConfig - healthCheckRegistry.............none
 HikariConfig - idleTimeout.....................600000
 HikariConfig - initializationFailFast..........true
 HikariConfig - initializationFailTimeout.......1
 HikariConfig - isolateInternalQueries..........false
 HikariConfig - jdbc4ConnectionTest.............false
 HikariConfig - jdbcUrl........................."jdbc:mysql://localhost:3306/testDB"
 HikariConfig - leakDetectionThreshold..........0
 HikariConfig - maxLifetime.....................1800000
 HikariConfig - maximumPoolSize.................10
 HikariConfig - metricRegistry..................none
 HikariConfig - metricsTrackerFactory...........none
 HikariConfig - minimumIdle.....................10
 HikariConfig - password........................<masked>
 HikariConfig - poolName........................"HikariPool-1"

Creating the HikariCP beans and deactivating the DataSource autoconfiguration and removing "spring.datasource" :

创建 HikariCP beans 并停用 DataSource 自动配置并删除 "spring.datasource" ：

@EnableAutoConfiguration(exclude = {DataSourceAutoConfiguration.class})
@SpringBootApplication
@ComponentScan
public class SpringApplication {


@Bean
@Primary
@ConfigurationProperties(prefix = "spring.datasource.hikari")
public HikariConfig hikariConfig() {
    return new HikariConfig();
}

@Bean
public DataSource dataSource() {
    return new HikariDataSource(hikariConfig());
}

solves my problem :

解决了我的问题：

 HikariConfig - dataSourceJNDI..................none
 HikariConfig - dataSourceProperties............{password=<masked>, prepStmtCacheSqlLimit=2048, cachePrepStmts=true, useServerPrepStmts=true, prepStmtCacheSize=250}
 HikariConfig - driverClassName................."com.mysql.jdbc.Driver"
 HikariConfig - healthCheckProperties...........{}
 HikariConfig - healthCheckRegistry.............none
 HikariConfig - idleTimeout.....................600000
 HikariConfig - initializationFailFast..........true
 HikariConfig - initializationFailTimeout.......1
 HikariConfig - isolateInternalQueries..........false
 HikariConfig - jdbc4ConnectionTest.............false
 HikariConfig - jdbcUrl........................."jdbc:mysql://localhost:3306/testDB?autoReconnect=true"
 HikariConfig - leakDetectionThreshold..........0
 HikariConfig - maxLifetime.....................1800000
 HikariConfig - poolName........................"Test-1"

But then the Flyway showing some weird warnings which were not shown before and I have to create the database Schema manually before running the Spring application, that is : the create schema does not work anymore.

但是随后 Flyway 显示了一些以前未显示的奇怪警告，我必须在运行 Spring 应用程序之前手动创建数据库架构，即：创建架构不再起作用。

[WARN ] JdbcTemplate - DB: Can't create database 'test'; database exists (SQL State: HY000 - Error Code: 1007)
[WARN ] JdbcTemplate - DB: Unknown table 'testSchema.tenant' (SQL State: 42S02 - Error Code: 1051)
[WARN ] JdbcTemplate - DB: Unknown table 'testSchema.user' (SQL State: 42S02 - Error Code: 1051) 

My Flyway SQL scripts are plain DDL scripts :

我的 Flyway SQL 脚本是普通的 DDL 脚本：

CREATE SCHEMA IF NOT EXISTS `testSchema` DEFAULT CHARACTER SET utf8 ;

DROP TABLE IF EXISTS `testSchema`.`tenant`;

CREATE TABLE `testSchema`.`tenant` (
  `id` int NOT NULL AUTO_INCREMENT,

I think that disabling the Auto-Datasource configuration is not the best solution since Flyway stops creating the schema and showing warnings. Is there any other way to solve this ?

我认为禁用自动数据源配置不是最好的解决方案，因为 Flyway 停止创建架构并显示警告。有没有其他方法可以解决这个问题？

回答by Andy Wilkinson

Declaring your own DataSourcewill already have implicity disabled Spring Boot's auto-configuration of a data source. In other words this won't be having any effect:

声明你自己DataSource已经隐式禁用了 Spring Boot 的数据源自动配置。换句话说，这不会产生任何影响：

@EnableAutoConfiguration(exclude = {DataSourceAutoConfiguration.class})

I think the problem lies in the fact that you aren't binding Hikari-specific configuration to your MySQL DataSource. You need to do something like this:

我认为问题在于您没有将 Hikari 特定的配置绑定到您的 MySQL DataSource。你需要做这样的事情：

@Bean
@Primary
@ConfigurationProperties("spring.datasource.hikari")
public DataSource mySQLDataSource() {
    return mySQLDataSourceProperties().initializeDataSourceBuilder().build();
}

This will mean that your mySQLDataSourcePropertiesare configured with general-purpose data source configuration. They then create a HikariDataSourcewhich is further configured with the Hikari-specific configuration.

这意味着您mySQLDataSourceProperties配置了通用数据源配置。然后他们创建一个HikariDataSource使用 Hikari 特定配置进一步配置的文件。

回答by ThomasW

Thank you Andy for your fast and valuable answer ! You set me on the right track. After fiddling around, I found this configuration is working for me :

感谢安迪快速而有价值的回答！你让我走上了正轨。摆弄之后，我发现这个配置对我有用：

    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource") 
  //@ConfigurationProperties("spring.datasource.hikari") can also be used, no difference
    public DataSourceProperties mySQLDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean
    @Primary
    @ConfigurationProperties("spring.datasource.hikari")
    public DataSource mySQLDataSource() {
        return mySQLDataSourceProperties().initializeDataSourceBuilder().build();
    }

    @Bean
    @ConfigurationProperties(prefix = "spring.datasource.hikari")
    public HikariConfig hikariConfig() {
        return new HikariConfig();
    }

    @Bean
    public DataSource dataSource() {
        return new HikariDataSource(hikariConfig());
    }

and I had to add these settings in the application.properties:

我必须在 application.properties 中添加这些设置：

# this is absolutely mandatory otherwise BeanInstantiationException in mySQLDataSource ! 
spring.datasource.url=${JDBC_CONNECTION_STRING}

spring.datasource.hikari.jdbc-url=${JDBC_CONNECTION_STRING}

spring.datasource.hikari.username=user
spring.datasource.hikari.password=pass