Multiple databases with Spring Boot and Spring Data JPA
Connecting a Spring Boot application to two separate databases with Spring Data JPA, working around Boot's default autowiring behaviour.
A little while back I knocked up a post describing how to enable a Spring application to connect to multiple data sources. At the time, I had only just heard about Spring Boot at the SpringOne 2GX conference in Santa Clara, so the examples didn’t take advantage of that and also didn’t work around some of the autowiring that it does.
Recently, I was working on a little ETL project to migrate data from one database to another with a different structure, so I returned to this problem and the following is the result.
First, if you want to get hold of a working (including some simple tests) example project, here it is:
https://github.com/gratiartis/multids-demo/tree/now-with-spring-boot
As previously, when you define an entity manager, you can define where it should scan for entities and repository classes. The classes can be named individually, but it is easiest if you put your domain entities and repository classes into their own packages and point the entity manager factory at the package. In this example, I used:
com.sctrcd.multids.foo.domain
com.sctrcd.multids.foo.repo
com.sctrcd.multids.bar.domain
com.sctrcd.multids.bar.repo
I suspect that it’s certainly possible to get around it, but I found that due to Spring Boot trying to inject beans
based on default names, it was easiest to set up one of the data sources to use the defaults and the other to use bean
names that I defined. As you can see in the application.yml below:
spring:
datasource:
url: jdbc:mysql://localhost/foo_schema
username: root
password: d4t4b4s3sForLif3
driverClassName: com.mysql.jdbc.Driver
test-on-borrow: true
test-while-idle: true
validation-query: select 1;
maxActive: 1
jpa:
show-sql: false
generate-ddl: false
properties:
hibernate:
dialect: org.hibernate.dialect.MySQL5InnoDBDialect
ddl-auto: validate
hbm2ddl:
import_files:
bar:
datasource:
url: jdbc:mysql://localhost/bar_schema
username: root
password: d4t4b4s3sForLif3
driverClassName: com.mysql.jdbc.Driver
test-on-borrow: true
test-while-idle: true
validation-query: select 1;
maxActive: 1… the spring.datasource.url, spring.datasource.username and spring.datasource.password properties are all defined
for the ‘default’ datasource. I define some additional non-conventional properties for the additional schema. We will
see how those are picked up shortly.
Beyond the application.yml configuration, all we need to do is define @Configuration beans which will pick up the
properties. First, a @Configuration to wire up the ‘default’ data source. This defines each bean as @Primary, to
ensure that they are the beans picked up by anything which does not specify a @Qualifier:
package com.sctrcd.multidsdemo;
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.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;
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
entityManagerFactoryRef = "entityManagerFactory",
basePackages = { "com.sctrcd.multidsdemo.foo.repo" })
public class FooConfig {
@Primary
@Bean(name = "dataSource")
@ConfigurationProperties(prefix="spring.datasource")
public DataSource dataSource() {
return DataSourceBuilder.create().build();
}
@Primary
@Bean(name = "entityManagerFactory")
public LocalContainerEntityManagerFactoryBean entityManagerFactory(
EntityManagerFactoryBuilder builder,
@Qualifier("dataSource") DataSource dataSource) {
return builder
.dataSource(dataSource)
.packages("com.sctrcd.multidsdemo.foo.domain")
.persistenceUnit("foo")
.build();
}
@Primary
@Bean(name = "transactionManager")
public PlatformTransactionManager transactionManager(
@Qualifier("entityManagerFactory") EntityManagerFactory entityManagerFactory) {
return new JpaTransactionManager(entityManagerFactory);
}
}Second a @Configuration to wire up the additional datasource. It is essentially identical to the ‘default’
configuration, except that it defines non-conventional names for the data source, entity manager factory and transaction
manager and scans different packages for the entities and repositories. It also defines the named transaction manager in
the @EnableJpaRepositories annotation and does not define the beans as @Primary.
package com.sctrcd.multidsdemo;
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.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;
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
entityManagerFactoryRef = "barEntityManagerFactory",
transactionManagerRef = "barTransactionManager",
basePackages = { "com.sctrcd.multidsdemo.bar.repo" })
public class BarConfig {
@Bean(name = "barDataSource")
@ConfigurationProperties(prefix="bar.datasource")
public DataSource barDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "barEntityManagerFactory")
public LocalContainerEntityManagerFactoryBean barEntityManagerFactory(
EntityManagerFactoryBuilder builder,
@Qualifier("barDataSource") DataSource barDataSource) {
return builder
.dataSource(barDataSource)
.packages("com.sctrcd.multidsdemo.bar.domain")
.persistenceUnit("bar")
.build();
}
@Bean(name = "barTransactionManager")
public PlatformTransactionManager barTransactionManager(
@Qualifier("barEntityManagerFactory") EntityManagerFactory barEntityManagerFactory) {
return new JpaTransactionManager(barEntityManagerFactory);
}
}Beyond those configuration classes, everything is just the standard setup for a Spring Boot / Spring Data JPA application, so if you have an application connecting to a single database already, there isn’t a lot of modification to support connecting to additional databases.