Spring Boot, Spring Data JPA with multiple DataSources
There is another way to have multiple dataSources by using @EnableAutoConfiguration and application.properties.
Basically put multiple dataSource configuration info on application.properties and generate default setup (dataSource and entityManagerFactory) automatically for first dataSource by @EnableAutoConfiguration. But for next dataSource, create dataSource, entityManagerFactory and transactionManager all manually by the info from property file.
Below is my example to setup two dataSources. First dataSource is setup by @EnableAutoConfiguration which can be assigned only for one configuration, not multiple. And that will generate 'transactionManager' by DataSourceTransactionManager, that looks default transactionManager generated by the annotation. However I have seen the transaction not beginning issue on the thread from scheduled thread pool only for the default DataSourceTransactionManager and also when there are multiple transaction managers. So I create transactionManager manually by JpaTransactionManager also for the first dataSource with assigning 'transactionManager' bean name and default entityManagerFactory. That JpaTransactionManager for first dataSource surely resolves the weird transaction issue on the thread from ScheduledThreadPool.
Update for Spring Boot 1.3.0.RELEASE
I found my previous configuration with @EnableAutoConfiguration for default dataSource has issue on finding entityManagerFactory with Spring Boot 1.3 version. Maybe default entityManagerFactory is not generated by @EnableAutoConfiguration, once after I introduce my own transactionManager. So now I create entityManagerFactory by myself. So I don't need to use @EntityScan. So it looks I'm getting more and more out of the setup by @EnableAutoConfiguration.
Second dataSource is setup without @EnableAutoConfiguration and create 'anotherTransactionManager' by manual way.
Since there are multiple transactionManager extends from PlatformTransactionManager, we should specify which transactionManager to use on each @Transactional annotation
Default Repository Config
@Configuration
@EnableTransactionManagement
@EnableAutoConfiguration
@EnableJpaRepositories(
entityManagerFactoryRef = "entityManagerFactory",
transactionManagerRef = "transactionManager",
basePackages = {"com.mysource.repository"})
public class RepositoryConfig {
@Autowired
JpaVendorAdapter jpaVendorAdapter;
@Autowired
DataSource dataSource;
@Bean(name = "entityManager")
public EntityManager entityManager() {
return entityManagerFactory().createEntityManager();
}
@Primary
@Bean(name = "entityManagerFactory")
public EntityManagerFactory entityManagerFactory() {
LocalContainerEntityManagerFactoryBean emf = new LocalContainerEntityManagerFactoryBean();
emf.setDataSource(dataSource);
emf.setJpaVendorAdapter(jpaVendorAdapter);
emf.setPackagesToScan("com.mysource.model");
emf.setPersistenceUnitName("default"); // <- giving 'default' as name
emf.afterPropertiesSet();
return emf.getObject();
}
@Bean(name = "transactionManager")
public PlatformTransactionManager transactionManager() {
JpaTransactionManager tm = new JpaTransactionManager();
tm.setEntityManagerFactory(entityManagerFactory());
return tm;
}
}
Another Repository Config
@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
entityManagerFactoryRef = "anotherEntityManagerFactory",
transactionManagerRef = "anotherTransactionManager",
basePackages = {"com.mysource.anothersource.repository"})
public class AnotherRepositoryConfig {
@Autowired
JpaVendorAdapter jpaVendorAdapter;
@Value("${another.datasource.url}")
private String databaseUrl;
@Value("${another.datasource.username}")
private String username;
@Value("${another.datasource.password}")
private String password;
@Value("${another.dataource.driverClassName}")
private String driverClassName;
@Value("${another.datasource.hibernate.dialect}")
private String dialect;
public DataSource dataSource() {
DriverManagerDataSource dataSource = new DriverManagerDataSource(databaseUrl, username, password);
dataSource.setDriverClassName(driverClassName);
return dataSource;
}
@Bean(name = "anotherEntityManager")
public EntityManager entityManager() {
return entityManagerFactory().createEntityManager();
}
@Bean(name = "anotherEntityManagerFactory")
public EntityManagerFactory entityManagerFactory() {
Properties properties = new Properties();
properties.setProperty("hibernate.dialect", dialect);
LocalContainerEntityManagerFactoryBean emf = new LocalContainerEntityManagerFactoryBean();
emf.setDataSource(dataSource());
emf.setJpaVendorAdapter(jpaVendorAdapter);
emf.setPackagesToScan("com.mysource.anothersource.model"); // <- package for entities
emf.setPersistenceUnitName("anotherPersistenceUnit");
emf.setJpaProperties(properties);
emf.afterPropertiesSet();
return emf.getObject();
}
@Bean(name = "anotherTransactionManager")
public PlatformTransactionManager transactionManager() {
return new JpaTransactionManager(entityManagerFactory());
}
}
application.properties
# database configuration
spring.datasource.url=jdbc:h2:file:~/main-source;AUTO_SERVER=TRUE
spring.datasource.username=sa
spring.datasource.password=
spring.datasource.driver-class-name=org.h2.Driver
spring.datasource.continueOnError=true
spring.datasource.initialize=false
# another database configuration
another.datasource.url=jdbc:sqlserver://localhost:1433;DatabaseName=another;
another.datasource.username=username
another.datasource.password=
another.datasource.hibernate.dialect=org.hibernate.dialect.SQLServer2008Dialect
another.datasource.driverClassName=com.microsoft.sqlserver.jdbc.SQLServerDriver
Choose proper transactionManager for @Transactional annotation
Service for first datasource
@Service("mainService")
@Transactional("transactionManager")
public class DefaultDataSourceServiceImpl implements DefaultDataSourceService
{
//
}
Service for another datasource
@Service("anotherService")
@Transactional("anotherTransactionManager")
public class AnotherDataSourceServiceImpl implements AnotherDataSourceService
{
//
}
here is my solution. base on spring-boot.1.2.5.RELEASE.
application.properties
first.datasource.driver-class-name=com.mysql.jdbc.Driver
first.datasource.url=jdbc:mysql://127.0.0.1:3306/test
first.datasource.username=
first.datasource.password=
first.datasource.validation-query=select 1
second.datasource.driver-class-name=com.mysql.jdbc.Driver
second.datasource.url=jdbc:mysql://127.0.0.1:3306/test2
second.datasource.username=
second.datasource.password=
second.datasource.validation-query=select 1
DataSourceConfig.java
@Configuration
public class DataSourceConfig {
@Bean
@Primary
@ConfigurationProperties(prefix="first.datasource")
public DataSource firstDataSource() {
DataSource ds = DataSourceBuilder.create().build();
return ds;
}
@Bean
@ConfigurationProperties(prefix="second.datasource")
public DataSource secondDataSource() {
DataSource ds = DataSourceBuilder.create().build();
return ds;
}
}
I checked the source code you provided on GitHub. There were several mistakes / typos in the configuration.
In CustomerDbConfig / OrderDbConfig you should refer to customerEntityManager and packages should point at existing packages:
@Configuration
@EnableJpaRepositories(
entityManagerFactoryRef = "customerEntityManager",
transactionManagerRef = "customerTransactionManager",
basePackages = {"com.mm.boot.multidb.repository.customer"})
public class CustomerDbConfig {
The packages to scan in customerEntityManager and orderEntityManager were both not pointing at proper package:
em.setPackagesToScan("com.mm.boot.multidb.model.customer");
Also the injection of proper EntityManagerFactory did not work. It should be:
@Bean(name = "customerTransactionManager")
public PlatformTransactionManager transactionManager(EntityManagerFactory customerEntityManager){
}
The above was causing the issue and the exception. While providing the name in a @Bean method you are sure you get proper EMF injected.
The last thing I have done was to disable to automatic configuration of JpaRepositories:
@EnableAutoConfiguration(exclude = JpaRepositoriesAutoConfiguration.class)
And with all fixes the application starts as you probably expect!
thanks to the answers of Steve Park and Rafal Borowiec I got my code working, however, I had one issue: the DriverManagerDataSource is a "simple" implementation and does NOT give you a ConnectionPool (check http://docs.spring.io/spring/docs/current/javadoc-api/org/springframework/jdbc/datasource/DriverManagerDataSource.html).
Hence, I replaced the functions which returns the DataSource for the secondDB to.
public DataSource <secondaryDB>DataSource() {
// use DataSourceBuilder and NOT DriverManagerDataSource
// as this would NOT give you ConnectionPool
DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create();
dataSourceBuilder.url(databaseUrl);
dataSourceBuilder.username(username);
dataSourceBuilder.password(password);
dataSourceBuilder.driverClassName(driverClassName);
return dataSourceBuilder.build();
}
Also, if do you not need the EntityManager as such, you can remove both the entityManager() and the @Bean annotation.
Plus, you may want to remove the basePackages annotation of your configuration class: maintaining it with the factoryBean.setPackagesToScan() call is sufficient.
I have written a complete article at Spring Boot JPA Multiple Data Sources Example. In this article, we will learn how to configure multiple data sources and connect to multiple databases in a typical Spring Boot web application. We will use Spring Boot 2.0.5, JPA, Hibernate 5, Thymeleaf and H2 database to build a simple Spring Boot multiple data sources web application.