Using two datasources with different jdbc drivers with Spring Boot

Question

I followed the documentation and created some sourcecode that is quite identical to the given example. When I run my code I get this exception:

java.lang.RuntimeException: Driver org.mariadb.jdbc.Driver claims to not accept jdbcUrl, jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE

The strange thing is that two things are mixed:

the h2-jdbcUrl is treated by the mariaDB-driver
the query that my previously existing code runs it targetted against mariaDB (I have not yet written any code that used the second datasource)

These are my beans:

@Bean
@Primary
@ConfigurationProperties("datasources.first")
public DataSourceProperties firstDataSourceProperties() {
    return new DataSourceProperties();
}

@Bean
@Primary
@ConfigurationProperties("datasources.first.configuration")
public HikariDataSource firstDataSource() {
    return firstDataSourceProperties().initializeDataSourceBuilder().type(HikariDataSource.class).build();
}

@Bean
@ConfigurationProperties("datasources.second")
public DataSourceProperties secondDataSourceProperties() {
    return new DataSourceProperties();
}

@Bean
@ConfigurationProperties("datasources.second.configuration")
public HikariDataSource secondDataSource() {
    return secondDataSourceProperties().initializeDataSourceBuilder().type(HikariDataSource.class).build();
}

My application.yml looks like this:

datasources:
  first:
    jdbcUrl: jdbc:mariadb://...
    username: ...
    password: ...
    driver-class-name: org.mariadb.jdbc.Driver
  second:
    jdbcUrl: jdbc:h2:/tmp/test;DB_CLOSE_ON_EXIT=FALSE
    username: ...
    password: ...
    driver-class-name: org.h2.Driver

This is the code that causes the exception:

@Component
public class Updater {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Autowired
    Queries queries;

    public List<User> getActiveUsers() throws Exception {
        List<User> users = jdbcTemplate.query(queries.getActive(), new UserRowMapper());

        return users;
    }

In order to diagnose this further I enabled the configprops and beans endpoints and checked their output. The config looks good and the beans too:

Config (one of many):

    "firstDataSourceProperties": {
      "prefix": "datasources.first",
      "properties": {
        "password": "******",
        "initializationMode": "EMBEDDED",
        "driverClassName": "org.mariadb.jdbc.Driver",
        "generateUniqueName": false,
        "xa": {
          "properties": {}
        },
        "separator": ";",
        "platform": "all",
        "continueOnError": false,
        "username": "..."
      }

Bean (one of many):

"firstDataSource": {
          "aliases": [],
          "scope": "singleton",
          "type": "com.zaxxer.hikari.HikariDataSource",
          "resource": "...Application",
          "dependencies": [
            "firstDataSourceProperties"
          ]
        }

I use Spring Boot 2.2.6

You have to set JdbcTemplate bean with respect to data sources, Please have look this answer — Az.MaYo
@Az.MaYo Thanks a lot for this pointer, but I still receive Driver org.mariadb.jdbc.Driver claims to not accept jdbcUrl, jdbc:h2:... although your comment solved my (future ?) problem with differentiating two JdbcTemplates — Marged
show your datasource properties. it will help to identify problem — GolamMazid Sajib
"Just for fun" I made an intentional mistake and forced the use of the second datasource by connecting the wrong JdbcTemplate and had it call an incorrect SQL statement. The error returned is a org.h2.jdbc.JdbcSQLSyntaxErrorException, that means the second datasource uses the correct driver — Marged
@GolamMazidsajib You mean those from the application properties or something else ? — Marged

Marged Marged · Accepted Answer · 2020-04-15T19:20:07

This problem was caused by me not understanding the interaction between DataSourceProperties and HikariDataSource.

I found out that the jdbcUrl didn't carry the value I configured, this could be seen by the details of the firstDataSource:

"firstDataSource": {
  "prefix": "datasources.first.configuration",
  "properties": {
    "initializationFailTimeout": 1,
    "validationTimeout": 5000,
    "readOnly": false,
    "registerMbeans": false,
    "healthCheckProperties": {},
    "isolateInternalQueries": false,
    "leakDetectionThreshold": 0,
    "maxLifetime": 1800000,
    "minimumIdle": 10,
    "metricsTrackerFactory": {},
    "allowPoolSuspension": false,
    "idleTimeout": 600000,
    "dataSourceProperties": {},
    "driverClassName": "org.mariadb.jdbc.Driver",
    "jdbcUrl": "jdbc:h2:mem:testdb;DB_CLOSE_DELAY=-1;DB_CLOSE_ON_EXIT=FALSE",
    "loginTimeout": 0,
    "maximumPoolSize": 10,
    "autoCommit": true,
    "connectionTimeout": 30000,
    "username": "...",
    "poolName": "HikariPool-1"
}

As you can see the jdbcUrl addresses h2. This is caused by the fact that I set "my" intended url at datasources.first.jdbcUrl. But after that the (non-existing) value of datasources.first.configuration.jdbcUrl was read which was not configured and obviously set to a default.

After I changed my application.yml to this it worked:

datasources:
  first:
    username: ...
    password: ...
    driver-class-name: org.mariadb.jdbc.Driver
    configuration:
      jdbcUrl: jdbc:mariadb://...

I will need to decide which value to put where, the username and password could be moved one level deeper too, but that's another story ...

Using two datasources with different jdbc drivers with Spring Boot

1 Answers