Monday , September 25 2017
Home / Spring Boot / Spring Boot Multiple Data Sources Example

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 very useful to connect with different databases in a single spring boot application.

Spring Boot Multiple Data Sources :

The technologies which are used for this example.

  • 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 those databases in our machine. If you have in your machine you can skip this or else follow the below steps to create databases in Mysql.

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

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

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.

Spring Boot Multiple Data Sources

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 :

application.properties

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


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 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 given the primary preference when multiple qualified beans autowire a single-valued dependency. In our case we have two data sources, we should declare anyone as primary 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);
    }
}
Spring Boot Root Class :
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 :
$ 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'
 On the above log statements you can see the highlighted lines for two data sources and data Person and Department data.
Happy Learning 🙂

Download Example

About chandrashekhar

Hi Folks, you have reach this so far, that shows you like what you are learning. Then why don't you support us to improve for bettor tutorials by leaving your valuable comments and why not you keep in touch with us for latest updates on your favorite blog @ facebook , twitter , Or Google+ ,

Recommended

Spring Boot FileUpload Ajax Example

In this tutorials, we are going to see how to work with Spring Boot Fileupload …

Leave a Reply

Your email address will not be published. Required fields are marked *