Handling Multiple Databases in Spring Boot: Concepts, Challenges, and Real-World Examples

Why Use Multiple Databases?

There are several reasons why you might want to split your application across multiple databases:

1. Separation of Business Concerns: Different domains, such as user management and transaction processing, might benefit from being separated to improve scalability and maintainability.
2. Performance Optimization: Some databases are better suited for specific tasks, such as a NoSQL database for large datasets or a relational database for complex queries.
3. Compliance Requirements: Storing sensitive data separately from operational data can ensure that you meet industry standards and regulations.
4. Legacy System Integration: In complex systems, legacy databases often need to be integrated without disturbing the current database setup.

Configuring multiple databases in a Spring Boot application is a common requirement when you need to interact with different data sources.

This can be achieved by setting up multiple DataSource beans, EntityManagerFactory beans, and TransactionManager beans for each database.

Scenario:

You have a Spring Boot application that deals with two different databases:

1. Customer Database (customer_db) stores all customer-related information.
2. Order Database (order_db) stores all order-related information.

These two databases are independent of each other. Your application needs to read and write data to both.

Steps to Configure Multiple Databases in a Project:

Step 1: Define the Entity Classes for Each Database

First, you need to define entity classes that map to the tables in both customer_db and order_db.

Customer Entity for customer_db:

@Entity
@Table(name = "customers")
public class Customer {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;
    
    private String name;
    private String email;
    // Getters and setters
}

Order Entity for order_db:

@Entity
@Table(name = "orders")
public class Order {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    private Long customerId;
    private String product;
    private Double amount;
    // Getters and setters
}

Step 2: Define Configuration for Each Database

In a setup, you’ll often define separate configuration classes for each database. These configuration classes handle the connection settings, entity management, and transaction management for each database.

• Application Properties (application.yml):

spring:
  datasource:
    customer-db:
      url: jdbc:mysql://localhost:3306/customer_db
      username: customer_user
      password: password
      driver-class-name: com.mysql.cj.jdbc.Driver

    order-db:
      url: jdbc:mysql://localhost:3306/order_db
      username: order_user
      password: password
      driver-class-name: com.mysql.cj.jdbc.Driver

Customer Database Configuration:

@Configuration
@EnableJpaRepositories(
    basePackages = "com.example.demo.customer",
    entityManagerFactoryRef = "customerEntityManagerFactory",
    transactionManagerRef = "customerTransactionManager"
)
public class CustomerDatabaseConfig {

    @Primary
    @Bean(name = "customerDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.customer-db")
    public DataSource customerDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Primary
    @Bean(name = "customerEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean customerEntityManagerFactory(
            EntityManagerFactoryBuilder builder, @Qualifier("customerDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("com.example.demo.customer")  // Package where your Customer entity is located
                .persistenceUnit("customer")
                .build();
    }

    @Primary
    @Bean(name = "customerTransactionManager")
    public PlatformTransactionManager customerTransactionManager(
            @Qualifier("customerEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
}

Order Database Configuration:

@Configuration
@EnableJpaRepositories(
    basePackages = "com.example.demo.order",
    entityManagerFactoryRef = "orderEntityManagerFactory",
    transactionManagerRef = "orderTransactionManager"
)
public class OrderDatabaseConfig {

    @Bean(name = "orderDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.order-db")
    public DataSource orderDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "orderEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean orderEntityManagerFactory(
            EntityManagerFactoryBuilder builder, @Qualifier("orderDataSource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("com.example.demo.order")  // Package where your Order entity is located
                .persistenceUnit("order")
                .build();
    }

    @Bean(name = "orderTransactionManager")
    public PlatformTransactionManager orderTransactionManager(
            @Qualifier("orderEntityManagerFactory") EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
}

Step 3: Create Repositories

In a real project, you would create repositories for each database to handle CRUD operations.

• Customer Repository:

package com.example.demo.customer;

import org.springframework.data.jpa.repository.JpaRepository;

public interface CustomerRepository extends JpaRepository<Customer, Long> {
}

• Order Repository:

package com.example.demo.order;

import org.springframework.data.jpa.repository.JpaRepository;

public interface OrderRepository extends JpaRepository<Order, Long> {
}

Step 4: Use Repositories in Services

You will write service classes to interact with both databases using their respective repositories. For example:

@Service
public class CustomerOrderService {

    @Autowired
    private CustomerRepository customerRepository;

    @Autowired
    private OrderRepository orderRepository;

    public void createCustomerAndOrder(Customer customer, Order order) {
        // Save customer in customer_db
        customerRepository.save(customer);

        // Save order in order_db
        order.setCustomerId(customer.getId());
        orderRepository.save(order);
    }
}

Step 5: Handling Transactions

If you need to handle transactions across both databases (distributed transactions), you might use Spring’s @Transactional annotation to ensure that both database operations are atomic. However, in complex corporate setups, distributed transaction management can require tools like JTA (Java Transaction API).

@Service
@Transactional
public class CustomerOrderService {

    @Autowired
    private CustomerRepository customerRepository;

    @Autowired
    private OrderRepository orderRepository;

    public void createCustomerAndOrder(Customer customer, Order order) {
        // Both operations will be treated as one atomic transaction
        customerRepository.save(customer);
        order.setCustomerId(customer.getId());
        orderRepository.save(order);
    }
}

Step 6. Main Application (DemoApplication.java)

Your main Spring Boot application class.

package com.example.demo;

import org.springframework.boot.SpringApplication;
import org.springframework.boot.autoconfigure.SpringBootApplication;

@SpringBootApplication
public class DemoApplication {

    public static void main(String[] args) {
        SpringApplication.run(DemoApplication.class, args);
    }
}

Explanation

In this setup:

• Configuration: Separate configuration classes are created to manage the connection settings and entity managers for each database. This allows Spring Boot to know how to interact with both databases.
• Repositories: These act as the bridge between your application and the databases. You create separate repositories for each database so that you can fetch and save data accordingly.
• Service Layer: This layer ties everything together, allowing you to perform operations that might involve both databases. You can implement business logic here.

Challenges in Handling Multiple Databases

While Spring Boot provides robust support for handling multiple databases, there are certain challenges that developers face in real-world projects:

1. Transaction Management: Ensuring transactional consistency across multiple databases is complex. A failure in one database operation can leave other operations incomplete, which can cause data inconsistency.

• Solution: Use a chained transaction manager or distributed transaction systems like the Saga pattern to handle failures across databases.

1. Migration and Schema Management: Each database requires its own schema migrations. Tools like Flyway or Liquibase help in managing migrations for multiple databases, but careful configuration is needed.

• Solution: Configure separate migration scripts for each database and ensure that they run independently.

1. Performance Issues: Connecting to and querying multiple databases can add latency. Moreover, splitting databases can sometimes degrade performance if not optimized properly.

• Solution: Ensure that queries are optimized for each database. Use read-replicas or caching to reduce the load on the databases.

1. Complexity in Code and Deployment: Managing multiple databases adds complexity to the codebase, as well as to deployment and maintenance.

• Solution: Keep configurations modular and automate the deployment process to ensure smooth updates.