How to Configure Multiple Data Source in Spring Boot
Overview
In my previous post, I wrote an article about configuring data source by using Hikari CP. However, there are times that we need more than one database connection in an application. Later in this post, I will show how to configure multiple data source by using spring boot.
Use Case
Recently, I have a requirement for my project to connect into two databases. The first connection goes to postgresql and the other one connects to mysql. Since I am a huge fan of spring boot with all its simplicity, I urged myself to stick with this option.
For single data source, it is very easy to configure. Just by mentioning all the connection properties in application.yml, everything just run as it is. But when you need more than one, you need more than just declare in properties file.
Step by step
The pom.xml file is no different with the previous post, you only need to add mysql library inside it.
Step 1: application.yml
Create a custom key properties for each data source.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
postgresql: datasource: jdbc-url: jdbc:postgresql://server1:5432/db_1 username: user password: password driver-class-name: org.postgresql.Driver connection-test-query: SELECT 1 mysql: datasource: jdbc-url: jdbc:mysql://server2/db_2 username: user password: password driver-class-name: com.mysql.jdbc.Driver connection-test-query: SELECT 1 |
Step 2: Configure Data Source
Next step is to create a Bean Configuration for each data source.
|
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 |
@Configuration @ConfigurationProperties(prefix = "postgre.datasource") @EnableTransactionManagement @EnableJpaRepositories( entityManagerFactoryRef = "postgreEntityManagerFactory", transactionManagerRef = "postgreTransactionManager", basePackages = "com.rurocker.repository.postgre") public class PostgreDataSourceConfig extends HikariConfig { @Bean(name = "postgreDataSource") @Primary public DataSource dataSource() { return new HikariDataSource(this); } @Bean(name = "postgreEntityManagerFactory") @Primary public LocalContainerEntityManagerFactoryBean postgreEntityManagerFactory(final EntityManagerFactoryBuilder builder, @Qualifier("postgreDataSource") final DataSource dataSource) { final LocalContainerEntityManagerFactoryBean entityManagerFactoryBean = new LocalContainerEntityManagerFactoryBean(); entityManagerFactoryBean.setJpaVendorAdapter(this.vendorAdaptor()); entityManagerFactoryBean.setDataSource(dataSource); entityManagerFactoryBean.setPersistenceProviderClass(HibernatePersistenceProvider.class); entityManagerFactoryBean.setPersistenceUnitName("postgre"); entityManagerFactoryBean.setPackagesToScan("com.rurocker.model.postgre"); entityManagerFactoryBean.setJpaProperties(this.jpaHibernateProperties()); entityManagerFactoryBean.afterPropertiesSet(); return entityManagerFactoryBean; } @Bean(name = "postgreTransactionManager") @Primary public PlatformTransactionManager postgreTransactionManager( @Qualifier("postgreEntityManagerFactory") final EntityManagerFactory emf) { return new JpaTransactionManager(emf); } private HibernateJpaVendorAdapter vendorAdaptor() { final HibernateJpaVendorAdapter vendorAdapter = new HibernateJpaVendorAdapter(); // put all the adapter properties here, such as show sql return vendorAdapter; } private Properties jpaHibernateProperties() { final Properties properties = new Properties(); // put all required jpa propeties here return properties; } } |
Take a notice in the highlighted part. First the @ConfigurationProperties annotation. The prefix is refer to the application.yml configuration (See postgres->datasource declaration). Another one is a basePackages property inside @EnableJpaRepositories annotation. This property is pointed to the postgresql repository package in your project structure. One ore thing is to define where my database models are located. The models are defined when I setup the EntityManagerFactory (See line 25).
Next do the same with mysql data source configuration. The main difference for mysql bean configuration is removing the @Primary annotation for each method declaration.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
@Configuration @ConfigurationProperties(prefix = "mysql.datasource") @EnableTransactionManagement @EnableJpaRepositories( entityManagerFactoryRef = "mysqlEntityManagerFactory", transactionManagerRef = "mysqlTransactionManager", basePackages = "com.rurocker.repository.mysql") public class MySqlDataSourceConfig extends HikariConfig { @Bean(name = "mysqlDataSource") public DataSource dataSource() { return new HikariDataSource(this); } @Bean(name = "mysqlEntityManagerFactory") public LocalContainerEntityManagerFactoryBean mysqlEntityManagerFactory(final EntityManagerFactoryBuilder builder, @Qualifier("mysqlDataSource") final DataSource dataSource) { // remove for clarity } /* the rest is similar with postgre part */ } |
Step 4: Models and Repository
The last part is to create database models and spring data repository for each data source. For instance, in this article the postgresql will have the models class under com.rurocker.model.postgre and mysql under com.rurocker.model.mysql. And for the repository interfaces and/or classes, postgresql will be located under com.rurocker.repository.postgre meanwhile mysql will be under com.rurocker.repository.mysql.
And that’s it. You can execute mvn spring-boot:run to make it up and running.
Conclusion
Well, maybe it is not as simple as spring boot is meant to be. But in my opinion, this is still acceptable because the complexity is still low. Take note, this example does not cover distributed transaction for multiple data source. So careful must be taken when using this example.
That’s all for me right now. If you guys have found simpler and easier approach, I really would like to know and we can share each other.
Hi ru-rocker..It’s working fine in JPA. But i want to connect jdbctemplate how can i configure please give me the solution step by step
Simplest way still with yml file
Spring boot : 2.0.5-RELEASE
JDK version : 1.8.0_111
Database : MySQL
@ConfigurationProperties(prefix = “spring.datasource.hikari”)
@Bean
public HikariConfig hikariConfig() {
HikariConfig hikariConfig = new HikariConfig();
return hikariConfig;
}
@Bean(name = “prodDataSource”)
@Qualifier(“prodDataSource”)
@Primary
@ConfigurationProperties(prefix = “spring.datasource.prod”)
public DataSource primaryDataSource() {
DataSource ds = DataSourceBuilder.create().build();
if (ds instanceof HikariDataSource) {
hikariConfig.setPoolName(“Hikari – prodPool”);
HikariDataSource hikariDs = (HikariDataSource)ds;
hikariConfig.copyStateTo(hikariDs);
return hikariDs;
}
return ds;
}
@Bean(name = “devDataSource”)
@Qualifier(“devDataSource”)
@ConfigurationProperties(prefix = “spring.datasource.dev”)
public DataSource secondaryDataSource() {
DataSource ds = DataSourceBuilder.create().build();
if (ds instanceof HikariDataSource) {
hikariConfig.setPoolName(“Hikari – devPool”);
HikariDataSource hikariDs = (HikariDataSource) ds;
hikariConfig.copyStateTo(hikariDs);
return hikariDs;
}
return ds;
}
Hi I am using spring boot flyway and have a application.yaml as below. when i run flyway it is picking only the last set of placeholders and ignoring the first sets. can u help me on this
spring:
flyway:
placeholders:
SCH_NME_ARG: ‘gk’
DB_NME_ARG: ‘GKI11’
VERSION_ARG: ‘5.6.0’
PRJ_ROOT_ARG: ‘/opt’
NFS_PATH_ARG: ‘/usr/local’
—
spring:
flyway:
placeholders:
SCH_NME_ARG: ‘gktest’
DB_NME_ARG: ‘GKtest11’
VERSION_ARG: ‘5.6.0’
PRJ_ROOT_ARG: ‘/opt’
NFS_PATH_ARG: ‘/usr/local’
—