By simple configuration, Spring boot provides a powerful way to work with a single database.  Sometimes you need to work with multiple databases (for example, one for basic usage and another for archiving). In this tutorial, we will see how to make Spring Boot multiple databases configuration.

Used in this tutorial
  • Spring Boot (1.4.2.RELEASE)
    • spring-boot-starter-data-jpa
    • spring-boot-starter-test
  • Hibernate (5.0.11.Final)
  • MySQL connector (5.1.40)
  • Postgresql (9.4.1212.jre7)
  • Maven (3.3.9)

Setting up the project

Databases

Let’s consider the following case : We want to store authors and their books in two different databases. The first one, a MySQL database in which we will store the authors, and the second is a PostgreSQL database which will contain the authors books.

Spring boot multiple databases

Project structure

Let’s take a look at the project structure :

Spring boot multiple databases project structure


The tutorial source code is available on Github. Download the source

Project Object Model

I use Maven to manage project dependencies. Below the content of the pom.xml file :

Spring boot properties file

The application.properties file contains the properties to configure the Spring Boot application. It contains the information of the two data sources. Below its content :

Note that the properties for each data source are quite similar expect for the prefix part. In fact, we will tell Spring boot to pick up the correct prefix to configure the adequate database.

In this example :

  • The prefix spring.postgresql.datasource.* is used to configure the PostgreSQL database
  • The prefix spring.mysql.datasource.* is used to configure the MySQL database

Main class

Our main class is defined is Application.java. It’s annotated with @SpringBootApplication which is  equivalent to using @Configuration, @EnableAutoConfiguration and @ComponentScan all together.


The tutorial source code is available on Github. Download the source

Datasources configuration

PostgreSQL data source configuration

The PostgreSQL data source is defined in the PostgresqlConfiguration.java. Let’s see the file content before commenting it :

Annotations

Three annotations are applied on the PostgresqlConfiguration class and below the role of each one :

  • @Configuration: indicate that our class declares @Bean methods that will be processed by the Spring container to be used at runtime.
  • @EnableTransactionManagement: used to allow the usage of annotation-driven transaction management capability.
  • @EnableJpaRepositories: since I’m using spring data jpa, this annotation is required to tell Spring to enable JPA repositories. We specified the entityManagerFactory and the transactionManager beans to be used in the JPA repositories. These beans will be shown in the next section. Next, we have to give the location (package) where spring  will find the jpa repositories (In our example : The jpa repositories for the postgresql database are located under the package : com.roufid.tutoriel.dao.postgresql)

Data source definition

The first step is to define the datasource.

With the annotation @ConfigurationProperties  we tell spring to pick up the data source properties that are prefixed with   spring.postgresql.datasource from the application.properties file and build a data source using DataSourceBuilder.

@Primary is to handle multiple autowire candidate. This annotation is used because our application have two bean of the same type. So the bean of type DataSource of the PostgreSQL database bean will be given preference.

Entity manager definition

This bean creates a JPA EntityManagerFactory that will used in the JPA repositories.

Note that you must specify the package where your entities are located. You can set  com.roufid.tutoriel.entity.postgresqlrather than Book.class.

I use the hibernateProperties() method to load some hibernate properties from the file hibernate.properties I put in classpath.

Transaction manager definition

The last thing to configure is the JPA transaction manager. The definition is as below :

It’s done, our PostgreSQL data source is ready to use !

MySQL data source configuration

The MySQL data source definition is quite similar to the PostgreSQL data source. The difference lies in the prefix that will be used by Spring to load the data source properties and the packages where the entities and JPA repositories are located.

Below the MySQL data source configuration :

Annotations

The MysqlConfiguration class is annotated with @Configuration because it also declares  @Bean methods. We enable the usage of annotation driven transaction management with the annotation @EnableTransactionManagement.

@EnableJpaRepositories: We set entityManagerFactory and the transactionManager beans to be used in the JPA repositories of the MySQL database. The repositories to be scanned are located in the package com.roufid.tutoriel.dao.mysql.

Data source definition

We tell spring that the properties to define the mysql data source are prefixed with spring.mysql.datasource.

EntityManagerFactory and JPA Transaction Manager

The entity manager factory and the JPA transaction manager of the MySQL data source are approximatively the same. The only differences are the data source used by entity manager, the package to scan to find the entities and the persistence unit name.

Testing  the application

Consider the following basic example to test the application : Le’ts create an author to which a book will be associated. Then we will try to find the author of the given book.

Spring boot test will be used in this example combined with JUnit.

JUnit test code

ApplicationTest.java

Running the unit test

  • Right-Click on your test and choose Run as -> JUnit Test

Spring boot starting

  • JUnit test result

Spring boot multiple database test result

  • Database content
    • MySQL

      Spring boot multiple database – MySQL author database

    • PostgreSQL

      Spring boot multiple database – PostgreSQL book database


The tutorial source code is available on Github. Download the source

References