Search code examples
hibernatespring-bootspring-data-jpajdbctemplate

How to connect database with configuration stored in main database at run time in Spring Boot?

I have a requirement where I have one main DB which contains the configuration of different clients DB connections. When I post the Data using Rest API containing JWT then that token will contain the client name and based on client name, I have to fetch the client DB Configurations from Main DB and do the DB connection and insert the data into that DB.

I have gone through various posts around it but not able to find any concrete example.

Currently I am using Spring Boot with Data JPA in project. If anyone has any solution with Spring JDBC Template, I am ready to move my application into that.

Solution

  • As @Jonathan Johx has mentioned its a multi tenant application, each tenant has its schema. To provide some concrete directions, You can create a special data source that will act as a proxy for real data sources, each of those can be in charge for 1 schema connection.

    Spring already provides AbstractRoutingDatSource for this purpose or you can roll your own. Basically you should define some mapping method (determineCurrentLookupKey) and maintain the map of key to actual data source that can be updated in real time in case a new data source should be connected.

    I've found This SO post that explains this approach more in-depth, so no reason to duplicate this information.

    Some points to consider based on your question:

    • Sometimes new data sources should be registered in runtime
    • Probably it worth to close connections and free up the resources both on your application side and RDBMS side by closing the data source upon some ttl if no-one uses it.
    • Since you've mentioned spring boot, it might have an actuator health check that checks the database connectivity, so make sure it still answers the requirements. I mention this because if it will respond with non-200 code and you're running in some advanced environment that makes health-checks like this (like kubernetes, ecs and so forth), the microservice might not be considered healthy because of this, so your devops might be angry :)