>1 RDBMS in Spring Data JPA

This document deals with building the backend application that uses Spring Data JPA with multiple relational databases.
For an example we will connect to MySQL + MSSQL database.

Main task here is to seperate properties and configurations for all the multiple databases that have to integrated.

Other JPA layers in code remain the same as for single integration. [ Repository + Entity]

[ Point to remember : Define these in different packages for different databases as we would need them when defining configs. ]

For specific code refer this.

Sample Configurations in application properties

spring.datasource.url=jdbc:mysql://127.0.0.1/heimdall_db?useSSL=false
spring.datasource.username=root
spring.datasource.password=pranjal
spring.datasource.driverClassName=com.mysql.cj.jdbc.Driver

##SQL Server
sqlserver.datasource.url=jdbc:sqlserver://localhost;databaseName=jpa_test
sqlserver.datasource.username=sa
sqlserver.datasource.password=reallyStrongPwd123
sqlserver.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver

spring.jpa.database=default

Don't define other hibernate configurations specific to database here.

Defining Separate Config Classes for all the databases

package com.sma.backend.multidb.config;

import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.jdbc.DataSourceProperties;
import org.springframework.boot.context.properties.ConfigurationProperties;
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;


import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "sqlServerEntityManagerFactory",
        transactionManagerRef = "sqlServerTransactionManager",
        basePackages = "com.sma.backend.multidb.database.sqlserver.repository")
public class SqlServerConfig {

    @Bean
    @ConfigurationProperties(prefix = "sqlserver.datasource")
    public DataSourceProperties sqlServerDataSourceProperties() {
        return new DataSourceProperties();
    }
    @Bean
    public DataSource sqlServerDataSource(@Qualifier("sqlServerDataSourceProperties") DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }

    @Bean(name = "sqlServerEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean sqlServerEntityManagerFactory(@Qualifier("sqlServerDataSource") DataSource sqlServerDataSource, EntityManagerFactoryBuilder builder) {

        return builder.dataSource(sqlServerDataSource)
                .packages("com.sma.backend.multidb.database.sqlserver.domain")
                .persistenceUnit("sqlserver")
                .build();

    }

    @Bean
    public PlatformTransactionManager sqlServerTransactionManager(@Qualifier("sqlServerEntityManagerFactory")
                                                                              EntityManagerFactory factory) {
        return new JpaTransactionManager(factory);
    }
}

MySqlConfig

package com.sma.backend.multidb.config;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "mysqlEntityManagerFactory", transactionManagerRef = "mysqlTransactionManager", basePackages = {"com.sma.backend.multidb.database.mysql.repository"})

public class MySqlConfig {

    @Primary
    @Bean
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSourceProperties mysqlDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Primary
    @Bean
    public DataSource mysqlDataSource(@Qualifier("mysqlDataSourceProperties") DataSourceProperties dataSourceProperties) {
        return dataSourceProperties.initializeDataSourceBuilder().build();
    }

    @Primary
    @Bean
    public LocalContainerEntityManagerFactoryBean mysqlEntityManagerFactory(@Qualifier("mysqlDataSource") DataSource hubDataSource, EntityManagerFactoryBuilder builder) {
        return builder.dataSource(hubDataSource).packages("com.sma.backend.multidb.database.mysql.domain")
                .persistenceUnit("mysql").build();
    }

    @Primary
    @Bean
    public PlatformTransactionManager mysqlTransactionManager(@Qualifier("mysqlEntityManagerFactory") EntityManagerFactory factory) {
        return new JpaTransactionManager(factory);
    }

}

POINTS TO REMEMBER :

hibernate.dialect → The dialect specifies the type of database used in hibernate so that hibernate generate appropriate type of SQL statements. For connecting any hibernate application with the database, it is required to provide the configuration of SQL dialect.

Hence to specify which language to use we have to define seperate values for this.

We can do that by passing this and all other properties which are specific to the databases in a map tagged as properties in EntityManagerFactoryBuilder

If running on Mac local you have to keep different ports for running both the databases on localHost as MSSQL needs Docker to run .

I hope that this Blog Post helped you! If you have any questions, feel free to use the comment section! 💬

Oh and if you want more content like this, follow me:

Blog