Connecting Multiple Databases in Spring Boot

In this tutorial, we will learn how to connect multiple databases in a Spring Boot application.

In this, we are going to connect two databases using myBatis.

These are the steps we are going to perform to connect our application with multiple databases.

adding dependencies.
add URL and credentials to the application.properties file
create a dataSource file for each database
for accessing create sqlSessionTemplate for each database
access database in the DAO layer

First, let's add the required dependencies

Add the below dependency in the pom.xml file.

<!-- mybatis dependencies -->
<dependency>
    <groupId>org.mybatis.spring.boot</groupId>
    <artifactId>mybatis-spring-boot-starter</artifactId>
    <version>1.3.3</version>
</dependency>

We are using Postgres, So adding postgreSql dependency

<dependency>
  <groupId>org.postgresql</groupId>
  <artifactId>postgresql</artifactId>
  <version>42.5.0</version>
</dependency>

In application.properties

DATABASE

# Data source 1

spring.datasource.url=jdbc:postgresql://192.168.1.31:5432/test_db_1
spring.datasource.username=postgres
spring.datasource.password=postgres
spring.datasource.platform=postgres

# Data source 2

spring.second.datasource.url=jdbc:postgresql://192.168.1.31:5432/test_db_2
spring.second.datasource.username=postgres
spring.second.datasource.password=postgres
spring.second.datasource.platform=postgres

Now create data source of you databases

import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
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.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;
import org.springframework.stereotype.Component;

import javax.sql.DataSource;

@Configuration
@Component
public class DataSource1Config {

    @Bean(name = "dataSource1")
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource dataSource1() {
        return DataSourceBuilder.create()
                .driverClassName("org.postgresql.Driver")
                .url("jdbc:postgresql://192.168.1.31:5432/test_db_1")
                .username("postgres")
                .password("postgres")
                .build();
    }

    @Bean(name = "sqlSessionFactory1")
    public SqlSessionFactory sqlSessionFactory1(@Qualifier("dataSource1") final DataSource dataSource1) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dataSource1);
        factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis-mapper-xml/*.xml"));
        return factoryBean.getObject();
    }

    @Bean(name = "sqlSessionTemplate1")
    public SqlSessionTemplate sqlSessionTemplate1(@Qualifier("sqlSessionFactory1") final SqlSessionFactory sqlSessionFactory1) {
        return new SqlSessionTemplate(sqlSessionFactory1);
    }

    @Bean
    @ConfigurationProperties("spring.datasource")
    public DataSourceProperties firstDataSourceProperties() {
        return new DataSourceProperties();
    }

    @Bean(name = "HikariDataSource1")
    public HikariDataSource dataSource() {
        DataSourceProperties properties = firstDataSourceProperties();
        return properties.initializeDataSourceBuilder().type(HikariDataSource.class)
                .build();
    }

}

second data source

import com.zaxxer.hikari.HikariDataSource;
import org.apache.ibatis.session.SqlSessionFactory;
import org.mybatis.spring.SqlSessionFactoryBean;
import org.mybatis.spring.SqlSessionTemplate;
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.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.io.support.PathMatchingResourcePatternResolver;

import javax.sql.DataSource;

@Configuration
public class DataSource2Config {

    @Bean(name = "dataSource2")
    //@Primary
    @ConfigurationProperties(prefix = "spring.second.datasource")
    public DataSource dataSource2() {
        System.out.println("created bean dataSource2 !!!");
        return DataSourceBuilder.create()
                .driverClassName("org.postgresql.Driver")
                .url("jdbc:postgresql://192.168.1.31:5432/test_db_2")
                .username("postgres")
                .password("postgres")
                .build();
    }

    @Bean(name = "sqlSessionFactory2")
    public SqlSessionFactory sqlSessionFactory2(@Qualifier("dataSource2")final DataSource dataSource2) throws Exception {
        SqlSessionFactoryBean factoryBean = new SqlSessionFactoryBean();
        factoryBean.setDataSource(dataSource2);
        factoryBean.setMapperLocations(new PathMatchingResourcePatternResolver().getResources("classpath:mybatis-mapper-xml/*.xml"));
        return factoryBean.getObject();
    }

    @Bean(name = "sqlSessionTemplate2")
    public SqlSessionTemplate sqlSessionTemplate2(@Qualifier("sqlSessionFactory2")final SqlSessionFactory sqlSessionFactory2) {
        return new SqlSessionTemplate(sqlSessionFactory2);
    }

    @Bean
    @ConfigurationProperties("spring.second.datasource")
    public DataSourceProperties secondDataSourceProperties() {
        return new DataSourceProperties();
    }
    @Bean(name = "HikariDataSource2")
    @Primary
    public HikariDataSource dataSource() {
        DataSourceProperties properties = secondDataSourceProperties();
        return properties.initializeDataSourceBuilder().type(HikariDataSource.class)
                .build();
    }

}

In Dao layer

import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

@Repository
public class Dao1 {

    @Autowired
    @Qualifier("sqlSessionTemplate1")
    private SqlSessionTemplate sqlSessionTemplate1;

    public List<Object> getDataFromDB1() {
        List<Object> objects = new ArrayList<>();
        try {
            objects = sqlSessionTemplate1.selectList("select * from company;");
            System.out.println("out1: " + objects.size());
        } catch (Exception exception) {
            System.out.println("Unable to connect database 1");
        }
        return objects;
    }

}

On the second DAO layer

import org.mybatis.spring.SqlSessionTemplate;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

@Repository
public class Dao2 {

    @Autowired
    @Qualifier("sqlSessionTemplate2")
    private SqlSessionTemplate sqlSessionTemplate2;

    public List<Object> getDataFromDB2() {
        List<Object> objects =new ArrayList<>();
        try {
            objects = sqlSessionTemplate2.selectList("select * from department;");
            System.out.println("out2: "+objects.toString());
            System.out.println("out2: "+objects.size());
        }catch (Exception e){
            System.out.println(""+e);
            System.out.println("unable to connect database 2");
        }

        return objects;
    }

}

For testing, we are going to add a controller

@RestController
public class Controller {

    @Autowired
    Dao1 dao1;

    @Autowired
    Dao2 dao2;

    @GetMapping(value = "/test", produces = "application/json")
    public List<Object> testDatabaseConnection() {
        List<Object> dataFromDB1 = null;
        List<Object> dataFromDB2 = null;

        dataFromDB1 = dao1.getDataFromDB1();
        dataFromDB2 = dao2.getDataFromDB2();

        List<Object> master = new ArrayList<>();
        master.add(dataFromDB1);
        master.add(dataFromDB2);

        return master;
    }
}