Configure and Use Multiple DataSources in Spring Boot

1. Overview

The typical scenario for a Spring Boot application is to store data in a single relational database. But we sometimes need to access multiple databases.

In this tutorial, we'll learn how to configure and use multiple data sources with Spring Boot.

To find out how to deal with a single data source, check out our introduction to Spring Data JPA.

2. Default Behavior

Let's remember what declaring a data source in Spring Boot looks like in application.yml:

spring:
  datasource:
    url: ...
    username: ...
    password: ...
    driverClassname: ...

Internally, Spring maps these settings to an instance of org.springframework.boot.autoconfigure.jdbc.DataSourceProperties.

Let's take a look into the implementation:

@ConfigurationProperties(prefix = "spring.datasource")
public class DataSourceProperties implements BeanClassLoaderAware, InitializingBean {

    // ...

    /**
     * Fully qualified name of the JDBC driver. Auto-detected based on the URL by default.
     */
    private String driverClassName;

    /**
     * JDBC URL of the database.
     */
    private String url;

    /**
     * Login username of the database.
     */
    private String username;

    /**
     * Login password of the database.
     */
    private String password;

    // ...

}

We should point out the @ConfigurationProperties annotation that maps the properties of the configuration to the Java object automatically.

3. Extending the Defaults

So, to use multiple data sources, we need to declare multiple beans with different mappings within Spring's application context.

We can do this by using configuration classes:

@Configuration
public class TodoDatasourceConfiguration {

    @Bean
    @ConfigurationProperties("spring.datasource.todos")
    public DataSourceProperties todosDataSourceProperties() {
        return new DataSourceProperties();
    }
}

@Configuration
public class TopicDatasourceConfiguration {

    @Bean
    @ConfigurationProperties("spring.datasource.topics")
    public DataSourceProperties topicsDataSourceProperties() {
        return new DataSourceProperties();
    }

}

The configuration for the data sources must look like this:

spring:
  datasource:
    todos:
      url: ...
      username: ...
      password: ...
      driverClassName: ...
    topics:
      url: ...
      username: ...
      password: ...
      driverClassName: ...

Then we can create the data sources by using the DataSourceProperties objects:

@Bean
public DataSource todosDataSource() {
    return todosDataSourceProperties()
      .initializeDataSourceBuilder()
      .build();
}

@Bean
public DataSource topicsDataSource() {
    return topicsDataSourceProperties()
      .initializeDataSourceBuilder()
      .build();
}

4. Spring Data JDBC

When using Spring Data JDBC, we also need to configure one instance of JdbcTemplate for each DataSource:

@Bean
public JdbcTemplate todosJdbcTemplate(@Qualifier("todosDataSource") DataSource dataSource) {
    return new JdbcTemplate(dataSource);
}

@Bean
public JdbcTemplate topicsJdbcTemplate(@Qualifier("topicsDataSource") DataSource dataSource) {
    return new JdbcTemplate(dataSource);
}

We can then use them also by specifying a @Qualifier:

@Autowired
@Qualifier("topicsJdbcTemplate")
JdbcTemplate jdbcTemplate;

5. Spring Data JPA

When using Spring Data JPA, we want to use repositories like the following, where Todo is the entity:

public interface TodoRepository extends JpaRepository<Todo, Long> {}

So, we need to declare EntityManager factories for each data source:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
  basePackageClasses = Todo.class,
  entityManagerFactoryRef = "todosEntityManagerFactory",
  transactionManagerRef = "todosTransactionManager"
)
public class TodoJpaConfiguration {

    @Bean
    public LocalContainerEntityManagerFactoryBean todosEntityManagerFactory(
      Qualifier("todosDataSource") DataSource dataSource,
      EntityManagerFactoryBuilder builder) {
        return builder
          .dataSource(todosDataSource())
          .packages(Todo.class)
          .build();
    }

    @Bean
    public PlatformTransactionManager todosTransactionManager(
      @Qualifier("todosEntityManagerFactory") LocalContainerEntityManagerFactoryBean todosEntityManagerFactory) {
        return new JpaTransactionManager(Objects.requireNonNull(todosEntityManagerFactory.getObject()));
    }

}

Let's look at a few restrictions that we should be aware of.

We need to split the packages to allow one @EnableJpaRepositories for each data source.

Unfortunately, to get EntityManagerFactoryBuilder injected, we need to declare one of the data sources as @Primary.

This is because EntityManagerFactoryBuilder is declared in org.springframework.boot.autoconfigure.orm.jpa.JpaBaseConfiguration, and this class needs a single data source injected. Usually, some parts of the framework might not expect multiple data sources configured.

6. Configure Hikari Connection Pool

If we want to configure Hikari, we just need to add an @ConfigurationProperties to the data source definition:

@Bean
@ConfigurationProperties("spring.datasource.todos.hikari")
public DataSource todosDataSource() {
    return todosDataSourceProperties()
      .initializeDataSourceBuilder()
      .build();
}

Then we can insert the following lines into the application.properties file:

spring.datasource.todos.hikari.connectionTimeout=30000 
spring.datasource.todos.hikari.idleTimeout=600000 
spring.datasource.todos.hikari.maxLifetime=1800000 

7. Conclusion

In this article, we learned how to configure multiple data sources with Spring Boot.

We saw that we need some configuration and that there might be pitfalls when deviating from the standard but that it is possible in the end.

As always, all the code is available over on GitHub.

res – Persistence (eBook) (cat=Persistence)

An intro to Spring Data, JPA
and Transaction Semantics Details with JPA

Get Persistence Right with Spring

Download the E-book