Problem

When we want to access multiple datasources using spring boot jdbcTemplate like this:

image-20201122203815235

Sometimes , we got this error message:


> Task :app4:bootRun FAILED

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.3.2.RELEASE)

2020-11-22 18:29:05.327  INFO 4728 --- [           main] com.bswen.app4.Main                      : The following profiles are active: dev
2020-11-22 18:29:05.818  INFO 4728 --- [           main] com.bswen.app4.Main                      : Started Main in 0.783 seconds (JVM running for 1.148)
2020-11-22 18:29:05.820  INFO 4728 --- [           main] com.bswen.app4.MultipleDBJDBCRunner      : Querying for users from mysql:
2020-11-22 18:29:05.824 ERROR 4728 --- [           main] com.zaxxer.hikari.HikariConfig           : HikariPool-1 - dataSource or dataSourceClassName or jdbcUrl is required.
2020-11-22 18:29:05.827  INFO 4728 --- [           main] ConditionEvaluationReportLoggingListener : 

Error starting ApplicationContext. To display the conditions report re-run your application with 'debug' enabled.
2020-11-22 18:29:05.837 ERROR 4728 --- [           main] o.s.boot.SpringApplication               : Application run failed

java.lang.IllegalStateException: Failed to execute CommandLineRunner
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:798) [spring-boot-2.3.2.RELEASE.jar:2.3.2.RELEASE]
    at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:779) [spring-boot-2.3.2.RELEASE.jar:2.3.2.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:322) [spring-boot-2.3.2.RELEASE.jar:2.3.2.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1237) [spring-boot-2.3.2.RELEASE.jar:2.3.2.RELEASE]
    at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226) [spring-boot-2.3.2.RELEASE.jar:2.3.2.RELEASE]
    at com.bswen.app4.Main.main(Main.java:9) [main/:na]
Caused by: java.lang.IllegalArgumentException: dataSource or dataSourceClassName or jdbcUrl is required.
    at com.zaxxer.hikari.HikariConfig.validate(HikariConfig.java:1004) ~[HikariCP-3.4.5.jar:na]
Caused by: java.lang.IllegalArgumentException: dataSource or dataSourceClassName or jdbcUrl is required.

    at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:109) ~[HikariCP-3.4.5.jar:na]
    at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:158) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:116) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:79) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:612) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:669) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:700) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:712) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.jdbc.core.JdbcTemplate.query(JdbcTemplate.java:763) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at com.bswen.app4.MultipleDBJDBCRunner.run(MultipleDBJDBCRunner.java:22) ~[main/:na]
    at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:795) [spring-boot-2.3.2.RELEASE.jar:2.3.2.RELEASE]
    ... 5 common frames omitted


Execution failed for task ':app4:bootRun'.
> Process 'command '/Library/Java/JavaVirtualMachines/jdk1.8.0_121.jdk/Contents/Home/bin/java'' finished with non-zero exit value 1

As the above error stacktrace shown, the main error is thrown by hikari datasource class :

Caused by: java.lang.IllegalArgumentException: dataSource or dataSourceClassName or jdbcUrl is required.
 at com.zaxxer.hikari.HikariConfig.validate(HikariConfig.java:1004) ~[HikariCP-3.4.5.jar:na]
Caused by: java.lang.IllegalArgumentException: dataSource or dataSourceClassName or jdbcUrl is required.

Environment

  • Postgresql 10.15
  • MySQL 5.7
  • Spring boot 2.3
  • JDK 1.8

Debug

Debug #1: The build.gradle of the app

This app is built by the gradle, let’s see the build.gradle file, it’s part of a root project that contains multiple submodules, you can reference this article to see how it works :

plugins {
    id 'org.springframework.boot'
    id 'io.spring.dependency-management'
    id 'java'
}

group 'com.bswen.app4'
version '1.0-SNAPSHOT'

sourceCompatibility = 1.8

repositories {
    mavenCentral()
}

dependencies {
    implementation 'org.springframework.boot:spring-boot-starter-data-jdbc'
    runtimeOnly 'mysql:mysql-connector-java'
    runtimeOnly 'org.postgresql:postgresql'
    testImplementation('org.springframework.boot:spring-boot-starter-test') {
        exclude group: 'org.junit.vintage', module: 'junit-vintage-engine'
    }
}

test {
    useJUnitPlatform()
}

It mainly depends on:

  1. The spring-boot-starter-data-jdbc
  2. The mysql jdbc driver
  3. The postgresql jdbc driver

Debug #2: The MultipleDBConfig class used by spring boot to connect to multiple datasources

If we want to connect to multiple datasources using spring boot, we should provide a @Configuration class to tell spring boot where to find the datasources, just as follows:

package com.bswen.app4;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;

@Configuration
public class MultipleDBConfig {
    @Bean(name = "mysqlDb")
    @ConfigurationProperties(prefix = "spring.ds-mysql")
    public DataSource mysqlDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "mysqlJdbcTemplate")
    public JdbcTemplate jdbcTemplate(@Qualifier("mysqlDb") DataSource dsMySQL) {
        return new JdbcTemplate(dsMySQL);
    }

    @Bean(name = "postgresDb")
    @ConfigurationProperties(prefix = "spring.ds-postgresql")
    public DataSource postgresDataSource() {
        return  DataSourceBuilder.create().build();
    }

    @Bean(name = "postgresJdbcTemplate")
    public JdbcTemplate postgresJdbcTemplate(@Qualifier("postgresDb")
                                                     DataSource dsPostgres) {
        return new JdbcTemplate(dsPostgres);
    }
}

Debug #3: The application.properties that defines the real connection information of those datasources

The above MultipleDBConfig has defined where to find the datasources, we also should define the real connection properties(host/port/username/password) that spring boot jdbc template used to connect.

spring.ds-postgresql.url =jdbc:postgresql://47.1.2.7:5432/bswendb
spring.ds-postgresql.username =bswen
spring.ds-postgresql.password =123456

spring.ds-mysql.url = jdbc:mysql://47.1.2.7:3306/bswen2
spring.ds-mysql.username = bswen
spring.ds-mysql.password = 123456

Debug #4: The real jdbc client that query the data from multiple datasources

package com.bswen.app4;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.CommandLineRunner;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Component;

@Component
public class MultipleDBJDBCRunner implements CommandLineRunner {
    private static final Logger log = LoggerFactory.getLogger(MultipleDBJDBCRunner.class);
    @Autowired @Qualifier("mysqlJdbcTemplate")
    JdbcTemplate mysqlJdbcTemplate;
    @Autowired @Qualifier("postgresJdbcTemplate")
    JdbcTemplate postgresJdbcTemplate;

    @Override
    public void run(String... args) throws Exception {
        log.info("Querying for users from mysql:");
        mysqlJdbcTemplate.query(
                "SELECT id, name, emailid, phoneno,location FROM usermaster", new Object[] {  },
                (rs, rowNum) -> new UserMaster(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("emailid"),
                        rs.getString("phoneno"),
                        rs.getString("location"))
        ).forEach(customer -> log.info(customer.toString()));

        log.info("Querying for users from postgresql:");
        postgresJdbcTemplate.query(
                "SELECT id, name, emailid, phoneno,location FROM usermaster", new Object[] {  },
                (rs, rowNum) -> new UserMaster(
                        rs.getInt("id"),
                        rs.getString("name"),
                        rs.getString("emailid"),
                        rs.getString("phoneno"),
                        rs.getString("location"))
        ).forEach(customer -> log.info(customer.toString()));
    }
}

Reason

According to this document:

However, there is a catch. Because the actual type of the connection pool is not exposed, no keys are generated in the metadata for your custom DataSource and no completion is available in your IDE (because the DataSource interface exposes no properties). Also, if you happen to have Hikari on the classpath, this basic setup does not work, because Hikari has no url property (but does have a jdbcUrl property). In that case, you must rewrite your configuration as follows:

If you are using Hikari connection pool , then you should define the connection properties as follows:

app.datasource.jdbc-url=jdbc:mysql://localhost/test
app.datasource.username=dbuser
app.datasource.password=dbpass
app.datasource.pool-size=30

Solution

Edit your application.properties like this:

spring.ds-postgresql.jdbc-url =jdbc:postgresql://47.1.2.7:5432/bswendb
spring.ds-postgresql.username =bswen
spring.ds-postgresql.password =123456

spring.ds-mysql.jdbc-url = jdbc:mysql://47.1.2.7:3306/bswen2
spring.ds-mysql.username = bswen
spring.ds-mysql.password = 123456

Then re-run our spring boot application:

18:37:27: Executing task 'bootRun'...

> Task :app4:compileJava
> Task :app4:processResources UP-TO-DATE
> Task :app4:classes

> Task :app4:bootRun

  .   ____          _            __ _ _
 /\\ / ___'_ __ _ _(_)_ __  __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
 \\/  ___)| |_)| | | | | || (_| |  ) ) ) )
  '  |____| .__|_| |_|_| |_\__, | / / / /
 =========|_|==============|___/=/_/_/_/
 :: Spring Boot ::        (v2.3.2.RELEASE)

2020-11-22 18:37:29.181  INFO 5105 --- [           main] com.bswen.app4.Main                      : The following profiles are active: dev
2020-11-22 18:37:29.665  INFO 5105 --- [           main] com.bswen.app4.Main                      : Started Main in 0.797 seconds (JVM running for 1.134)
2020-11-22 18:37:29.666  INFO 5105 --- [           main] com.bswen.app4.MultipleDBJDBCRunner      : Querying for users from mysql:
2020-11-22 18:37:29.670  INFO 5105 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
2020-11-22 18:37:30.244  INFO 5105 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
2020-11-22 18:37:30.286  INFO 5105 --- [           main] com.bswen.app4.MultipleDBJDBCRunner      : UserMaster{id=1, name='name_mysql', emailid='[email protected]', photono='1234567890', location='IN'}
2020-11-22 18:37:30.286  INFO 5105 --- [           main] com.bswen.app4.MultipleDBJDBCRunner      : Querying for users from postgresql:
2020-11-22 18:37:30.286  INFO 5105 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-2 - Starting...
2020-11-22 18:37:30.557  INFO 5105 --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-2 - Start completed.
2020-11-22 18:37:30.573  INFO 5105 --- [           main] com.bswen.app4.MultipleDBJDBCRunner      : UserMaster{id=1, name='name_postgres', emailid='[email protected]', photono='1234567890', location='IN'}
2020-11-22 18:37:30.576  INFO 5105 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-2 - Shutdown initiated...
2020-11-22 18:37:30.579  INFO 5105 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-2 - Shutdown completed.
2020-11-22 18:37:30.579  INFO 5105 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
2020-11-22 18:37:30.584  INFO 5105 --- [extShutdownHook] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.

BUILD SUCCESSFUL in 2s
3 actionable tasks: 2 executed, 1 up-to-date
18:37:30: Task execution finished 'bootRun'.

All the code is upload to github, you can download the example code here.