Configure Multiple Datasource ibn Spring Boot

To configure and resolve multiple databases in a Spring Boot application at runtime, you can follow these steps. The key idea is to define multiple data source configurations and choose the appropriate one dynamically based on the operation.

1. Add Dependencies

Include the necessary database drivers in your pom.xml for the databases you’re using. For example:

<dependency>
    <groupId>org.springframework.boot</groupId>
    <artifactId>spring-boot-starter-data-jpa</artifactId>
</dependency>
<dependency>
    <groupId>mysql</groupId>
    <artifactId>mysql-connector-java</artifactId>
</dependency>
<dependency>
    <groupId>org.postgresql</groupId>
    <artifactId>postgresql</artifactId>
</dependency>
<dependency>
    <groupId>com.microsoft.sqlserver</groupId>
    <artifactId>mssql-jdbc</artifactId>
</dependency>

2. Define Multiple DataSources

Create configuration classes for each database.

Example: DataSourceConfig.java

@Configuration
public class DataSourceConfig {

    @Bean(name = "mysqlDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.mysql")
    public DataSource mysqlDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "postgresDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.postgres")
    public DataSource postgresDataSource() {
        return DataSourceBuilder.create().build();
    }

    @Bean(name = "sqlServerDataSource")
    @ConfigurationProperties(prefix = "spring.datasource.sqlserver")
    public DataSource sqlServerDataSource() {
        return DataSourceBuilder.create().build();
    }
}

3. Add Properties in application.yml

Define configurations for all databases.

spring:
  datasource:
    mysql:
      url: jdbc:mysql://localhost:3306/db_mysql
      username: root
      password: password
      driver-class-name: com.mysql.cj.jdbc.Driver

    postgres:
      url: jdbc:postgresql://localhost:5432/db_postgres
      username: postgres
      password: password
      driver-class-name: org.postgresql.Driver

    sqlserver:
      url: jdbc:sqlserver://localhost:1433;databaseName=db_sqlserver
      username: sa
      password: password
      driver-class-name: com.microsoft.sqlserver.jdbc.SQLServerDriver

4. Create a Routing DataSource

Implement a RoutingDataSource to determine which database to use dynamically.

Example: DynamicRoutingDataSource.java

public class DynamicRoutingDataSource extends AbstractRoutingDataSource {

    @Override
    protected Object determineCurrentLookupKey() {
        return DatabaseContextHolder.getDatabaseType();
    }
}

5. Create a Context Holder

A context holder is used to set the current database dynamically.

Example: DatabaseContextHolder.java

public class DatabaseContextHolder {

    private static final ThreadLocal<String> CONTEXT = new ThreadLocal<>();

    public static void setDatabaseType(String databaseType) {
        CONTEXT.set(databaseType);
    }

    public static String getDatabaseType() {
        return CONTEXT.get();
    }

    public static void clear() {
        CONTEXT.remove();
    }
}

6. Configure the Routing DataSource

Create a bean to integrate the routing logic with all the configured data sources.

Example: DynamicDataSourceConfig.java

@Configuration
public class DynamicDataSourceConfig {

    @Bean
    public DataSource dataSource(
            @Qualifier("mysqlDataSource") DataSource mysqlDataSource,
            @Qualifier("postgresDataSource") DataSource postgresDataSource,
            @Qualifier("sqlServerDataSource") DataSource sqlServerDataSource) {

        Map<Object, Object> dataSourceMap = new HashMap<>();
        dataSourceMap.put("MYSQL", mysqlDataSource);
        dataSourceMap.put("POSTGRES", postgresDataSource);
        dataSourceMap.put("SQLSERVER", sqlServerDataSource);

        DynamicRoutingDataSource routingDataSource = new DynamicRoutingDataSource();
        routingDataSource.setDefaultTargetDataSource(mysqlDataSource);
        routingDataSource.setTargetDataSources(dataSourceMap);

        return routingDataSource;
    }

    @Bean
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
            EntityManagerFactoryBuilder builder, DataSource dataSource) {
        return builder.dataSource(dataSource).packages("com.example.demo").build();
    }

    @Bean
    public PlatformTransactionManager transactionManager(
            EntityManagerFactory entityManagerFactory) {
        return new JpaTransactionManager(entityManagerFactory);
    }
}

7. Use the Dynamic DataSource

Set the desired database at runtime using the DatabaseContextHolder.

Example: Setting the Database Dynamically

@Service
public class SomeService {

    @Transactional
    public void performOperation(String databaseType) {
        try {
            DatabaseContextHolder.setDatabaseType(databaseType); // Set the database
            // Perform database operations
        } finally {
            DatabaseContextHolder.clear(); // Clear after the operation
        }
    }
}

8. Test the Implementation

Call the performOperation method with the desired database type (MYSQL, POSTGRES, SQLSERVER) to perform operations against the respective database.

This setup ensures runtime flexibility in choosing databases and supports transactional consistency across multiple operations.