Spring Boot Multiple Database Configuration using gradle
There are many situations where we require to fetch data from multiple databases and while using JPA sometimes it becomes challenging to create connections with multiple databases through a single application. Hence, in this article we will be discussing about creating multiple database connections with JPA using spring boot through a single application. Spring boot provides a very convenient way to use multiple datasources in a single application with properties file configurations.
Project Structure
We have two different config files for two different datasources and one more for data initialization in both tables. We have a controller that exposes a rest endpoint a /booking/{emailId} that returns bookings of the user based on the email id provided in the request.
Adding gradle nature to project
here, add gradle nature and later you can refresh gradle project if any change in build.gradle file.
Defining gradle : build.gradle
buildscript {
ext {
springBootVersion = '2.0.2.RELEASE'
}
repositories {
mavenCentral()
}
dependencies {
classpath("org.springframework.boot:spring-boot-gradle-plugin:${springBootVersion}")
}
}
apply plugin: 'java'
apply plugin: 'eclipse'
apply plugin: 'org.springframework.boot'
apply plugin: 'io.spring.dependency-management'
group = 'com.javaskool'
version = '0.0.1-SNAPSHOT'
sourceCompatibility = 1.8
repositories {
mavenCentral()
}
dependencies {
compile('org.springframework.boot:spring-boot-starter-cache')
compile('org.springframework.boot:spring-boot-starter-data-jpa')
compile('org.springframework.boot:spring-boot-starter-data-rest')
compile('org.springframework.boot:spring-boot-starter-integration')
compile('org.springframework.boot:spring-boot-starter-jersey')
compile('org.springframework.boot:spring-boot-starter-mail')
compile('org.springframework.boot:spring-boot-starter-validation')
compile('org.springframework.boot:spring-boot-starter-web')
compile('org.springframework.session:spring-session-core')
compile('org.springframework.boot:spring-boot-autoconfigure')
compile ('com.zaxxer:HikariCP:2.7.9')
runtime('mysql:mysql-connector-java')
compileOnly('org.projectlombok:lombok')
testCompile('org.springframework.boot:spring-boot-starter-test')
testCompile('org.springframework.batch:spring-batch-test')
}
task createFolder{
def configDir = new File(project.buildDir.path+"/libs", "config")
def keystoreDir = new File(project.buildDir.path+"/libs", "keystore")
def logDir = new File(project.buildDir.path+"/libs", "log")
def libDir = new File(project.buildDir.path+"/libs", "lib")
if(!logDir.exists()){
logDir.mkdirs()
}
delete configDir
delete libDir
delete keystoreDir
libDir.mkdirs()
configDir.mkdirs()
keystoreDir.mkdirs()
}
//copy config
task copyConfig(type: Copy) {
into project.buildDir.path+"/libs/config"
from "config"
}
//copy keystore
task copyKeystore(type: Copy) {
into project.buildDir.path+"/libs/keystore"
from "keystore"
}
//copy dependencies
task copyRuntimeLibs(type: Copy) {
into project.buildDir.path+"/libs/lib"
from configurations.compile
}
task bundleAll(type: Jar){
dependsOn 'createFolder', 'copyRuntimeLibs', 'copyConfig', 'copyKeystore'
manifest {
def manifestClasspath = configurations.compile.collect { "lib/" + it.getName() }.join(' ')
attributes 'Implementation-Title': 'spring-boot-multiple-database-gradle',
'Implementation-Version': version,
'Main-Class': 'com.javaskool.starter.SpringBootMultipleDatabaseGradleApplication',
'Class-Path': manifestClasspath
}
baseName=project.name
from { (configurations.compile - configurations.compile).collect { it.isDirectory() ? it : zipTree(it) } }
with jar
exclude 'META-INF/*.RSA', 'META-INF/*.SF','META-INF/*.DSA'
}
task zip(type: Zip){
dependsOn 'bundleAll'
from 'build/libs'
}
Defining Controller
Following is the controller that exposes an endpoint at /booking/emailId.
package com.javaskool.controller;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.http.HttpStatus;
import org.springframework.http.ResponseEntity;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import com.javaskool.model.booking.Booking;
import com.javaskool.service.BookingService;
@Controller
@RequestMapping("/booking")
public class BookingController {
@Autowired
private BookingService bookingService;
@RequestMapping(value = "/{email:.+}", method = RequestMethod.GET)
public ResponseEntity> findUserBookings(@PathVariable(name = "email", value = "email") String email) {
List bookings = bookingService.findUserBookings(email);
return new ResponseEntity>(bookings, HttpStatus.OK);
}
}
Defining Services
Following is the service class where we have injected both the DAOs. The service class communicates with the different DAOs and collects the data from multiple databases.
package com.javaskool.service.impl;
import java.util.List;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import com.javaskool.booking.dao.BookingDao;
import com.javaskool.model.booking.Booking;
import com.javaskool.model.user.UserDetails;
import com.javaskool.service.BookingService;
import com.javaskool.user.dao.UserDao;
@Service
public class BookingServiceImpl implements BookingService {
@Autowired
private UserDao userDao;
@Autowired
private BookingDao bookingDao;
public List findUserBookings(String emailId) {
UserDetails userdetails = userDao.findByEmail(emailId);
List bookings = bookingDao.findByCreatedBy(userdetails.getId());
return bookings;
}
}
Defining Model Classes
package com.javaskool.model.user;
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="user_details")
public class UserDetails {
@Id
@Column
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
@Column
private String firstName;
@Column
private String lastName;
@Column
private String email;
@Column
private String password;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public String getFirstName() {
return firstName;
}
public void setFirstName(String firstName) {
this.firstName = firstName;
}
public String getLastName() {
return lastName;
}
public void setLastName(String lastName) {
this.lastName = lastName;
}
public String getEmail() {
return email;
}
public void setEmail(String email) {
this.email = email;
}
public String getPassword() {
return password;
}
public void setPassword(String password) {
this.password = password;
}
}
package com.javaskool.model.booking;
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="booking_details")
public class Booking {
@Id
@Column
@GeneratedValue(strategy = GenerationType.IDENTITY)
private Long id;
private Long createdBy;
private String pickupAddress;
private String dropAddress;
private String bookingAmount;
public Long getId() {
return id;
}
public void setId(Long id) {
this.id = id;
}
public Long getCreatedBy() {
return createdBy;
}
public void setCreatedBy(Long createdBy) {
this.createdBy = createdBy;
}
public String getPickupAddress() {
return pickupAddress;
}
public void setPickupAddress(String pickupAddress) {
this.pickupAddress = pickupAddress;
}
public String getDropAddress() {
return dropAddress;
}
public void setDropAddress(String dropAddress) {
this.dropAddress = dropAddress;
}
public String getBookingAmount() {
return bookingAmount;
}
public void setBookingAmount(String bookingAmount) {
this.bookingAmount = bookingAmount;
}
}
Defining Dao classes
Following is the dao class that is responsible to query against user database. In this database, we have all the user details.The UserDao interface extends CrudRepository which has different crud methods such as create, findOne, delete etc and hence our UserDao automatically inherits them which is available for our service class to use. Its spring data which will generate the implementations at run time for these crud methods. Hence, we dont have to provide the implementations.
Notice the generic parameters in CrudRepository. Based on these parameters, Spring data will perform different crud operations at run time on our behalf.
package com.javaskool.user.dao;
import org.springframework.data.repository.CrudRepository;
import com.javaskool.model.user.UserDetails;
public interface UserDao extends CrudRepository {
UserDetails findByEmail(String email);
}
Following is the booking dao class which is responsible to query in booking table.
package com.javaskool.booking.dao;
import java.util.List;
import org.springframework.data.repository.CrudRepository;
import com.javaskool.model.booking.Booking;
public interface BookingDao extends CrudRepository {
List findByCreatedBy(Long userId);
}
Multiple Database Configurations in Spring Boot
Here is the application.properties file that contains configurations for multiple databases. You can notice that properties starting from spring.user.datasource has user database configuration and properties starting from spring.booking.datasource has booking datasource configurations. These configurations are used in the coming sections while configuring entitymanager and transactionmanager for respective DB connections.
#server.ip=192.168.1.1
server.port=9070
security.basic.enabled=false
spring.application.name=spring-boot-multiple-database-gradle
server.context-path=/spring-boot-multiple-database-gradle_api/
spring.db.mysql.driver=com.mysql.jdbc.Driver
# Show all queries
spring.jpa.show-sql=true
spring.jpa.hibernate.ddl-auto=create
spring.jpa.hibernate.naming.physical-strategy=org.hibernate.boot.model.naming.PhysicalNamingStrategyStandardImpl
spring.jpa.properties.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
spring.user.datasource.url=jdbc:mysql://localhost:3306/js_user_first_db
spring.user.datasource.username=root
spring.user.datasource.password=root
spring.user.datasource.driver-class-name=com.mysql.jdbc.Driver
spring.booking.datasource.url=jdbc:mysql://localhost:3306/js_booking_second_db
spring.booking.datasource.username=root
spring.booking.datasource.password=root
spring.booking.datasource.driver-class-name=com.mysql.jdbc.Driver
hibernate.properties
hibernate.show_sql=true
hibernate.format_sql=true
hibernate.hbm2ddl.auto=create
Configuring Booking Database
Here is the configuration to connect to booking database. We have configured the entitymanager required to query the booking DB as per JPA.
@ConfigurationProperties(prefix = “spring.booking.datasource”). This will ensure that spring picks properties starting with spring.booking.datasource to create the datasource and utilities it while executing methods of BookingDao.java.
basePackages = “com.javaskool.booking.dao” will ensure that spring uses booking datasource while executing methods of BookingDao.java
@Primary : It tells spring to use this bean to use as a primary bean as we have multiple beans for same return type. To use other beans of same return type we require to use @Qualifier annotation.
package com.javaskool.config;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import java.util.stream.Collectors;
import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
//import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
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.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PropertiesLoaderUtils;
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.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import com.javaskool.model.booking.Booking;
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
entityManagerFactoryRef = "bookingEntityManager",
transactionManagerRef = "bookingTransactionManager",
basePackages = "com.javaskool.booking.dao"
)
public class BookingPrimaryDBConfig {
@Autowired
private Environment env;
@Primary
@Bean
@ConfigurationProperties(prefix = "spring.booking.datasource")
public DataSource mysqlDataSource() {
/*return DataSourceBuilder
.create()
.build();*/
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName(env.getProperty("spring.booking.datasource.driver-class-name"));
dataSource.setUrl(env.getProperty("spring.booking.datasource.url"));
dataSource.setUsername(env.getProperty("spring.booking.datasource.username"));
dataSource.setPassword(env.getProperty("spring.booking.datasource.password"));
return dataSource;
}
@Primary
@Bean(name = "bookingEntityManager")
public LocalContainerEntityManagerFactoryBean mysqlEntityManagerFactory(EntityManagerFactoryBuilder builder) {
return builder
.dataSource(mysqlDataSource())
.properties(hibernateProperties())
.packages("com.javaskool.model.booking")
.persistenceUnit("booking_PU")
.build();
}
@Primary
@Bean(name = "bookingTransactionManager")
public PlatformTransactionManager mysqlTransactionManager(@Qualifier("bookingEntityManager") EntityManagerFactory entityManagerFactory) {
return new JpaTransactionManager(entityManagerFactory);
}
private Map hibernateProperties() {
Resource resource = new ClassPathResource("hibernate.properties");
try {
Properties properties = PropertiesLoaderUtils.loadProperties(resource);
return properties.entrySet().stream()
.collect(Collectors.toMap(
e -> e.getKey().toString(),
e -> e.getValue())
);
} catch (IOException e) {
return new HashMap();
}
}
}
Configuring User DataBase
Here is the configuration for user DB. The configurations are similar to BookingPrimaryDBConfig.java.
package com.javaskool.config;
import java.io.IOException;
import java.util.HashMap;
import java.util.Map;
import java.util.Properties;
import java.util.stream.Collectors;
import javax.persistence.EntityManagerFactory;
import javax.sql.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Qualifier;
//import org.springframework.boot.autoconfigure.jdbc.DataSourceBuilder;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.orm.jpa.EntityManagerFactoryBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.core.env.Environment;
import org.springframework.core.io.ClassPathResource;
import org.springframework.core.io.Resource;
import org.springframework.core.io.support.PropertiesLoaderUtils;
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.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;
import com.javaskool.model.user.UserDetails;
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
entityManagerFactoryRef = "userEntityManager",
transactionManagerRef = "userTransactionManager",
basePackages = "com.javaskool.user.dao"
)
public class UserSecondaryDBConfig {
@Autowired
private Environment env;
@Bean
@ConfigurationProperties(prefix = "spring.user.datasource")
public DataSource postgresqlDataSource() {
/*return DataSourceBuilder
.create()
.build();*/
DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName(env.getProperty("spring.user.datasource.driver-class-name"));
dataSource.setUrl(env.getProperty("spring.user.datasource.url"));
dataSource.setUsername(env.getProperty("spring.user.datasource.username"));
dataSource.setPassword(env.getProperty("spring.user.datasource.password"));
return dataSource;
}
@Bean(name = "userEntityManager")
public LocalContainerEntityManagerFactoryBean postgresqlEntityManagerFactory(EntityManagerFactoryBuilder builder) {
return builder
.dataSource(postgresqlDataSource())
.properties(hibernateProperties())
.packages("com.javaskool.model.user")
.persistenceUnit("user_PU")
.build();
}
@Bean(name = "userTransactionManager")
public PlatformTransactionManager postgresqlTransactionManager(@Qualifier("userEntityManager") EntityManagerFactory entityManagerFactory) {
return new JpaTransactionManager(entityManagerFactory);
}
private Map hibernateProperties() {
Resource resource = new ClassPathResource("hibernate.properties");
try {
Properties properties = PropertiesLoaderUtils.loadProperties(resource);
return properties.entrySet().stream()
.collect(Collectors.toMap(
e -> e.getKey().toString(),
e -> e.getValue())
);
} catch (IOException e) {
return new HashMap();
}
}
}
Defining Spring Application class
@SpringBootApplication enables many defaults. It is a convenience annotation that adds @Configuration, @ComponentScan.
The main() method uses Spring Boot SpringApplication.run() method to launch an application.
package com.javaskool.starter;
import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;
import org.springframework.boot.autoconfigure.security.servlet.SecurityAutoConfiguration;
import org.springframework.context.annotation.ComponentScan;
@ComponentScan("com.javaskool")
@SpringBootApplication(exclude = {SecurityAutoConfiguration.class })
public class SpringBootMultipleDatabaseGradleApplication {
public static void main(String[] args) {
SpringApplication.run(SpringBootMultipleDatabaseGradleApplication.class, args);
}
}
SQL for Booking Table
Here, no need to create tables and insert records. Spring application will do this. You need to create databases only.
create database js_user_first_db;
create database js_booking_second_db;
CREATE TABLE `user_details` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`email` varchar(255) DEFAULT NULL,
`firstName` varchar(255) DEFAULT NULL,
`lastName` varchar(255) DEFAULT NULL,
`password` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;
CREATE TABLE `booking_details` (
`id` bigint(20) NOT NULL AUTO_INCREMENT,
`bookingAmount` varchar(255) DEFAULT NULL,
`createdBy` bigint(20) DEFAULT NULL,
`dropAddress` varchar(255) DEFAULT NULL,
`pickupAddress` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1;
INSERT INTO js_user_first_db.user_details(email,firstName,lastName,`password`) VALUES ('javaskool@test.com','Abc','Test','password');
INSERT INTO js_booking_second_db.booking_details(bookingAmount,createdBy,dropAddress,pickupAddress)VALUES('999',1,'Chennai','Bangalore'),('998',1,'Bangalore','Chennai');
Running Application
1. Run SpringBootMultipleDatabaseGradleApplication.java as a java application.
2. Now hit the url – localhost:9070/booking/javaskool@test.com and you can see following.
Recent Comments