Dynamic datasource as second datasource in Spring Boot + Hibernate

Question

I have a question about how to handle two different datasources in a Spring Boot application.

Use case:

I have one main repository (db) which must be connected all the time (application scope), i have no problem with that, having TransactionManager and EntityManager.

The second database connection should be only request scoped, with dynamic credentials gathered from an httpRequest.

The datasources are both from PostgreSQL.

Is that even possible? If yes, what is the best way to achieve that.

Thanks for help.

Answer 1

This is an interesting twist on the two datasource pattern!

Your second datasource will have to be resolved based on information external to your application, so you won't be able to use the Spring application context.

You can configure a datasource programmatically in Spring, which is covered in this Q&A:

Configure DataSource programmatically in Spring Boot

Your case is slightly different since the credentials will be resolved at runtime, but can use the same idea.

1. Make sure you have the Spring JDBC dependency.

    <dependency>
        <groupId>org.springframework.boot</groupId>
        <artifactId>spring-boot-starter-jdbc</artifactId>
    </dependency>

2. Using a DataSourceBuilder, construct a new DataSource object using your credentials

public DataSource getDataSource(String user, String password) {
    DataSourceBuilder dataSourceBuilder = DataSourceBuilder.create();
    dataSourceBuilder.url(DBB_URL);
    dataSourceBuilder.username(user);
    dataSourceBuilder.password(password);
    return dataSourceBuilder.build();   
}

And then use that as the datasource for your code. You won't be able to Autowire it, so you'll have come up with a rational strategy for when to construct your datasource in your JDBC methods and use it with a Spring JdbcTemplate in a way that allows you the transaction integrity you are looking for.

There are other considerations that you haven't specified. This strategy assumes you are using the same JDBC driver for both data sources. If you are using different databases, you will have to add the dependencies for those libraries, and then specify the driver class for your dynamic datasource.