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 :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 |
<?xml version="1.0" encoding="UTF-8"?> <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>com.roufid.tutoriel</groupId> <artifactId>spring-boot-multi-database-tutoriel</artifactId> <version>0.0.1-SNAPSHOT</version> <packaging>jar</packaging> <name>SB-Multi-Database</name> <description>Demo for multi databases Spring Boot project </description> <parent> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-parent</artifactId> <version>1.4.2.RELEASE</version> </parent> <properties> <project.build.sourceEncoding>UTF-8</project.build.sourceEncoding> <project.reporting.outputEncoding>UTF-8</project.reporting.outputEncoding> <java.version>1.8</java.version> </properties> <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-data-jpa</artifactId> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.postgresql</groupId> <artifactId>postgresql</artifactId> <scope>runtime</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-test</artifactId> <scope>test</scope> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-configuration-processor</artifactId> <optional>true</optional> </dependency> </dependencies> <build> <plugins> <plugin> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-maven-plugin</artifactId> </plugin> </plugins> </build> </project> |
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 :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
# ----------------------- # POSTGRESQL DATABASE CONFIGURATION # ----------------------- spring.postgresql.datasource.url=jdbc:postgresql://localhost:5432/book_db spring.postgresql.datasource.username=postgres spring.postgresql.datasource.password=postgres spring.postgresql.datasource.driver-class-name=org.postgresql.Driver # ------------------------------ # MYSQL DATABASE CONFIGURATION # ------------------------------ spring.mysql.datasource.url=jdbc:mysql://localhost:3306/author_db?autoReconnect=true&useSSL=false spring.mysql.datasource.username=root spring.mysql.datasource.password= spring.mysql.datasource.driver-class-name=com.mysql.jdbc.Driver |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
package com.roufid.tutoriel; 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); } } |
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 :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 |
package com.roufid.tutoriel.configuration; 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.roufid.tutoriel.entity.postgresql.Book; /** * Spring configuration of the "PostgreSQL" database. * * @author Radouane ROUFID. * */ @Configuration @EnableTransactionManagement @EnableJpaRepositories( entityManagerFactoryRef = "postgresqlEntityManager", transactionManagerRef = "postgresqlTransactionManager", basePackages = "com.roufid.tutoriel.dao.postgresql" ) public class PostgresqlConfiguration { /** * PostgreSQL datasource definition. * * @return datasource. */ @Bean @Primary @ConfigurationProperties(prefix = "spring.postgresql.datasource") public DataSource postgresqlDataSource() { return DataSourceBuilder .create() .build(); } /** * Entity manager definition. * * @param builder an EntityManagerFactoryBuilder. * @return LocalContainerEntityManagerFactoryBean. */ @Primary @Bean(name = "postgresqlEntityManager") public LocalContainerEntityManagerFactoryBean postgresqlEntityManagerFactory(EntityManagerFactoryBuilder builder) { return builder .dataSource(postgresqlDataSource()) .properties(hibernateProperties()) .packages(Book.class) .persistenceUnit("postgresqlPU") .build(); } @Primary @Bean(name = "postgresqlTransactionManager") public PlatformTransactionManager postgresqlTransactionManager(@Qualifier("postgresqlEntityManager") EntityManagerFactory entityManagerFactory) { return new JpaTransactionManager(entityManagerFactory); } private Map<String, Object> 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<String, Object>(); } } } |
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.
|
1 2 3 4 5 6 7 8 |
@Bean @Primary @ConfigurationProperties(prefix = "spring.postgresql.datasource") public DataSource postgresqlDataSource() { return DataSourceBuilder .create() .build(); } |
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
|
1 2 3 4 5 6 7 8 9 10 |
@Primary @Bean(name = "postgresqlEntityManager") public LocalContainerEntityManagerFactoryBean postgresqlEntityManagerFactory(EntityManagerFactoryBuilder builder) { return builder .dataSource(postgresqlDataSource()) .properties(hibernateProperties()) .packages(Book.class) .persistenceUnit("postgresqlPU") .build(); } |
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 :
|
1 2 3 4 5 |
@Primary @Bean(name = "postgresqlTransactionManager") public PlatformTransactionManager postgresqlTransactionManager(@Qualifier("postgresqlEntityManager") EntityManagerFactory entityManagerFactory) { return new JpaTransactionManager(entityManagerFactory); } |
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 :
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 |
package com.roufid.tutoriel.configuration; 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.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.roufid.tutoriel.entity.mysql.Author; /** * Spring configuration of the "mysql" database. * * @author Radouane ROUFID. * */ @Configuration @EnableTransactionManagement @EnableJpaRepositories( entityManagerFactoryRef = "mysqlEntityManager", transactionManagerRef = "mysqlTransactionManager", basePackages = "com.roufid.tutoriel.dao.mysql" ) public class MysqlConfiguration { /** * MySQL datasource definition. * * @return datasource. */ @Bean @ConfigurationProperties(prefix = "spring.mysql.datasource") public DataSource mysqlDataSource() { return DataSourceBuilder .create() .build(); } /** * Entity manager definition. * * @param builder an EntityManagerFactoryBuilder. * @return LocalContainerEntityManagerFactoryBean. */ @Bean(name = "mysqlEntityManager") public LocalContainerEntityManagerFactoryBean mysqlEntityManagerFactory(EntityManagerFactoryBuilder builder) { return builder .dataSource(mysqlDataSource()) .properties(hibernateProperties()) .packages(Author.class) .persistenceUnit("mysqlPU") .build(); } /** * @param entityManagerFactory * @return */ @Bean(name = "mysqlTransactionManager") public PlatformTransactionManager mysqlTransactionManager(@Qualifier("mysqlEntityManager") EntityManagerFactory entityManagerFactory) { return new JpaTransactionManager(entityManagerFactory); } private Map<String, Object> 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<String, Object>(); } } } |
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
|
1 2 3 4 5 6 7 |
@Bean @ConfigurationProperties(prefix = "spring.mysql.datasource") public DataSource mysqlDataSource() { return DataSourceBuilder .create() .build(); } |
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|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
package com.roufid.tutoriel; import org.junit.Assert; import org.junit.Before; import org.junit.Test; import org.junit.runner.RunWith; import org.springframework.beans.factory.annotation.Autowired; import org.springframework.boot.test.context.SpringBootTest; import org.springframework.test.context.junit4.SpringRunner; import com.roufid.tutoriel.dao.mysql.AuthorRepository; import com.roufid.tutoriel.dao.postgresql.BookRepository; import com.roufid.tutoriel.entity.mysql.Author; import com.roufid.tutoriel.entity.postgresql.Book; @RunWith(SpringRunner.class) @SpringBootTest public class ApplicationTest { @Autowired private AuthorRepository authorRepository; @Autowired private BookRepository bookRepository; private Long bookId = 1L; /** * Will be executed before the methods annotated with code @Test. */ @Before public void init() { Author author = new Author(); author.setId(1L); author.setFirstname("Radouane"); author.setLastname("Roufid"); authorRepository.save(author); Book book = new Book(); book.setId(bookId); book.setName("Spring Boot Book"); book.setAuthorId(author.getId()); bookRepository.save(book); } /** * Test should find the author's book from the PostgreSQL database. */ @Test public void testShouldFindAuthor() { Book book = bookRepository.findOne(bookId); Assert.assertNotNull(book); Author author = authorRepository.findOne(book.getId()); Assert.assertNotNull(author); System.out.println("The book " + book.getName() + " was written by " + author.getFirstname() + " " + author.getLastname()); } } |
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
- MySQL
The tutorial source code is available on Github. Download the source
