Spring Boot Multiple Data Sources Example

Here I am going to show how to configure multiple data sources in spring boot. Spring Boot multiple data sources configuration are advantageous to connect with different databases in a single spring boot application.

Spring Boot Multiple Data Sources :

Technologies.

Spring Boot 1.5.1.RELEASE
Java 1.8
Hibernate 5.0.1
Maven
MySql

Create Multiple Databases in Mysql :

Since we are going to work with multiple data sources, we need to have multiple databases in our local machine to access them.

Create Data Bases :

Step 1: Open MySql command prompt and log in with your credentials.

Step 2: Create two different databases by passing the below command :

Database

mysql>create database db1;

Query OK, 1 row affected (0.10 sec)

mysql>use db1;

// Create person table under db1

CREATE TABLE person (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(50) DEFAULT NULL,
city varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
)

mysql> create database db2;

Query OK, 1 row affected (0.9 sec)

mysql>use db2;

// Create department table under db2

CREATE TABLE department (
id int(11) NOT NULL AUTO_INCREMENT,
name varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
)

Now two databases are ready to use. We are going to get access these two from our Spring Boot Multiple Data Sources example.

Recommended: Spring Boot JPA Integration

Multiple Data Sources Project Structure :

A typical Maven project structure.

Project Dependencies :

pom.xml

<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 http://maven.apache.org/xsd/maven-4.0.0.xsd">
  <modelVersion>4.0.0</modelVersion>
  <groupId>org.springframework.samples.service.service</groupId>
  <artifactId>SpringBoot_JPA_Multiple_DataSource</artifactId>
  <version>0.0.1-SNAPSHOT</version>
  <packaging>war</packaging>
  <properties>
    <java.version>1.8</java.version>
  </properties>
  <parent>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-parent</artifactId>
    <version>1.5.1.RELEASE</version>
  </parent>
  <dependencies>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-data-jpa</artifactId>
    </dependency>
    <dependency>
      <groupId>org.springframework.boot</groupId>
      <artifactId>spring-boot-starter-test</artifactId>
      <scope>test</scope>
    </dependency>
    <dependency>
      <groupId>mysql</groupId>
      <artifactId>mysql-connector-java</artifactId>
    </dependency>
  </dependencies>
  <build>
    <plugins>
      <plugin>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-maven-plugin</artifactId>
      </plugin>
    </plugins>
  </build>
  <repositories>
    <repository>
      <id>spring-releases</id>
      <name>Spring Releases</name>
      <url>https://repo.spring.io/libs-release</url>
    </repository>
    <repository>
      <id>org.jboss.repository.releases</id>
      <name>JBoss Maven Release Repository</name>
      <url>https://repository.jboss.org/nexus/content/repositories/releases</url>
    </repository>
  </repositories>
  <pluginRepositories>
    <pluginRepository>
      <id>spring-releases</id>
      <name>Spring Releases</name>
      <url>https://repo.spring.io/libs-release</url>
    </pluginRepository>
  </pluginRepositories>
</project>

Configuration Properties :

Define two database configurations in a single application.properties file like below.

application.properties

jdbc.driver-class-name:com.mysql.jdbc.Driver
jdbc.show-sql:true

#First Datasource (DB1)
db1.datasource.url:jdbc:mysql://localhost:3306/db1?useSSL=false
db1.datasource.username:root
db1.datasource.password:12345

#Second Datasource (DB2)
db2.datasource.url:jdbc:mysql://localhost:3306/db2?useSSL=false
db2.datasource.username:root
db2.datasource.password:54321

spring.jooq.sql-dialect=org.hibernate.dialect.MySQL5Dialect

Create Two Data Source Configurations :

Since we are going to get access to two different databases (db1, db2), we need to configure each data source configuration separately like:

DB1_DataSource.java

package com.onlinetutorialspoint.config;

import java.util.HashMap;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;

@Configuration
@EnableJpaRepositories(basePackages = {"com.onlinetutorialspoint.repository.db1"},
        entityManagerFactoryRef = "db1EntityManager",
        transactionManagerRef = "db1TransactionManager")
public class DB1_DataSource {
    @Autowired
    private Environment env;
    @Bean
    @Primary
    public LocalContainerEntityManagerFactoryBean db1EntityManager() {
        LocalContainerEntityManagerFactoryBean em = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(db1Datasource());
        em.setPackagesToScan(new String[]{"com.onlinetutorialspoint.model.db1"});
        em.setPersistenceUnitName("db1EntityManager");
        HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        HashMap<string, object=""> properties = new HashMap<>();
        properties.put("hibernate.dialect",
                env.getProperty("hibernate.dialect"));
        properties.put("hibernate.show-sql",
                env.getProperty("jdbc.show-sql"));
        em.setJpaPropertyMap(properties);
        return em;
    }

    @Primary
    @Bean
    public DataSource db1Datasource() {

        DriverManagerDataSource dataSource
                = new DriverManagerDataSource();
        dataSource.setDriverClassName(
                env.getProperty("jdbc.driver-class-name"));
        dataSource.setUrl(env.getProperty("db1.datasource.url"));
        dataSource.setUsername(env.getProperty("db1.datasource.username"));
        dataSource.setPassword(env.getProperty("db1.datasource.password"));

        return dataSource;
    }

    @Primary
    @Bean
    public PlatformTransactionManager db1TransactionManager() {

        JpaTransactionManager transactionManager
                = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(
                db1EntityManager().getObject());
        return transactionManager;
    }
}

@EnableJpaRepositories : Annotation to enable JPA repositories. It will scan the package of the annotated configuration classes for repositories. We can let to scan our repositories by giving the basePackages attribute.

@Primary: This annotation indicates that a bean should be given as primary preference when multiple qualified beans were autowired on a single-valued dependency. In our case, we have two different data sources, so that we should have to declare anyone as a primary data source between two (or among all of them).

Recommended: Spring Boot Hibernate Integration.

DB2_DataSource.java

package com.onlinetutorialspoint.config;

import java.util.HashMap;

import javax.sql.DataSource;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;
import org.springframework.data.jpa.repository.config.EnableJpaRepositories;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.orm.jpa.vendor.HibernateJpaVendorAdapter;
import org.springframework.transaction.PlatformTransactionManager;


@Configuration
@EnableJpaRepositories(basePackages = {"com.onlinetutorialspoint.repository.db2"},
        entityManagerFactoryRef = "db2EntityManager",
        transactionManagerRef = "db2TransactionManager")
public class DB2_DataSource {

    @Autowired
    private Environment env;

    @Bean
    public LocalContainerEntityManagerFactoryBean db2EntityManager() {
        LocalContainerEntityManagerFactoryBean em
                = new LocalContainerEntityManagerFactoryBean();
        em.setDataSource(db2Datasource());
        em.setPackagesToScan(
                new String[]{"com.onlinetutorialspoint.model.db2"});
        em.setPersistenceUnitName("db2EntityManager");
        HibernateJpaVendorAdapter vendorAdapter
                = new HibernateJpaVendorAdapter();
        em.setJpaVendorAdapter(vendorAdapter);
        HashMap<string, object=""> properties = new HashMap<>();
        properties.put("hibernate.dialect",
                env.getProperty("hibernate.dialect"));
        properties.put("hibernate.show-sql",
                env.getProperty("jdbc.show-sql"));
        em.setJpaPropertyMap(properties);
        return em;
    }

    @Bean
    public DataSource db2Datasource() {
        DriverManagerDataSource dataSource
                = new DriverManagerDataSource();
        dataSource.setDriverClassName(
                env.getProperty("jdbc.driver-class-name"));
        dataSource.setUrl(env.getProperty("db2.datasource.url"));
        dataSource.setUsername(env.getProperty("db2.datasource.username"));
        dataSource.setPassword(env.getProperty("db2.datasource.password"));

        return dataSource;
    }

    @Bean
    public PlatformTransactionManager db2TransactionManager() {
        JpaTransactionManager transactionManager
                = new JpaTransactionManager();
        transactionManager.setEntityManagerFactory(
                db2EntityManager().getObject());
        return transactionManager;
    }
}

Create Repositories :

PersonRepository.java

package com.onlinetutorialspoint.repository.db1;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import com.onlinetutorialspoint.model.db1.Person;
@Repository
public interface PersonRepository extends CrudRepository<person, long="">{
}

DepartmentRepository.java

package com.onlinetutorialspoint.repository.db2;
import org.springframework.data.repository.CrudRepository;
import org.springframework.stereotype.Repository;

import com.onlinetutorialspoint.model.db1.Person;
import com.onlinetutorialspoint.model.db2.Department;
@Repository
public interface DepartmentRepository extends CrudRepository<department, long="">{
}

Create Entity Classes :

Person.java

package com.onlinetutorialspoint.model.db1;

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "person")
public class Person {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    @Column(name="city")
    private String city;

    public Person() {
        super();
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String getCity() {
        return city;
    }

    public void setCity(String city) {
        this.city = city;
    }

    @Override
    public String toString() {
        return "Person [pid=" + id + ", pName=" + name + ", pCity=" + city
                + "]";
    }

}

Department.java

package com.onlinetutorialspoint.model.db2;

import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name = "department")
public class Department {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    private String name;
    public Department() {
        super();
    }

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    @Override
    public String toString() {
        return "Department [id=" + id + ", Name=" + name +"]";
    }

}

Create Services :

PersonService.java

package com.onlinetutorialspoint.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.onlinetutorialspoint.model.db1.Person;
import com.onlinetutorialspoint.repository.db1.PersonRepository;

@Service
public class PersonService {
    @Autowired
    PersonRepository personRepo;

    public List getAllPersons() {
        return (List) personRepo.findAll();
    }

    public Person savePerson(Person person) {
        return personRepo.save(person);
    }
}

DepartmentService.java

package com.onlinetutorialspoint.service;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.onlinetutorialspoint.model.db2.Department;
import com.onlinetutorialspoint.repository.db2.DepartmentRepository;

@Service
public class DepartmentService {
    @Autowired
    DepartmentRepository deptRepo;

    public List getAllDepartment() {
        return (List) deptRepo.findAll();
    }

    public Department saveDepartment(Department dept) {
        return deptRepo.save(dept);
    }
}

Main Class:

Application.java

package com.onlinetutorialspoint;

import java.util.List;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.CommandLineRunner;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.context.annotation.Bean;

import com.onlinetutorialspoint.model.db1.Person;
import com.onlinetutorialspoint.model.db2.Department;
import com.onlinetutorialspoint.repository.db1.PersonRepository;
import com.onlinetutorialspoint.service.DepartmentService;
import com.onlinetutorialspoint.service.PersonService;

@SpringBootApplication
public class Application {
    public static void main(String[] args) {
        SpringApplication.run(Application.class);
    }
    @Autowired
    PersonService personService;
    @Autowired
    DepartmentService deptService;
    
    @Autowired 
    @Bean
    public CommandLineRunner run(PersonRepository repository) {
        return (args) -> {
            //savePersonDetails();
            //saveDepartmentDetails();
            getAllPerson();
            getDepartments();
        };
    }
    
    public Person savePersonDetails(){
        Person person = new Person();
        person.setName("Chandra Shekhar Goka");
        person.setCity("Hyderabad");
        return personService.savePerson(person);
    }
    
    public Department saveDepartmentDetails(){
        Department dept = new Department();
        dept.setName("IT");
        return deptService.saveDepartment(dept);
    }
    
    public void getPersonDetails(){
        
    }
    
    public void getAllPerson(){
        List persons = personService.getAllPersons();
        persons.forEach(System.out::println);
    }
    
    public void getDepartments(){
        List depts = deptService.getAllDepartment();
        depts.forEach(System.out::println);
    }
    
}

Run the Application:

Console

mvn clean install;
mvn spring-boot:run
[INFO] Scanning for projects...
[INFO]
[INFO] ------------------------------------------------------------------------
[INFO] Building SpringBoot_JPA_Multiple_DataSource 0.0.1-SNAPSHOT
[INFO] ------------------------------------------------------------------------
[INFO]
[INFO] >>> spring-boot-maven-plugin:1.5.1.RELEASE:run (default-cli) > test-compile @ SpringBoot_JPA_Multiple_DataSource >>>
[INFO]
[INFO] --- maven-resources-plugin:2.6:resources (default-resources) @ SpringBoot_JPA_Multiple_DataSource ---
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] Copying 1 resource
[INFO] Copying 2 resources
[INFO]
[INFO] --- maven-compiler-plugin:3.1:compile (default-compile) @ SpringBoot_JPA_Multiple_DataSource ---
[INFO] Nothing to compile - all classes are up to date
[INFO]
[INFO] --- maven-resources-plugin:2.6:testResources (default-testResources) @ SpringBoot_JPA_Multiple_DataSource ---
[INFO] Using 'UTF-8' encoding to copy filtered resources.
[INFO] skip non existing resourceDirectory /home/chandrashekhar/Documents/Spring-Class/SpringBoot_JPA_Multiple_DataSource/src/test/resources
[INFO]
[INFO] --- maven-compiler-plugin:3.1:testCompile (default-testCompile) @ SpringBoot_JPA_Multiple_DataSource ---
[INFO] Nothing to compile - all classes are up to date
[INFO]
[INFO] <<< spring-boot-maven-plugin:1.5.1.RELEASE:run (default-cli) < test-compile @ SpringBoot_JPA_Multiple_DataSource <<<
[INFO]
[INFO] --- spring-boot-maven-plugin:1.5.1.RELEASE:run (default-cli) @ SpringBoot_JPA_Multiple_DataSource ---

. ____ _ __ _ _
/\\ / ___'_ __ _ _(_)_ __ __ _ \ \ \ \
( ( )\___ | '_ | '_| | '_ \/ _` | \ \ \ \
\\/ ___)| |_)| | | | | || (_| | ) ) ) )
' |____| .__|_| |_|_| |_\__, | / / / /
=========|_|==============|___/=/_/_/_/
:: Spring Boot :: (v1.5.1.RELEASE)

INFO Application - Starting Application on goka with PID 8808 (/home/chandrashekhar/Documents/Spring-Class/SpringBoot_JPA_Multiple_DataSource/target/classes started by chandrashekhar in /home/chandrashekhar/Documents/Spring-Class/SpringBoot_JPA_Multiple_DataSource)
INFO Application - No active profile set, falling back to default profiles: default
INFO AnnotationConfigApplicationContext - Refreshing org.springframework.context.annotation.AnnotationConfigApplicationContext@73a7b154: startup date [Sat Jun 24 14:36:09 IST 2017]; root of context hierarchy
INFO DriverManagerDataSource - Loaded JDBC driver: com.mysql.jdbc.Driver
INFO LocalContainerEntityManagerFactoryBean - Building JPA container EntityManagerFactory for persistence unit 'db1EntityManager'
INFO LogHelper - HHH000204: Processing PersistenceUnitInfo [
name: db1EntityManager
...]
INFO Version - HHH000412: Hibernate Core {5.0.11.Final}
INFO Environment - HHH000206: hibernate.properties not found
INFO Environment - HHH000021: Bytecode provider name : javassist
INFO Version - HCANN000001: Hibernate Commons Annotations {5.0.1.Final}
INFO Dialect - HHH000400: Using dialect: org.hibernate.dialect.MySQL5Dialect
INFO LocalContainerEntityManagerFactoryBean - Initialized JPA EntityManagerFactory for persistence unit 'db1EntityManager'
INFO DriverManagerDataSource - Loaded JDBC driver: com.mysql.jdbc.Driver
INFO LocalContainerEntityManagerFactoryBean - Building JPA container EntityManagerFactory for persistence unit 'db2EntityManager'
INFO LogHelper - HHH000204: Processing PersistenceUnitInfo [
name: db2EntityManager
...]
INFO Dialect - HHH000400: Using dialect: org.hibernate.dialect.MySQL5Dialect
INFO LocalContainerEntityManagerFactoryBean - Initialized JPA EntityManagerFactory for persistence unit 'db2EntityManager'
INFO AnnotationMBeanExporter - Registering beans for JMX exposure on startup
INFO QueryTranslatorFactoryInitiator - HHH000397: Using ASTQueryTranslatorFactory
Person [pid=1, pName=Chandra Shekhar Goka, pCity=Hyderabad]
Person [pid=2, pName=Rahul, pCity=Vijayawada]
INFO QueryTranslatorFactoryInitiator - HHH000397: Using ASTQueryTranslatorFactory
Department [id=1, Name=IT]
INFO Application - Started Application in 8.902 seconds (JVM running for 25.604)
[INFO] ------------------------------------------------------------------------
[INFO] BUILD SUCCESS
[INFO] ------------------------------------------------------------------------
[INFO] Total time: 16.005 s
[INFO] Finished at: 2017-06-24T14:36:17+05:30
[INFO] Final Memory: 36M/262M
[INFO] ------------------------------------------------------------------------
INFO AnnotationConfigApplicationContext - Closing org.springframework.context.annotation.AnnotationConfigApplicationContext@73a7b154: startup date [Sat Jun 24 14:36:09 IST 2017]; root of context hierarchy
INFO AnnotationMBeanExporter - Unregistering JMX-exposed beans on shutdown
INFO LocalContainerEntityManagerFactoryBean - Closing JPA EntityManagerFactory for persistence unit 'db2EntityManager'
INFO LocalContainerEntityManagerFactoryBean - Closing JPA EntityManagerFactory for persistence unit 'db1EntityManager'

Run the Application :

On the above log statements you can see two data different sources data -Person and Department data.

Happy Learning 🙂

Download Example

Spring Boot Multiple Data Sources Example
File size: 45 KB Downloads: 3728

Spring Boot Multiple Data Sources Example

Spring Boot Multiple Data Sources :

Create Multiple Databases in Mysql :

Create Data Bases :

Multiple Data Sources Project Structure :

Project Dependencies :

Configuration Properties :

Create Two Data Source Configurations :

Create Repositories :

Create Entity Classes :

Create Services :

Main Class:

Run the Application:

Download Example

About the Author: chandrashekhar

Spring Boot – How to load initial data on startup

Spring Boot PostgreSQL DB CRUD Example

How to Configure Spring Profile in Tomcat ?

How to fix BeanDefinitionOverrideException

How to enable Swagger in Spring Boot Application

Spring Boot Multiple Data Sources Example

Spring Boot Multiple Data Sources :

Create Multiple Databases in Mysql :

Create Data Bases :

Multiple Data Sources Project Structure :

Project Dependencies :

Configuration Properties :

Create Two Data Source Configurations :

Create Repositories :

Create Entity Classes :

Create Services :

Main Class:

Run the Application:

Download Example

Related Posts

Share a word.

About the Author: chandrashekhar

Related Posts

Spring Boot – How to load initial data on startup

Spring Boot PostgreSQL DB CRUD Example

How to Configure Spring Profile in Tomcat ?

How to fix BeanDefinitionOverrideException

How to enable Swagger in Spring Boot Application