How to initialize database connection on the fly for Java Spring Boot

Question

In my backend API I want to select or create a database, once my database is selected or created I would like to use ORM to update or create the schemas in the database, but how can I set the jdbc.url to connect into my database at runtime?

I have tried some approaches but all of them connect to the database at the beginning(that's why the application.properties store the jdbc.url and some other properties).

That gives me another idea of making 2 different APIs, one master API(with a master database) which will create or select the necessary database(clients database), and the second API will establish the connection with that database(client database) from the beginning as the common Java packages require, but them I got another question. How can I start my second API with the specified jdbc.url database?

I'm using Azure portal services.

score 2 · Answer 1 · answered Aug 30 '23 at 21:42

Spring has mechanisms to set the connection dynamically (mostly for multitenant approaches). I think you can leverage that to solve your questions.

You can try to build the dataSource to connect to a new db during the getConnection step of the MultitenantConnectionProvider. Take as reference this tutorial: https://spring.io/blog/2022/07/31/how-to-integrate-hibernates-multitenant-feature-with-spring-data-jpa-in-a-spring-boot-application

I wrote some code as example of what you would need:

Entity to hold the tenant information:

public class DBContext {

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

    public static String getCurrentDB {
        return CURRENT_DB.get();
    }

    public static void setCurrentDB(String db) {
        CURRENT_DB.set(db);
    }

Class to set the tenant identifier:

@Component
public class TenantIdentifierResolver implements CurrentTenantIdentifierResolver {

    @Override
    public String resolveCurrentTenantIdentifier() {
        return DBContext.getCurrentDB();
    }

And then use that to build the dataSource:

public class MultiTenantConnectionProviderImpl implements MultiTenantConnectionProvider {

    @Override
    public Connection getConnection(String tenantIdentifier) throws SQLException {
        // < build data source here >
        return datasource.getConnection();
    }
}

Finally, somewhere in your code, set the current DB with:

     DBContext.setCurrentDB(newDB);

You can try to simplify things! But I'm pretty sure this can handle your case.

How to initialize database connection on the fly for Java Spring Boot

1 Answers1