Multiple database Connectivity using Spring Boot and Spring Data JPA

We will see how multiple database configuration and handling using spring boot in this article.

We are taking two databases Postgres and MySQL. We will connect these databases in a Spring Boot Application. We have to create a spring boot maven project with the below dependencies.

1. SpringBootStarter
2. SpringBootStarterWeb
3. SpringBootDataJPA
4. MySQLConnector-j
5. PostgresSQL

pom.xml

        <dependency>
 		<groupId>org.springframework.boot</groupId>
 		<artifactId>spring-boot-starter-data-jpa</artifactId>
 	</dependency>
 	<dependency>
 		<groupId>org.springframework.boot</groupId>
 		<artifactId>spring-boot-starter-web</artifactId>
 	</dependency>
 	<dependency>
 		<groupId>com.mysql</groupId>
 		<artifactId>mysql-connector-j</artifactId>
 		<scope>runtime</scope>
 	</dependency>
 	<dependency>
 		<groupId>org.postgresql</groupId>
 		<artifactId>postgresql</artifactId>
 		<scope>runtime</scope>
      </depencency>
 

Below are the required steps.

1. Configure the Database properties in application.yaml file.
2. Create Configuration for the first database and make this as primary database.
3. Create separate configuration for second database.
4. Create Separate Repository interfaces for both Postgres and MySQL
5. Using EntityManager for native sql queries.

 

1. Configure the Database properties in application.yaml file for both the databases as shown below.

application.yml

#Primary database configuration
spring:
  datasource:
    username: postgres
    password: postgres
    url :  jdbc:postgresql://localhost:5432/test
    driver-class-name: org.postgresql.Driver
    jdbcUrl: jdbc:postgresql://localhost:5432/test

#Second database configuration
mysql:
  datasource:
    username: root
    password: root
    url: jdbc:mysql://localhost:3306/test
    driver-class-name: com.mysql.cj.jdbc.Driver
    jdbcUrl: jdbc:mysql://localhost:3306/test									

For the Postgres spring specific data source properties being used. So Spring will auto configure the data source, EntityManageFactory and TransactionManager.
However, we have to override these beans by creating the configuration as shown below (Point 2).  
For MySQL we used custom properties.

2. Create Configuration for the first database and make this as primary database.

@Configuration
@EnableJpaRepositories(basePackages = "com.example.multipledb.postgres.repository", entityManagerFactoryRef = "postgresFactory",
transactionManagerRef = "postgresTxnMgr")
public class PrimaryDBConfiguration {

    @Bean("postgresDS")
    @Primary
    @ConfigurationProperties(prefix = "spring.datasource")
    DataSource dataSource(){
        return new HikariDataSource();
    }


    @Bean("postgresFactory")
    @Primary
    public LocalContainerEntityManagerFactoryBean localEntityManagerFactoryBean(){
        LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();

        Map<String, Object> jpaProperties = new HashMap<>();
        jpaProperties.put("hibernate.dialect", PostgreSQL95Dialect.class.getName());
        jpaProperties.put("hibernate.hbm2ddl.auto", "update");
        factoryBean.setJpaPropertyMap(jpaProperties);
        factoryBean.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
        factoryBean.setPersistenceUnitName("postgres");
        factoryBean.setPackagesToScan(Claims.class.getPackageName());
        factoryBean.setDataSource(dataSource());

        return factoryBean;
    }

    @Bean("postgresTxnMgr")
    @Primary
    public PlatformTransactionManager postgresTransactionManager(){

        JpaTransactionManager txnMgr = new JpaTransactionManager();
        txnMgr.setEntityManagerFactory(localEntityManagerFactoryBean().getObject());
        return txnMgr;
    }

}

We have to use @Configuration for this class as this class has spring bean configurations.

@EnableJpaRepositories is used to configure entityManager and transactionManagers to the specific base package “com.example.multipledb.postgres.repository”.

Create dataSource, EntityManagerFactory and TransactionManager beans and annotated them with @Primary annotation.
Also mentioned the Entity package to scan the postgres relates entity pojos.
          factoryBean.setPackagesToScan(“com.example.multipledb.postgres.model”);

 

3. Create separate configuration for second database.

We have to create separate configuration for the mysql database related data source, EntityManagerFactory and TransactionManager beans.

Use @Configuration, @EnableJpaRepositories and configure entityManager, transactionManager to the base package “com.example.multipledb.mysql.repository”

set the mysql related entity pojos package to factory bean to scan and identify the entities.
factoryBean.setPackagesToScan(“com.example.multipledb.mysql.model”);

package com.example.multipledb;
import ......
@Configuration
@EnableJpaRepositories(basePackages = "com.example.multipledb.mysql.repository",
        entityManagerFactoryRef = "mysqlFactory",
        transactionManagerRef = "mysqlTxnMgr")
public class SecondaryDBConfiguration {

    @Bean(name = "mysqlDS")
    @ConfigurationProperties(prefix = "mysql.datasource")
    DataSource dataSource(){
        return new HikariDataSource();
    }

    @Bean("mysqlFactory")
    public LocalContainerEntityManagerFactoryBean localEntityManagerFactoryBean(){
        LocalContainerEntityManagerFactoryBean factoryBean = new LocalContainerEntityManagerFactoryBean();

        Map<String, Object> jpaProperties = new HashMap<>();
        jpaProperties.put("hibernate.dialect", MySQL8Dialect.class.getName());
        jpaProperties.put("hibernate.hbm2ddl.auto", "update");
        factoryBean.setJpaPropertyMap(jpaProperties);
        factoryBean.setJpaVendorAdapter(new HibernateJpaVendorAdapter());
        factoryBean.setPersistenceUnitName("mysql");
        factoryBean.setPackagesToScan("com.example.multipledb.mysql.repository");
        factoryBean.setDataSource(dataSource());

        return factoryBean;
    }

    @Bean("mysqlTxnMgr")
    public PlatformTransactionManager mysqlTransactionManager(){

        JpaTransactionManager txnMgr = new JpaTransactionManager();
        txnMgr.setEntityManagerFactory(localEntityManagerFactoryBean().getObject());
        return txnMgr;
    }
}

 

4. Create Separate Repository interfaces for both Postgres and MySQL

 

package com.example.multipledb.postgres.repository;
import com.example.multipledb.postgres.model.Claims;
import org.springframework.data.jpa.repository.JpaRepository;

public interface ClaimsRepository extends JpaRepository<Claims, Integer> {
}

------------------------

package com.example.multipledb.mysql.repository;
import com.example.multipledb.mysql.model.ClaimsInfo;
import org.springframework.data.jpa.repository.JpaRepository;

public interface ClaimsInfoRepository extends JpaRepository<ClaimsInfo, Integer> {
}

These repositories can be used for general CRUD operations along with other Entity Field Specific select querying without any specific implementation. These repository interfaces can be used to configure Native SQL queries and use this repository to execute.

5. Using EntityManager for native sql queries.

 

To work with more complex Native Queries, Stored Procedures or to leverage the use Query or Criteria interfaces we can use EntityManager.

To use EntityManager we have to autowire the EntityManager  with  @PersistenceContext annotation by specifying the unitName  specific to the database @PersistenceContext(unitName=”<SpecificDatabaseUnitName>”).

    @PersistenceContext(unitName = "postgres")
    EntityManager claimsManager;

    public int getClaimsCountByRate(){

        int count = claimsManager.createNativeQuery("SELECT COUNT(*) FROM CLAIMS where RATE='EMP' ", Integer.class).getFirstResult();

        return count;

    }
   
-----------------------

 @PersistenceContext(unitName = "mysql")
    EntityManager entityManager;

    public int getClaimsInfoCount(){

        int count = ((BigInteger)entityManager.createNativeQuery("SELECT COUNT(*) FROM CLAIMSINFO WHERE TENANT = '123' ").getSingleResult()).intValue();

        return count;
    }

 

Conclusion

Connecting multiple databases in an application is a typical use case in mostly monolithic applications. But not preferred in Microservice architecture. However, there are some use cases like a single application fetching data from one schema and updating or inserting it into another schema within the same database server. In this case the same approach used here will by creating multiple repositories specific to the schema and models. We can manage the multiple database configuration in spring boot mainly by creating separate configuration, repository interfaces and EntityFactory(to handle complex native queries) to each database or schema.