springboot-how to solve 'dataSource or dataSourceClassName or jdbcUrl is required' when connect to multiple datasources using spring boot
Problem
When we want to access multiple datasources using spring boot jdbcTemplate like this:
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:
- The spring-boot-starter-data-jdbc
- The mysql jdbc driver
- 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.