How to add new Datasource at runtime Spring Boot

Question

I have this, but I don't find any possible solution. All the answers I found was about configuring two or more Datasource or Multitenant Dabatase, but It is not what I need.
I have to do this:

1. Config the 1st Datasource from application.properties. This is the primary Database configuration

@Configuration
@EnableTransactionManagement
@EnableJpaRepositories(
        entityManagerFactoryRef = "primaryEntityManagerFactory",
        basePackages = "com.example.primary")
public class SmartConnectConfig {

    @Primary
    @Bean(name = "primaryDatasource")
    @ConfigurationProperties(prefix = "spring.datasource")
    public DataSource dataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setUrl(env.getProperty("spring.datasource.url"));
        dataSource.setUsername(env.getProperty("spring.datasource.username"));
        dataSource.setPassword(env.getProperty("spring.datasource.password"));
        return dataSource;
    }

    @Primary
    @Bean(name = "primaryEntityManagerFactory")
    public LocalContainerEntityManagerFactoryBean entityManagerFactory(
            EntityManagerFactoryBuilder builder,
            @Qualifier("primaryDatasource") DataSource dataSource) {
        return builder
                .dataSource(dataSource)
                .packages("com.example.primary")
                .persistenceUnit("primary")
                .build();
    }

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

2. When the user connected in the system depending on the organization make a query an get all the properties or the organization Database saved in the 1st Database for getting config of 2nd Datasource (url, username, password, driverClassName). This is VERY IMPORTANT because depending on user login I need to create different Datasource depending on the organization.
3. Add to spring to manage some repository with the 2nd Datasource
   Note: 1st and 2nd repositories are in the different package for Spring can make a scan of those repositories, the second package is "com.example.second".

Someone can give some suggestions.
Thanks

Answer 1

I suggest you try storing datasource information to the HttpSession.

Example:

@RestController
public class CategoryController {

  @PostMapping("init")
  public void createDatasource(HttpSession session, @RequestBody CategoryRequestDto request) {
    if (session != null && session.isNew()) {
        DBInfo dbInfo = request.getUserinfo().getDatabaseInfo();
        HikariDataSource hikariDataSource = new HikariDataSource();
        hikariDataSource.setUsername(dbInfo.getUsername());
        hikariDataSource.setPassword(dbInfo.getPassword());
        hikariDataSource.setJdbcUrl("jdbc:h2:mem:"+session.getId());
        hikariDataSource.setDriverClassName(Driver.class.getName());
        session.setAttribute("datasource", hikariDataSource);// store datasource for later use
    }
  }
}

Answer 2

There are plenty of resources explaining how to configure data source in Spring Boot using various options and various ways to retrieve the configuration details of the connections (from external file, from application.properties, hard-coded, etc). On the other hand, if you already have a data source configured and at runtime you want to change the details of this connection (perhaps hostname of database changes) without restarting the application, the information is a bit scarcer.

The use-case I am talking about is a situation where the user is allowed to change all or part of the connection details to the database (hostname for example). The user has a Settings page where he can edit these details. This settings are kept in an external file, not in application.properties. In this case, for the subsequent requests to that database, the new parameters need to be used. The default behavior of Spring is to create Beans as Singleton, including the DataSource that you configure and thus, the bean won’t be recreated if you change the settings (it will still use the old parameters).

Here I present one of many approaches as to how this can be achieved.

Creating the data source

There are multiple ways to create a data source to use in Spring Boot and plenty of resources for this. Here, I show how to create a simple JdbcTemplate based Repository configured in a separate package (will not use database properties defined in application.properties).

Here’s the configuration snippet for the data source:

@Configuration
public class CustomDataSourceConfiguration {

    @Lazy
    @Bean
    @Scope(ConfigurableBeanFactory.SCOPE_PROTOTYPE)
    public DataSource customDataSource() {
        DataSourceBuilder dsBuilder = DataSourceBuilder.create();
        dsBuilder.driverClassName(„oracle.jdbc.driver.OracleDriver“);
        CustomDatabaseSettings dbSettings = <….>//Here obtain the settings from whereever you need
        dsBuilder.url(dbSettings.jdbcUrl());
        dsBuilder.username(dbSettings.username());
        dsBuilder.password(dbSettings.password());
        return dsBuilder.build();
    }

    @Lazy
    @Qualifier(„customJdbcTemplate“)
    @Bean
    public JdbcTemplate customJdbcTemplate() {
        return new JdbcTemplate(customDataSource());
    }

}

Here, using a class annotated as Configuration class, I define the Beans needed for the DataSource and for the JdbcTemplate. The @Lazy annotation is important in this context since I read the external file which holds the database configuration from Java code and this happens after Spring Container initializes all beans. By default, Beans are loaded Eagerly, meaning that when Spring scans for them and meets them, it will instantiate them. Lazy will wait with the initialization until the first request for that bean is made. See the doc for @Lazy – https://docs.spring.io/spring-framework/docs/current/javadoc-api/org/springframework/context/annotation/Lazy.html

The Scope of the bean is set to ‚Prototype‘. By default the scope is set to Singleton, but as I said in the beginning of the article, we need a way to reinitialize the data source with new configuration details and the easiest way is using this scope. This scope will call the initialization each time the bean is requested. As you can see from the code, this suits our case very well since calling again the method to initialize will also get the new configured database connection details. There are some caveats to using Prototype. More info about the scopes can be found in the docs: https://docs.spring.io/spring/docs/3.0.0.M3/reference/html/ch04s04.html

Here’s the code the Repository as well:

@Lazy
@Repository
@ComponentScan(basePackages = „com.cli.jdbc.datasource“)
public class CustomRepository{

    @Qualifier(„customJdbcTemplate“)
    @Autowired
    private JdbcTemplate jdbcTemplate;

//Implement methods here

So we’ve configured the DataSource by reading from our custom Configuration file which changes dynamically at runtime. Now, when the Settings are changed, we need a way to reinitialize the data source. Conceptually this is done by simply retrieving the DataSource bean from the Spring Context. Because we used Prototype scope, this will cause the logic in the above defined method to be called again which in turn will take the newest changes for our database. After this step, we need to retrieve the jdbcTemplate from the context as well and set this new DataSource to it.

The code looks like this:

public void refreshCustomJdbc() {
        DataSource ds = (DataSource) getSpringContext().getBean(„customDataSource“);
        JdbcTemplate customJdbcTemplate = (JdbcTemplate) getSpringContext().getBean(„customJdbcTemplate“);
        customJdbcTemplate.setDataSource(ds);
    }

Reference: https://blog.virtual7.de/dynamically-change-data-source-connection-details-at-runtime-in-spring-boot/

Answer 3

If you need to configure two data sources, you only need to call this with differrent names, for example :

@Bean("dataSource")
public DataSource getDataSource() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
        dataSource.setDriverClassName(this.hProperties.getProperty("db.driverClass"));
        dataSource.setUrl(this.hProperties.getProperty("db.location"));
        dataSource.setUsername(this.hProperties.getProperty("db.username"));
        dataSource.setPassword(this.hProperties.getProperty("db.password"));
        return dataSource;
    }
    @Bean("dataSourceTwo")
    public DataSource getDataSourceLucca() {
        DriverManagerDataSource dataSource = new DriverManagerDataSource();
    dataSource.setDriverClassName(this.hProperties.getProperty("db.lucca.driverClass"));
        dataSource.setUrl(this.hProperties.getProperty("db.lucca.location"));
        dataSource.setUsername(this.hProperties.getProperty("db.lucca.username"));
        dataSource.setPassword(this.hProperties.getProperty("db.lucca.password"));
        return dataSource;
    }


@Bean("sessionFactory")
public LocalSessionFactoryBean getSessionFactory() {
    loadProperties();
    LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();
    sessionFactory.setDataSource(getDataSource());
    sessionFactory.setPackagesToScan(new String[] { "com.monty.goofy" });
    sessionFactory.setHibernateProperties(getHibernateProperties());
    return sessionFactory;
}
@Bean("sessionFactoryTwo")
public LocalSessionFactoryBean getSessionFactoryTwo() {
    loadProperties();
    LocalSessionFactoryBean sessionFactory = new LocalSessionFactoryBean();
    sessionFactory.setDataSource(getDataSourceTwo());
    return sessionFactory;
}
@Bean("transactionManager")
public HibernateTransactionManager transactionManager() {
    HibernateTransactionManager txManager = new HibernateTransactionManager();
    SessionFactory sessionFactory = getSessionFactory().getObject(); 
    txManager.setSessionFactory(sessionFactory);
    return txManager;
}
@Bean("transactionManagerTwo")
public HibernateTransactionManager transactionManagerLucca() {
    HibernateTransactionManager txManager = new HibernateTransactionManager();
    SessionFactory sessionFactoryTwo = getSessionFactoryTwo().getObject(); 
    txManager.setSessionFactory(sessionFactoryTwo);
    return txManager;
}

This is an example using both of them:

@Override
@Transactional("transactionManager")
public void saveOne(){

}
@Override
@Transactional("transactionManagerTwo")
public void saveTwo(){

}

(this is a manual configuration)