Spring Boot Multiple Database Configuration Example

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. Actually, 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. 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.

Here, we have user details present in one DB and the corresponding users booking is available in another DB.We have 2 different DAOs to communicate with the different databases.

Maven Dependencies

spring-boot-starter-parent: It provides useful Maven defaults. It also provides a dependency-management section so that you can omit version tags for existing dependencies.

spring-boot-starter-web: It includes all the dependencies required to create a web app. This will avoid lining up different spring common project versions.

spring-boot-starter-tomcat: It enable an embedded Apache Tomcat 7 instance, by default.

spring-boot-starter-data-jpa: It provides key dependencies for Hibernate, Spring Data JPA and Spring ORM.

<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-web</artifactId>
        </dependency>
		<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-tomcat</artifactId>
        </dependency>
		<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-security</artifactId>
		</dependency>
		<dependency>
            <groupId>org.springframework.boot</groupId>
            <artifactId>spring-boot-starter-data-jpa</artifactId>
			<exclusions>
                <exclusion>
                    <groupId>org.apache.tomcat</groupId>
                    <artifactId>tomcat-jdbc</artifactId>
                </exclusion>
            </exclusions>
		</dependency>
		<dependency>
            <groupId>mysql</groupId>
            &tartifactId>mysql-connector-java</artifactId>
        </dependency>
		<dependency>
            <groupId>commons-dbcp</groupId>
            <artifactId>commons-dbcp</artifactId>
		</dependency>
		
    </dependencies>
	

Defining Controller

Following is the controller that exposes an endpoint at /booking/emailId.

@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);
	}

}

 Other Interesting Posts
Spring Boot Hibernate 5 Example
Spring Data JPA Example
Spring Boot Actuator Rest Endpoints Example
Securing REST API with Spring Boot Security Basic Authentication
Spring Boot Security Password Encoding using Bcrypt Encoder
Spring Security with Spring MVC Example Using Spring Boot
Spring Boot JMS ActiveMQ Example

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 databses.

@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

@Entity
@Table
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;
	
	//corresponding getters and setters
	

@Entity
@Table
public class Booking {
	
	@Id
	@Column
	@GeneratedValue(strategy = GenerationType.IDENTITY)
	private Long id;
	
	private Long createdBy;
	private String pickupAddress;
	private String dropAddress;
	private String bookingAmount;
	
	//corresponding getters and setters
	

Defining User Dao

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.devglan.user.dao;

import org.springframework.data.repository.CrudRepository;

import com.devglan.model.UserDetails;

public interface UserDao extends CrudRepository {
	
	UserDetails findByEmail(String email);
	
}

Defining Booking Dao

Following is the booking dao class which is responsible to query in booking table.

package com.devglan.booking.dao;

import java.util.List;

import org.springframework.data.repository.CrudRepository;

import com.devglan.model.Booking;

public interface BookingDao extends CrudRepository {
	
	List findByCreatedBy(Long userId);

}

Multiple Database Configurations in Spring Boot

Following 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.

 
spring.user.datasource.url=jdbc:mysql://localhost:3306/user
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/booking
spring.booking.datasource.username=root
spring.booking.datasource.password=root
spring.booking.datasource.driver-class-name=com.mysql.jdbc.Driver

Configuring Booking Database

Following 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 utilise it while executing methods of BookingDao.java.

basePackages = "com.devglan.booking.dao" will ensure that spring uses booking datasource while executing methods of BookingDao.java

@PrimaryIt 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.devglan.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.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.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.orm.jpa.JpaTransactionManager;
import org.springframework.orm.jpa.LocalContainerEntityManagerFactoryBean;
import org.springframework.transaction.PlatformTransactionManager;
import org.springframework.transaction.annotation.EnableTransactionManagement;

import com.devglan.model.Booking;

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
		entityManagerFactoryRef = "bookingEntityManager", 
		transactionManagerRef = "bookingTransactionManager", 
		basePackages = "com.devglan.booking.dao"
)
public class BookingDBConfig {

	@Primary
	@Bean
	@ConfigurationProperties(prefix = "spring.booking.datasource")
	public DataSource mysqlDataSource() {
		return DataSourceBuilder
					.create()
					.build();
	}

	@Primary
	@Bean(name = "bookingEntityManager")
	public LocalContainerEntityManagerFactoryBean mysqlEntityManagerFactory(EntityManagerFactoryBuilder builder) {
		return builder
					.dataSource(mysqlDataSource())
					.properties(hibernateProperties())
					.packages(Booking.class)
					.persistenceUnit("bookingPU")
					.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

Follwing is the configuration for user DB. The configurations are similar to BookingDBConfig.java.

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
		entityManagerFactoryRef = "userEntityManager", 
		transactionManagerRef = "userTransactionManager", 
		basePackages = "com.devglan.user.dao"
)
public class UserDBConfig {

	@Bean
	@ConfigurationProperties(prefix = "spring.user.datasource")
	public DataSource postgresqlDataSource() {
		return DataSourceBuilder
					.create()
					.build();
	}

	@Bean(name = "userEntityManager")
	public LocalContainerEntityManagerFactoryBean postgresqlEntityManagerFactory(EntityManagerFactoryBuilder builder) {
		return builder
					.dataSource(postgresqlDataSource())
					.properties(hibernateProperties())
					.packages(UserDetails.class)
					.persistenceUnit("userPU")
					.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, @EnableAutoConfiguration, @EnableWebMvc, @ComponentScan

The main() method uses Spring Boot SpringApplication.run() method to launch an application.

package com.devglan;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class Application {

    public static void main(String[] args) {
        SpringApplication.run(Application.class, args);
    }

}

SQL for Booking Table

Following are some sample DML. We will be creating some dummy booking details using following insert statements.

CREATE TABLE booking(id BIGINT NOT NULL AUTO_INCREMENT, bookingAmount VARCHAR(255), createdBy BIGINT, dropAddress VARCHAR(255), pickupAddress  VARCHAR(255), PRIMARY KEY (id)) ENGINE=InnoDB;

INSERT INTO booking(bookingAmount,createdBy,dropAddress,pickupAddress)VALUES('1250',1,'Chennai','Bangalore');

INSERT INTO booking(bookingAmount,createdBy,dropAddress,pickupAddress)VALUES('2050',1,'Bangalore','Chennai');

SQL for UserDetails Table

Following are some sample DML. We will be creating some dummy user details using following insert statements.

create table User_Details (id integer not null auto_increment, email varchar(255), first_Name varchar(255), last_Name varchar(255), password varchar(255), primary key (id)) ENGINE=InnoDB;

INSERT INTO user_details(email,first_Name,last_Name,password) VALUES ('abc@test.com','Abc','Test','password');

Running Application

1. Run Application.java as a java application.

2. Now hit the url - localhost:8080/booking/abc@test.com and you can see following.

Conclusion

I hope this article served you that you were looking for. If you have anything that you want to add or share then please share it below in the comment section.