如何在Spring Boot中结合两个数据源和Flyway使用HikariCP [英] How to use HikariCP in Spring Boot with two datasources in conjunction with Flyway
问题描述
我想在Spring引导应用程序中将HikariCP用作JDBC连接池.我有两个数据源(MySQL数据库作为主要数据库,并通过Hibernate访问这些数据,另外还有一个Oracle数据库,用于通过JDBCTemplate读取其他数据).
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).
我将MySQL数据源设置为主豆:
I set the MySQL datasource as primary 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);
}
,然后将以下配置放在我的application.properties中:
and I put the following configurations in my 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
不幸的是,这些HikariCP配置没有被读取:
Unforuntately, these HikariCP configurations are not being read :
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"
创建HikariCP Bean并停用DataSource自动配置并删除"spring.datasource":
Creating the HikariCP beans and deactivating the DataSource autoconfiguration and removing "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());
}
解决了我的问题:
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"
但是随后Flyway显示了一些之前未显示的奇怪警告,因此我必须在运行Spring应用程序之前手动创建数据库模式,即:创建模式不再起作用.
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.
[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)
我的Flyway SQL脚本是普通的DDL脚本:
My Flyway SQL scripts are plain DDL scripts :
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,
我认为禁用自动数据源配置不是最佳解决方案,因为Flyway会停止创建架构并显示警告.还有其他解决方法吗?
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 ?
推荐答案
声明自己的DataSource
将已经隐式禁用Spring Boot的数据源自动配置.换句话说,这不会起作用:
Declaring your own DataSource
will already have implicity disabled Spring Boot's auto-configuration of a data source. In other words this won't be having any effect:
@EnableAutoConfiguration(exclude = {DataSourceAutoConfiguration.class})
我认为问题在于您没有将Hikari特定的配置绑定到MySQL DataSource
.您需要执行以下操作:
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:
@Bean
@Primary
@ConfigurationProperties("spring.datasource.hikari")
public DataSource mySQLDataSource() {
return mySQLDataSourceProperties().initializeDataSourceBuilder().build();
}
这意味着您的mySQLDataSourceProperties
已配置了通用数据源配置.然后,他们创建一个HikariDataSource
,并进一步使用Hikari特定的配置进行配置.
This will mean that your mySQLDataSourceProperties
are configured with general-purpose data source configuration. They then create a HikariDataSource
which is further configured with the Hikari-specific configuration.
这篇关于如何在Spring Boot中结合两个数据源和Flyway使用HikariCP的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!