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 :
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 :
<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.
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:
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;
}
}
@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.
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 :
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="">{
}
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 :
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
+ "]";
}
}
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 :
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);
}
}
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:
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 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.