Dynamic Datasource using SpringBoot

Question

I'm writing a Microservice using SpringBoot and I have a requirement to select the datasource dynamically. I will select the datasource based on the parameter. Each datasources will point to Oracle Database that has the same schema (same tables, triggers, stored procedures and etc). How can I implement this requirement?

Answer 1

Configure all datasources at startup, then:

either:

a. Have a different repo class that implements each datasource, check the parameter before calling the corresponding repo.

b. Have one repo class that checks the parameter and uses the corresponding datasource for its queries.

Answer 2

I did implement something similar...

I used application.properties to store datasource connection

Datasource drive name
spring.datasource.driver-class-name=
spring.datasource.url=
spring.datasource.username=
spring.datasource.password=

there is some code example on how to read and write to properties

https://github.com/evandbrown/amediamanager/blob/d42228a924cfbf14832e774a77c03eb0e9c2dba1/src/main/java/com/amediamanager/config/ConfigurationProviderChain.java

create an endpoint to update your properties

@PutMapping("/update")
    public ResponseEntity<?> updateConnection(@RequestBody final List<ConfigurationProperty> properies) {
        LOGGER.trace("Updating data source properties ");
        for (final ConfigurationProperty configurationProperty : properies) {
            config.getConfigurationProvider().persistDatabaseProperty(configurationProperty.getPropertyName(),
                    configurationProperty.getPropertyValue());
        }

            }
        }
        return new ResponseEntity<> (HttpStatus.OK);
    }

One more tip from experience, ping the connection before executing update endpoint. :) The only conclusion with my implementation, the user will need to restart the server for making changes to pickup.

Answer 3

You must implement two configuration datasource in your application.properties and after that configure two EntityManagerFactory and TransactionManager. You can switch between both datasources using diferent Repositories for every one.

application.properties

first.datasource.url=jdbc:oracle:thin:@//host:1521/firstdb
first.datasource.username=first
first.datasource.password=first
first.datasource.driver-class-name=oracle.jdbc.OracleDriver

second.datasource.url=jdbc:oracle:thin:@//host:1521/firstdb
second.datasource.username=second
second.datasource.password=second
second.datasource.driver-class-name=oracle.jdbc.OracleDriver

spring.jpa.database=default

Two packages to every Entity and repository and two configuration to setup.

First:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
  entityManagerFactoryRef = "entityManagerFactory",
  basePackages = { "com.system.first.repo" }
)
public class FirstDbConfig {

  @Primary
  @Bean(name = "dataSource")
  @ConfigurationProperties(prefix = "first.datasource")
  public DataSource dataSource() {
    return DataSourceBuilder.create().build();
  }

  @Primary
  @Bean(name = "entityManagerFactory")
  public LocalContainerEntityManagerFactoryBean 
  entityManagerFactory(
    EntityManagerFactoryBuilder builder,
    @Qualifier("dataSource") DataSource dataSource
  ) {
    return builder
      .dataSource(dataSource)
      .packages("com.system.first.domain")
      .persistenceUnit("first")
      .build();
  }

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

Second:

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
  entityManagerFactoryRef = "secondEntityManagerFactory",
  transactionManagerRef = "secondTransactionManager",
  basePackages = { "com.system.second.repo" }
)
public class SecondDbConfig {

  @Bean(name = "secondDataSource")
  @ConfigurationProperties(prefix = "second.datasource")
  public DataSource dataSource() {
    return DataSourceBuilder.create().build();
  }

  @Bean(name = "secondEntityManagerFactory")
  public LocalContainerEntityManagerFactoryBean 
  secondEntityManagerFactory(
    EntityManagerFactoryBuilder builder,
    @Qualifier("secondDataSource") DataSource dataSource
  ) {
    return
      builder
        .dataSource(dataSource)
        .packages("com.system.second.domain")
        .persistenceUnit("second")
        .build();
  }
  @Bean(name = "secondTransactionManager")
  public PlatformTransactionManager secondTransactionManager(
    @Qualifier("secondEntityManagerFactory") EntityManagerFactory
    secondEntityManagerFactory
  ) {
    return new JpaTransactionManager(secondEntityManagerFactory);
  }
}

Answer 4

try this AbstractRoutingDataSource in springboot.

https://spring.io/blog/2007/01/23/dynamic-datasource-routing/