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

Question

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).

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

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

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"

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

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.

[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 :

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 ?

Andy Wilkinson · Answer 1 · 2017-06-05T22:19:32.920

20

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

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

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.

edited Jun 05 '17 at 22:19

answered Jun 05 '17 at 16:39

Andy Wilkinson

85,432
19
215
204

Could you please guide me here: https://stackoverflow.com/questions/62510899/spring-batch-create-two-datasources-and-how-to-customized-to-use-other-propert? – Pra_A Jun 22 '20 at 09:31
Great answer and thanks for your work on Spring Boot/REST. Any chance you could kindly include a link to where this is documented in Spring Boot? – xlm Sep 30 '20 at 00:39

score 8 · Answer 2 · answered Jun 05 '17 at 18:42

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:

# 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

Isn't the way you call `hikariConfig()` and `mySQLDataSourceProperties()` @Bean methods directly broken? I don't think Spring is smart enough to proxy those calls, assume you get an empty not-configured-by-properties instance that way? However, those @Bean methods can take parameters, those are initialized beans. See e.g. https://www.logicbig.com/tutorials/spring-framework/spring-core/javaconfig-methods-inter-dependency.html — Stefan L, Feb 11 '21 at 23:39

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

2 Answers2

Linked