Two databases used by one Spring Boot application does not seem tricky. But they both are accessed through Spring Data JDBC. And then the most tutorials remains tight-lipped. Even though that is not straight forward but still possible.
Spring framework saves a lot of our time. We can achieve much with a little effort. That is so because many repeatable pieces of code is already added by Spring. The pieces that we would have to type into all (ok, most) our applications if we did not take advantage of Spring. Lucky us, hah?
The problem starts when you want to do something unobvious, non-standard, something one-of-a-kind. A few years ago I wrote a tutorial that explained how to integrate with two OAuth2 providers. Then, I had to fallback from using the standard Spring configuration and to start overriding Spring classes.
This time, it will be similar.
Two datasources? Why?!
Oh, I am not going to spend much time on that. I am sure you can easily imagine a system that needs data from two different sources, databases in this case. A good example is a banking software that keeps financial data in one database, texts translations in another one, users credentials in the third one, stock quotes in the forth one etc.
Of course, instead of reaching four databases from one application, you may prefer implementing four separate services and have the fith one talking to them over REST API. That might be a good idea, but not always. Sometimes you just need two or more datasources in one application. You probably would not read this article if you did not have such case, right?
So let's leave the justifications behind and focus on achieving the goal.
Two databases
The base of the setup are two databases. I have employees and users. Don't ask me why such names. They are just examples.
For simplicity, I put both on the same MySQL instance but that does not change much. The configuration from this article will work even if you have databases on two different physical machines.
Separate DAO packages for each datasource
As I use Spring JDBC, I will create entities and Spring repositories. It is important to split them to separate packages. Don't put repositories from both datasources to the same package.
I put all entities and repositories for employees database to one package. And everything for users database to the second package.
The code has no magic. A sample department entity:
package com.dbapresents.twodatasources.dao.employees.entities;
import org.springframework.data.annotation.Id;
import org.springframework.data.relational.core.mapping.Column;
import org.springframework.data.relational.core.mapping.Table;
@Table("departments")
public class Department {
@Id
@Column("dept_no")
private String deptNo;
@Column("dept_name")
private String deptName;
public String getDeptNo() {
return deptNo;
}
public void setDeptNo(String deptNo) {
this.deptNo = deptNo;
}
public String getDeptName() {
return deptName;
}
public void setDeptName(String deptName) {
this.deptName = deptName;
}
}
A sample DepartmentRepository:
package com.dbapresents.twodatasources.dao.employees;
import com.dbapresents.twodatasources.dao.employees.entities.Department;
import org.springframework.data.repository.CrudRepository;
public interface DepartmentRepository extends CrudRepository<Department, String> {
}
Classes for the second database: Account and AccountRepository are similar. As you can see I still do not point any of those classes to a particular datasource. Hmm ... I still don't even have a datasource.
Two datasources configuration
At some point it is necessary to configure datasources. If I had only one, I could do that in application.properties file by adding properties with prefix spring.datasource. Spring would automatically instantiate an appropriate datasource object for me.
Having two datasources complicates the thing but only a little bit.
I put configuration of both datasource to application.properties file.
spring.datasource.jdbc-url=jdbc:mysql://localhost:3306/employees
spring.datasource.username=root
spring.datasource.password=password
spring.users-datasource.jdbc-url=jdbc:mysql://localhost:3306/users
spring.users-datasource.username=root
spring.users-datasource.password=password
I cannot use the same prefix (spring.datasource.) for both of them so use a custom prefix for the second datasource.
Custom datasources setup
As I used a custom prefix for the second datasource in application.properties file, I have to make some manual configuration to tell Spring what these properties actually are. That is the trickiest part.
I create a Spring configuration class one per each datasource. The first one - employees looks like this (PersistenceEmployeesConfiguration.java)
package com.dbapresents.twodatasources.config;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.data.jdbc.JdbcRepositoriesAutoConfiguration;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.data.jdbc.repository.config.EnableJdbcRepositories;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcOperations;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.JdbcTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import javax.sql.DataSource;
@Configuration
@EnableJdbcRepositories(
basePackages = "com.dbapresents.twodatasources.dao.employees",
transactionManagerRef = "employeesTransactionManager",
jdbcOperationsRef = "employeesJdbcOperations"
)
@EnableAutoConfiguration(exclude = {
DataSourceAutoConfiguration.class,
JdbcRepositoriesAutoConfiguration.class
})
public class PersistenceEmployeesConfiguration {
@Bean
@ConfigurationProperties(prefix="spring.datasource")
DataSource employeesDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "employeesTransactionManager")
PlatformTransactionManager employeesTransactionManager(@Qualifier("employeesDataSource") DataSource employeesDataSource) {
return new JdbcTransactionManager(employeesDataSource);
}
@Bean
NamedParameterJdbcOperations employeesJdbcOperations(@Qualifier("employeesDataSource") DataSource dataSource) {
return new NamedParameterJdbcTemplate(dataSource);
}
}
Using basePackages attribute I point the datasource to the package with DAO for employees datasource. That is why I insisted on separating repositories for each database to different packages.
Datasource bean creation method has a custom prefix attribute. It is set to spring.datasource which means that Spring should look for spring.datasource.* properties (in application.properties) to find url, credentials and other settings for employeesDataSource.
The second configuration - PersistenceUsersConfiguration is very similar: basePackages points to the package, prefix links properties with the database settings.
package com.dbapresents.twodatasources.config;
import org.springframework.beans.factory.annotation.Qualifier;
import org.springframework.boot.autoconfigure.EnableAutoConfiguration;
import org.springframework.boot.autoconfigure.data.jdbc.JdbcRepositoriesAutoConfiguration;
import org.springframework.boot.autoconfigure.jdbc.DataSourceAutoConfiguration;
import org.springframework.boot.context.properties.ConfigurationProperties;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Lazy;
import org.springframework.data.jdbc.core.convert.*;
import org.springframework.data.jdbc.core.mapping.JdbcMappingContext;
import org.springframework.data.jdbc.repository.config.EnableJdbcRepositories;
import org.springframework.data.relational.core.dialect.Dialect;
import org.springframework.data.relational.core.dialect.MySqlDialect;
import org.springframework.data.relational.core.mapping.NamingStrategy;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcOperations;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.support.JdbcTransactionManager;
import org.springframework.transaction.PlatformTransactionManager;
import javax.sql.DataSource;
import java.util.Optional;
@Configuration
@EnableJdbcRepositories(
basePackages = "com.dbapresents.twodatasources.dao.users",
transactionManagerRef = "usersTransactionManager",
jdbcOperationsRef = "usersJdbcOperations"
)
@EnableAutoConfiguration(exclude = {
DataSourceAutoConfiguration.class,
JdbcRepositoriesAutoConfiguration.class
})
public class PersistenceUsersConfiguration {
@Bean
@ConfigurationProperties(prefix="spring.users-datasource")
DataSource userDataSource() {
return DataSourceBuilder.create().build();
}
@Bean(name = "usersTransactionManager")
PlatformTransactionManager usersTransactionManager(@Qualifier("userDataSource") DataSource userDataSource) {
return new JdbcTransactionManager(userDataSource);
}
@Bean
NamedParameterJdbcOperations usersJdbcOperations(@Qualifier("userDataSource") DataSource dataSource) {
return new NamedParameterJdbcTemplate(dataSource);
}
@Bean
Dialect jdbcDialect() {
return MySqlDialect.INSTANCE;
}
@Bean
JdbcCustomConversions customConversions() {
return new JdbcCustomConversions();
}
@Bean
JdbcMappingContext jdbcMappingContext(Optional<NamingStrategy> namingStrategy, JdbcCustomConversions customConversions) {
JdbcMappingContext mappingContext = new JdbcMappingContext((NamingStrategy)namingStrategy.orElse(NamingStrategy.INSTANCE));
mappingContext.setSimpleTypeHolder(customConversions.getSimpleTypeHolder());
return mappingContext;
}
@Bean
JdbcConverter jdbcConverter(JdbcMappingContext mappingContext,
@Qualifier("usersJdbcOperations") NamedParameterJdbcOperations jdbcOperationsDataBase1,
@Lazy RelationResolver relationResolver,
JdbcCustomConversions conversions,
Dialect dialect) {
DefaultJdbcTypeFactory jdbcTypeFactory = new DefaultJdbcTypeFactory(jdbcOperationsDataBase1.getJdbcOperations());
return new BasicJdbcConverter(mappingContext, relationResolver, conversions, jdbcTypeFactory,
dialect.getIdentifierProcessing());
}
}
An important elements are jdbcDialect, customConversions, jdbcMappingContex and jdbcConverter. Those beans have to exist in Spring JDBC application but they can be shared among both datasources. That is why I defined them only in one configuration class.
Testing two datasources
It is time to test the whole setup if both repositories send queries to proper databases. I do that by such test.
package com.dbapresents.twodatasources.dao;
import com.dbapresents.twodatasources.dao.employees.DepartmentRepository;
import com.dbapresents.twodatasources.dao.users.AccountRepository;
import org.junit.jupiter.api.Test;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.test.context.SpringBootTest;
@SpringBootTest
class TwoDatasourcesTest {
private final DepartmentRepository departmentRepository;
private final AccountRepository accountRepository;
@Autowired
public TwoDatasourcesTest(DepartmentRepository departmentRepository, AccountRepository accountRepository) {
this.departmentRepository = departmentRepository;
this.accountRepository = accountRepository;
}
@Test
void test() {
departmentRepository.findById("abcd");
accountRepository.findById(2);
}
}
test() method queries departments table from the first database and accounts table from the second database.