Multiple Data Source in Spring boot Using Spring boot data starter(HikariCP)

Most of us use Spring Boot to rapidly start the project by using its starter packages, one such package we use to connect persistent layer is spring boot data starter, and in most scenario just by adding the dependency is sufficient, but when we have multiple data source like MySQL,Oracle or PostgreSQL, we need to configure multiple data source to retrieve from different persistence unit or from different schema, we need to  override default configurations, to do configure multiple data source in spring boot is very simple to speed up our development time,so in this article will help to configure multiple data source for demanding project needs.

Lets First Create Primary Data Source to connect to database

Usually need to configure primary data source, primary means this will be default when data source when we actually @autowire the dependency. also some times it acts as fallback configuration.So primary Data source is identified by @Primary annotation.

package com.**.**.configs.datasource;

import com.zaxxer.hikari.HikariDataSource;
import org.hibernate.cfg.AvailableSettings;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;
import java.util.HashMap;


@PropertySource("classpath:application.properties")
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "DS1EntityManagerFactory",
        transactionManagerRef = "DS1TransactionManager", basePackages = {"com.**.**.repositories.ds1"})
public class DS1 {

    @Autowired
    Environment environment;


    @Bean(name = "DS1DataSource")
    @ConfigurationProperties(prefix = "ds1.spring.datasource")
    public DataSource DS1DataSource() {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setDriverClassName(environment.getProperty("spring.datasource.driver"));
        dataSource.setJdbcUrl(environment.getProperty("ds1.spring.datasource.url"));
        dataSource.setUsername(environment.getProperty("ds1.spring.datasource.username"));
        dataSource.setPassword(environment.getProperty("ds1.spring.datasource.password"));
        dataSource.setCatalog("*****");
        /**
         * HikariCP specific properties. Remove if you move to other connection pooling library.
         **/
        dataSource.addDataSourceProperty("cachePrepStmts", true);
        dataSource.addDataSourceProperty("prepStmtCacheSize", 25000);
        dataSource.addDataSourceProperty("prepStmtCacheSqlLimit", 20048);
        dataSource.addDataSourceProperty("useServerPrepStmts", true);
        dataSource.addDataSourceProperty("initializationFailFast", true);
        dataSource.setPoolName("DS1_HIKARICP_CONNECTION_POOL");

        return dataSource;
    }


    @Bean(name = "DS1EntityManagerFactory")
    @Primary //important
    public LocalContainerEntityManagerFactoryBean DS1EntityManagerFactory() {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(DS1DataSource());
        em.setPersistenceUnitName("DS1");
        em.setPackagesToScan("com.**.**.entities.DS1");
        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        HashMap<String, Object> properties = new HashMap<>();
        properties.put(AvailableSettings.HBM2DDL_AUTO, environment.getProperty("spring.jpa.hibernate.ddl-auto"));
        em.setJpaPropertyMap(properties);
        return em;
    }


    @Bean(name = "DS1TransactionManager")
    public PlatformTransactionManager DS1TransactionManager() {
        JpaTransactionManager transactionManager
                = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(
                DS1EntityManagerFactory().getObject());
        return transactionManager;
    }

}

Creating Secondary Data Secondary Data Source

Secondary is almost similar like primary one, but we don't provide @primary annotation rest of the code remains same, just bean name and data sources name varies.

2. Lets create secondary data source

package com.**.**.configs.datasource;

import com.zaxxer.hikari.HikariDataSource;
import org.hibernate.cfg.AvailableSettings;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.context.annotation.PropertySource;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import javax.sql.DataSource;
import java.util.HashMap;


@PropertySource("classpath:application.properties")
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(entityManagerFactoryRef = "DS2EntityManagerFactory",
        transactionManagerRef = "DS2TransactionManager", basePackages = {"com.**.**.repositories.ds2"})
public class DS2 {

    @Autowired
    Environment environment;


    @Bean(name = "DS2DataSource")
    @ConfigurationProperties(prefix = "ds2.spring.datasource")
    public DataSource DS2DataSource() {
        HikariDataSource dataSource = new HikariDataSource();
        dataSource.setDriverClassName(environment.getProperty("spring.datasource.driver"));
        dataSource.setJdbcUrl(environment.getProperty("ds2.spring.datasource.url"));
        dataSource.setUsername(environment.getProperty("ds2.spring.datasource.username"));
        dataSource.setPassword(environment.getProperty("ds2.spring.datasource.password"));
        dataSource.setCatalog("*****");
        /**
         * HikariCP specific properties. Remove if you move to other connection pooling library.
         **/
         /**for handling concurrent request**/
         
        dataSource.setMaximumPoolSize(50);
        dataSource.setAllowPoolSuspension(true);
        dataSource.setMinimumIdle(5);
        dataSource.setConnectionTimeout(300000);
        dataSource.setMaxLifetime(900000);
        
        dataSource.addDataSourceProperty("cachePrepStmts", true);
        dataSource.addDataSourceProperty("prepStmtCacheSize", 25000);
        dataSource.addDataSourceProperty("prepStmtCacheSqlLimit", 20048);
        dataSource.addDataSourceProperty("useServerPrepStmts", true);
        dataSource.addDataSourceProperty("initializationFailFast", true);
        dataSource.setPoolName("DS2_HIKARICP_CONNECTION_POOL");

        return dataSource;
    }


    @Bean(name = "DS2EntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean DS2EntityManagerFactory() {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(DS2DataSource());
        em.setPersistenceUnitName("DS2");
        em.setPackagesToScan("com.**.**.entities.DS2");
        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        HashMap<String, Object> properties = new HashMap<>();
        properties.put(AvailableSettings.HBM2DDL_AUTO, environment.getProperty("spring.jpa.hibernate.ddl-auto"));
        em.setJpaPropertyMap(properties);
        return em;
    }


    @Bean(name = "DS2TransactionManager")
    public PlatformTransactionManager DS2TransactionManager() {
        JpaTransactionManager transactionManager
                = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(
                DS2EntityManagerFactory().getObject());
        return transactionManager;
    }

}

So the above code snippets we have created two data sources which connects to different schema and have different connection pool.like the second data source we can create n numbers of data sources. if you have more than one data source.

Some important steps to follow after creating the data source

1. Ensure the repositories and entity which we to create are in the packages configured in above code em.setPackagesToScan("com.**.**.entities.DS<1/2>") ,..basePackages = {"com.**.**.repositories.ds<1/2>"})
2. And we can normally use repositories by @autowired it in our services.
3. And if we are Auto wiring the EnitityManager class use  it as shown below

 @PersistenceContext(name = "DS1/DS2")
    EntityManager vpoEm;

Which is the name provided in the em.setPersistenceUnitName("DS2");in the function DS1EntityManagerFactory or DS1EntityManagerFactory

Just to summarise the concept we are creating multiple Data Sources by overriding auto configuration of spring boot. basically we create DataSource, EntityManger, TranscationManager with different bean name so the bean class wont conflict. while Auto loading based on the packages repositories use the respective configuration which is handeled by spring.