not able to connect to two databases on different hosts using springboot

Question

I am not able to connect to two databases residing on different hosts. I have defined primary and secondary data sources configuration classes. Crossed checked my credentials. Tested OK with telnet command for reach-ability. But some how I was able to connect to those two databases from my localhost environment. I suppose I was able to connect to those DBs through localhost because host was same. But don't know what's the issue on production. Here are my files.

application.properties

server.port=9393
server.sessionTimeout = 30

spring.profiles.active = dev
spring.jackson.serialization.fail-on-empty-beans = false
spring.mvc.throw-exception-if-no-handler-found=true

######production#################
server.address = [some IP]
spring.data.mongodb.host=[some IP]
spring.data.mongodb.port=27017
spring.data.mongodb.database=MongoLog
#########################

#######localhost###########
#spring.data.mongodb.host=localhost
#spring.data.mongodb.port=27017
#spring.data.mongodb.database=MongoLog
###########################

#production DB-1
spring.datasource.jdbc-url=jdbc:mysql://[some IP-1]:3306/proDB1?useSSL=false&autoReconnect=true&useUnicode=yes&characterEncoding=UTF-8
spring.datasource.username=root
spring.datasource.password=secret@123

#localhost DB1--->
#spring.datasource.jdbc-url=jdbc:mysql://localhost:3306/proDB1?autoReconnect=true&useUnicode=yes&characterEncoding=UTF-8
#spring.datasource.username=root
#spring.datasource.password=root

#production DB-2
spring.seconddatasource.jdbc-url=jdbc:mysql://[some IP-2]:3306/proDB2?autoReconnect=true&useUnicode=yes&characterEncoding=UTF-8
spring.seconddatasource.username=root
spring.seconddatasource.password=secret@321

#localhost DB2--->
#spring.seconddatasource.jdbc-url=jdbc:mysql://localhost:3306/proDB2?autoReconnect=true&useUnicode=yes&characterEncoding=UTF-8
#spring.seconddatasource.username=root
#spring.seconddatasource.password=root

spring.jpa.database=default
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5Dialect

# TOMCAT
tomcat.accessLogEnabled=false
tomcat.protocolHeader=x-forwarded-proto
tomcat.remoteIpHeader=x-forwarded-for
tomcat.backgroundProcessorDelay=30

primary datasource class

package com.product.dailyReport;
import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
          entityManagerFactoryRef = "primaryEntityManagerFactory",
          transactionManagerRef = "primaryTransactionManager",
          basePackages = { "com.product.dailyReport.repository" }
          
        
        )
public class PrimaryDBConfig {
    @Bean(name="primaryDataSource")
    @Primary
    @ConfigurationProperties(prefix="spring.datasource")
    public DataSource primaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean(name = "primaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean primaryEntityManagerFactory(EntityManagerFactoryBuilder builder,
            @Qualifier("primaryDataSource") DataSource primaryDataSource) {
        return builder
                .dataSource(primaryDataSource)
                .packages("com.product.dailyReport")
                .build();
    }

    @Bean(name = "primaryTransactionManager")
    public PlatformTransactionManager primaryTransactionManager(
            @Qualifier("primaryEntityManagerFactory") EntityManagerFactory primaryEntityManagerFactory) {
        return new JpaTransactionManager(primaryEntityManagerFactory);
    }
}

secondary datasource class

package com.product.dailyReport;
import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
  entityManagerFactoryRef = "secondaryEntityManagerFactory",
  transactionManagerRef = "secondaryTransactionManager",
  basePackages = { "com.product.dailyReport.colombo.repository" }
)
public class SecondaryDBConfig {
    @Bean(name="secondaryDataSource")
    @ConfigurationProperties(prefix="spring.seconddatasource")
    public DataSource secondaryDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "secondaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean secondaryEntityManagerFactory(EntityManagerFactoryBuilder builder,
            @Qualifier("secondaryDataSource") DataSource secondaryDataSource) {
        return builder
                .dataSource(secondaryDataSource)
                .packages("com.product.dailyReport")
                .build();
    }

    @Bean(name = "secondaryTransactionManager")
    public PlatformTransactionManager secondaryTransactionManager(
            @Qualifier("secondaryEntityManagerFactory") EntityManagerFactory secondaryEntityManagerFactory) {
        return new JpaTransactionManager(secondaryEntityManagerFactory);
    }
}

pom.xml

<?xml version="1.0" encoding="UTF-8"?>
<project xmlns="http://maven.apache.org/POM/4.0.0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 https://maven.apache.org/xsd/maven-4.0.0.xsd">
    <modelVersion>4.0.0</modelVersion>
    <parent>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-parent</artifactId>
        <version>2.5.4</version>
        <relativePath/> <!-- lookup parent from repository -->
    </parent>
    <groupId>com.crbt</groupId>
    <artifactId>DailyReport</artifactId>
    <version>0.0.1-SNAPSHOT</version>
    <name>DailyReport</name>
    <description>Demo project for Spring Boot</description>

    <properties>
        <java.version>1.8</java.version>
    </properties>

    <dependencies>
        <!-- Spring boot dependencies -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter</artifactId>
        </dependency>
        
        <!-- <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-test</artifactId>
            <scope>test</scope>
        </dependency> -->
<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-mongodb</artifactId>
        </dependency>
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-web</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
        </dependency>

        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
        </dependency>

        <!-- Spring boot development tools -->
        <dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-devtools</artifactId>
            <optional>true</optional>
        </dependency>

        <dependency>
            <groupId>commons-fileupload</groupId>
            <artifactId>commons-fileupload</artifactId>
            <version>1.3.3</version>
        </dependency>

        

        <!-- MySQL database connector -->
        <dependency>
            <groupId>mysql</groupId>
            <artifactId>mysql-connector-java</artifactId>
            <version>5.1.31</version>
        </dependency>
        
        <!-- https://mvnrepository.com/artifact/org.mariadb.jdbc/mariadb-java-client 
<dependency>
    <groupId>org.mariadb.jdbc</groupId>
    <artifactId>mariadb-java-client</artifactId>
    <version>2.3.0</version>
</dependency>
        -->

        <!-- https://mvnrepository.com/artifact/commons-dbcp/commons-dbcp -->
        <dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
            <version>1.2.2</version>
        </dependency>
        <!-- https://mvnrepository.com/artifact/commons-pool/commons-pool -->
        <dependency>
            <groupId>commons-pool</groupId>
            <artifactId>commons-pool</artifactId>
            <version>1.5.7</version>
        </dependency>

        <!-- Java EE based dependency injection -->
        <dependency>
            <groupId>javax.inject</groupId>
            <artifactId>javax.inject</artifactId>
            <version>1</version>
        </dependency>

    
        <!-- EMail validator -->
        <dependency>
            <groupId>commons-validator</groupId>
            <artifactId>commons-validator</artifactId>
            <version>1.4.1</version>
        </dependency>
        <!-- JSON/XML tool -->
        <dependency>
            <groupId>com.fasterxml.jackson.dataformat</groupId>
            <artifactId>jackson-dataformat-xml</artifactId>
        </dependency>

        <!-- https://mvnrepository.com/artifact/com.googlecode.json-simple/json-simple -->
        <dependency>
            <groupId>com.googlecode.json-simple</groupId>
            <artifactId>json-simple</artifactId>
            <version>1.1.1</version>
        </dependency>

<!-- https://mvnrepository.com/artifact/com.squareup.okhttp3/okhttp -->
<dependency>
    <groupId>com.squareup.okhttp3</groupId>
    <artifactId>okhttp</artifactId>
    <version>3.7.0</version>
</dependency>


<!-- https://mvnrepository.com/artifact/com.squareup.okio/okio -->
<dependency>
    <groupId>com.squareup.okio</groupId>
    <artifactId>okio</artifactId>
    <version>1.12.0</version>
</dependency>



        <!-- https://mvnrepository.com/artifact/commons-codec/commons-codec -->
        <dependency>
            <groupId>commons-codec</groupId>
            <artifactId>commons-codec</artifactId>
        </dependency>

        <!-- https://mvnrepository.com/artifact/commons-lang/commons-lang -->
        <dependency>
            <groupId>org.apache.commons</groupId>
            <artifactId>commons-lang3</artifactId>
            <version>3.6</version>
        </dependency>
        
        
        
        
                        <!-- API, java.xml.bind module -->
<dependency>
    <groupId>jakarta.xml.bind</groupId>
    <artifactId>jakarta.xml.bind-api</artifactId>
    <version>2.3.2</version>
</dependency>

<!-- Runtime, com.sun.xml.bind module -->
<dependency>
    <groupId>org.glassfish.jaxb</groupId>
    <artifactId>jaxb-runtime</artifactId>
    <version>2.3.2</version>
</dependency>
        
        
    </dependencies>

    <build>
        <plugins>
            <plugin>
                <groupId>org.springframework.boot</groupId>
                <artifactId>spring-boot-maven-plugin</artifactId>
            </plugin>
        </plugins>
    </build>

    <repositories>
        <repository>
            <id>spring-milestones</id>
            <name>Spring Milestones</name>
            <url>https://repo.spring.io/milestone</url>
        </repository>
        <repository>
            <id>spring-snapshots</id>
            <name>Spring Snapshots</name>
            <url>https://repo.spring.io/snapshot</url>
            <snapshots>
                <enabled>true</enabled>
            </snapshots>
        </repository>
    </repositories>
    <pluginRepositories>
        <pluginRepository>
            <id>spring-milestones</id>
            <name>Spring Milestones</name>
            <url>https://repo.spring.io/milestone</url>
        </pluginRepository>
        <pluginRepository>
            <id>spring-snapshots</id>
            <name>Spring Snapshots</name>
            <url>https://repo.spring.io/snapshot</url>
            <snapshots>
                <enabled>true</enabled>
            </snapshots>
        </pluginRepository>
    </pluginRepositories>

</project>

all is working fine with localhost environment i.e I was able to connect to two databases from localhost using above settings but on production environment I am getting com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up. Below is the full stack, it has something related to hikari.. don't know what it is :

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up.
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_144]
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_144]
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_144]
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_144]
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:408) ~[mysql-connector-java-5.1.31.jar!/:na]
        at com.mysql.jdbc.Util.getInstance(Util.java:383) ~[mysql-connector-java-5.1.31.jar!/:na]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1023) ~[mysql-connector-java-5.1.31.jar!/:na]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:997) ~[mysql-connector-java-5.1.31.jar!/:na]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:983) ~[mysql-connector-java-5.1.31.jar!/:na]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:928) ~[mysql-connector-java-5.1.31.jar!/:na]
        at com.mysql.jdbc.ConnectionImpl.connectWithRetries(ConnectionImpl.java:2407) ~[mysql-connector-java-5.1.31.jar!/:na]
        at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2328) ~[mysql-connector-java-5.1.31.jar!/:na]
        at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:832) ~[mysql-connector-java-5.1.31.jar!/:na]
        at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:46) ~[mysql-connector-java-5.1.31.jar!/:na]
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_144]
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_144]
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_144]
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_144]
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:408) ~[mysql-connector-java-5.1.31.jar!/:na]
        at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:417) ~[mysql-connector-java-5.1.31.jar!/:na]
        at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:344) ~[mysql-connector-java-5.1.31.jar!/:na]
        at com.zaxxer.hikari.util.DriverDataSource.getConnection(DriverDataSource.java:138) ~[HikariCP-4.0.3.jar!/:na]
        at com.zaxxer.hikari.pool.PoolBase.newConnection(PoolBase.java:364) ~[HikariCP-4.0.3.jar!/:na]
        at com.zaxxer.hikari.pool.PoolBase.newPoolEntry(PoolBase.java:206) ~[HikariCP-4.0.3.jar!/:na]
        at com.zaxxer.hikari.pool.HikariPool.createPoolEntry(HikariPool.java:476) [HikariCP-4.0.3.jar!/:na]
        at com.zaxxer.hikari.pool.HikariPool.checkFailFast(HikariPool.java:561) [HikariCP-4.0.3.jar!/:na]
        at com.zaxxer.hikari.pool.HikariPool.<init>(HikariPool.java:115) [HikariCP-4.0.3.jar!/:na]
        at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:112) [HikariCP-4.0.3.jar!/:na]
        at org.hibernate.engine.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:122) [hibernate-core-5.4.32.Final.jar!/:5.4.32.Final]

pringi · Answer 1 · 2022-02-18T12:47:13.523

0

Add the following to your application.properties:

spring.datasource.url=jdbc:mysql://[some IP-1]:3306/proDB1?useSSL=false&autoReconnect=true&useUnicode=yes&characterEncoding=UTF-8
spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
spring.seconddatasource.url=jdbc:mysql://[some IP-2]:3306/proDB2?autoReconnect=true&useUnicode=yes&characterEncoding=UTF-8
spring.seconddatasource.driver-class-name=com.mysql.cj.jdbc.Driver

In practice the same value that is already in "jdbc-url".

It is related how hiraki and drivers read the database url. More information here: How to use HikariCP in Spring Boot with two datasources in conjunction with Flyway

edited Feb 18 '22 at 12:47

answered Feb 18 '22 at 09:53

pringi

3,987
5
35
45

I changed that. But now it's saying `java.lang.IllegalArgumentException: dataSource or dataSourceClassName or jdbcUrl is required. at com.zaxxer.hikari.HikariConfig.validate(HikariConfig.java:1029) ~[HikariCP-4.0.3.jar!/:na] at com.zaxxer.hikari.HikariDataSource.getConnection(HikariDataSource.java:109) ~[HikariCP-4.0.3.jar!/:na] ` – J. Doe Feb 18 '22 at 12:41
I've updated the response to include the driver – pringi Feb 18 '22 at 12:48
Ok I included driver class for both datasources as per your answer. Now I am getting `Failed to bind properties under 'spring.datasource' to javax.sql.DataSource: Property: spring.datasource.driver-class-name Value: com.mysql.cj.jdbc.Driver Origin: class path resource [application.properties] from DailyReport-0.0.1-SNAPSHOT.jar - 44:37 Reason: java.lang.RuntimeException: Failed to load driver class com.mysql.cj.jdbc.Driver in either of HikariConfig class loader or Thread context classloader Action: Update your application's configuration` – J. Doe Feb 18 '22 at 13:11
Use "com.mysql.jdbc.Driver" instead. You are using version 5.1 of mysql drivers. – pringi Feb 18 '22 at 13:15
ok do I also need to change the property key from `spring.datasource.driver-class-name` to `spring.datasource.driverClassName` – J. Doe Feb 18 '22 at 13:22
I heard lot of good things about spring boot capabilities. But sorry to say this feature really sucks. It has to be taken care of internally using minimum annotations. In todays times we often have to work with multiple DBs. – J. Doe Feb 18 '22 at 13:28
`java.lang.IllegalArgumentException: jdbcUrl is required with driverClassName.` I think I have to do this task using legacy jdbc connection (class.forName), old school way. By getting connection objects and closing them etc. – J. Doe Feb 18 '22 at 13:35
You are getting strange errors. Temporarily disable one of the datasources and check if you can run with only one without errors. Use properties in camel case also: spring.datasource.jdbcUrl, spring.datasource.driverClassName – pringi Feb 18 '22 at 13:58

not able to connect to two databases on different hosts using springboot

1 Answers1