Spring Boot configure and use two data sources

Spring Boot

data sources

configuration

Java

tutorial

Spring Boot configure and use two data sources

Interview Questions practice on Codemia

Over 8,000 real interview questions from top companies, searchable by company and role.

Browse interview questions

Java

Spring Boot is a powerful framework that simplifies the setup of a Spring application by reducing boilerplate code and providing defaults for a quick start. One of the advanced features of Spring Boot is its support for multiple data sources, enabling applications to connect to different databases simultaneously. This article explains how to configure and use multiple data sources in a Spring Boot application.

Understanding Multiple Data Sources

In enterprise applications, it is common to use multiple data sources. For instance, an application may need to interact with both a relational database and a NoSQL database. Spring Boot facilitates this by allowing configuration of multiple data sources within the same application context.

Configuration Steps

To configure and use two data sources in a Spring Boot application, follow these steps:

Step 1: Add Dependencies

In your pom.xml (for Maven users) or build.gradle (for Gradle users), add the necessary dependencies for the databases you plan to use. Here is an example for a Maven project using MySQL and PostgreSQL:

xml

1<dependencies>
2    <!-- MySQL datasource -->
3    <dependency>
4        <groupId>mysql</groupId>
5        <artifactId>mysql-connector-java</artifactId>
6    </dependency>
7    <!-- PostgreSQL datasource -->
8    <dependency>
9        <groupId>org.postgresql</groupId>
10        <artifactId>postgresql</artifactId>
11    </dependency>
12    <!-- Spring Boot Starter Data JPA -->
13    <dependency>
14        <groupId>org.springframework.boot</groupId>
15        <artifactId>spring-boot-starter-data-jpa</artifactId>
16    </dependency>
17</dependencies>

Step 2: Configure Data Sources

In application.properties or application.yml, specify the configurations for both data sources. For example, configuring two data sources in a properties file might look like this:

properties

1# MySQL datasource configuration
2spring.datasource.url=jdbc:mysql://localhost:3306/mydb1
3spring.datasource.username=myuser1
4spring.datasource.password=mypassword1
5spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver
6
7# PostgreSQL datasource configuration
8second.datasource.url=jdbc:postgresql://localhost:5432/mydb2
9second.datasource.username=myuser2
10second.datasource.password=mypassword2
11second.datasource.driver-class-name=org.postgresql.Driver

Step 3: Configure Data Source Beans

In your Spring Boot application, define the necessary beans for both data sources and their associated entity managers and transaction managers:

java

1@Configuration
2public class DataSourceConfig {
3
4    @Bean(name = "mySqlDataSource")
5    @ConfigurationProperties(prefix = "spring.datasource")
6    public DataSource mySqlDataSource() {
7        return DataSourceBuilder.create().build();
8    }
9
10    @Bean(name = "postgreDataSource")
11    @ConfigurationProperties(prefix = "second.datasource")
12    public DataSource postgreDataSource() {
13        return DataSourceBuilder.create().build();
14    }
15
16    @Bean(name = "entityManagerFactory1")
17    public LocalContainerEntityManagerFactoryBean entityManagerFactory1(
18        EntityManagerFactoryBuilder builder,
19        @Qualifier("mySqlDataSource") DataSource mySqlDataSource) {
20        return builder
21            .dataSource(mySqlDataSource)
22            .packages("com.example.entity1")
23            .persistenceUnit("mydb1")
24            .build();
25    }
26
27    @Bean(name = "entityManagerFactory2")
28    public LocalContainerEntityManagerFactoryBean entityManagerFactory2(
29        EntityManagerFactoryBuilder builder,
30        @Qualifier("postgreDataSource") DataSource postgreDataSource) {
31        return builder
32            .dataSource(postgreDataSource)
33            .packages("com.example.entity2")
34            .persistenceUnit("mydb2")
35            .build();
36    }
37
38    @Bean(name = "transactionManager1")
39    public PlatformTransactionManager transactionManager1(
40        @Qualifier("entityManagerFactory1") EntityManagerFactory entityManagerFactory1) {
41        return new JpaTransactionManager(entityManagerFactory1);
42    }
43
44    @Bean(name = "transactionManager2")
45    public PlatformTransactionManager transactionManager2(
46        @Qualifier("entityManagerFactory2") EntityManagerFactory entityManagerFactory2) {
47        return new JpaTransactionManager(entityManagerFactory2);
48    }
49}

Step 4: Access the Data Sources

Use the @Qualifier annotation in your repositories to specify which data source bean to use:

java

1@Repository
2@Transactional(transactionManager = "transactionManager1")
3public interface Entity1Repository extends JpaRepository<Entity1, Long> {
4    // Repository methods
5}
6
7@Repository
8@Transactional(transactionManager = "transactionManager2")
9public interface Entity2Repository extends JpaRepository<Entity2, Long> {
10    // Repository methods
11}

Key Points Summary

Configuration Aspect	MySQL	PostgreSQL
Dependency	`mysql-connector-java`	`postgresql`
URL Property	`spring.datasource.url`	`second.datasource.url`
Username Property	`spring.datasource.username`	`second.datasource.username`
Password Property	`spring.datasource.password`	`second.datasource.password`
Driver Class Name	`com.mysql.cj.jdbc.Driver`	`org.postgresql.Driver`
DataSource Bean Name	`mySqlDataSource`	`postgreDataSource`
Entity Manager Factory Bean	`entityManagerFactory1`	`entityManagerFactory2`
Transaction Manager Bean Name	`transactionManager1`	`transactionManager2`

Additional Considerations

Transaction Management: Be mindful of how transactions are managed across data sources. Ensure that your transactions are appropriately annotated to use the correct transaction manager.
Database-specific Queries: When working with multiple databases, remember that their SQL dialects may differ. Ensure that any database-specific queries are correctly formatted for the specific database.
Performance Considerations: Each data source connection involves resource consumption. Proper connection pooling and resource management strategies should be employed to ensure optimal performance.
Security and Credentials: Secure your data source configurations using environment variables or secret management solutions to avoid exposure of sensitive information in configuration files.

Configuring and using multiple data sources in a Spring Boot application involves setting up separate configurations, defining distinct entity managers and transaction managers, and correctly annotating repositories. Understanding these components is crucial in effectively building scalable, reliable, and maintainable applications.

OOD Fundamentals

Master object-oriented design from first principles, SOLID, design patterns, and classic interview problems with hands-on coding.

View the course

Track what you have practised

A free account saves your progress, solutions and study plan across every problem on Codemia.

Interview Questions practice on Codemia

Over 8,000 real interview questions from top companies, searchable by company and role.

Browse interview questions

Spring Boot configure and use two data sources

Interview Questions practice on Codemia

Understanding Multiple Data Sources

Configuration Steps

Step 1: Add Dependencies

Step 2: Configure Data Sources

Step 3: Configure Data Source Beans

Step 4: Access the Data Sources

Key Points Summary

Additional Considerations

Related reading

OOD Fundamentals

Track what you have practised

Interview Questions practice on Codemia