Connecting dynamically to different databases using spring boot

Question

How can I connect to multiple databases which are non-identical on the fly (loading the type and connection info of the DB from my DB) using Spring?

what I am trying to build?
I am building a Spring-Boot application that should be able to get data from my customers in the following ways:
1. Connect to my customer SQL DB (MySQL, MSSQL, PostgreSQL,etc...)
2. connect to my customer MongoDB,
3. read data from CSV/JSON files.

While the app is running it gets an HTTP request with customer id. to connect to. at this point, the app should load this customer from my DB (that contain exactly what DB he's using and the credentials to connect to it) and should init a connection to this DB to be able to start query it. (DB Schemas are different between 1 customer to another so I also maintain a set of queries in my DB to query the customer)

I'm struggling to implement the blue part of the diagram:

Kurland

Can you rephrase your question? Please try to explain in points. – Compliance

It is possible to create the DataSource bean programmatically when your application is starting up. You can find more information here. If it is not what you want to achieve please add some kind of sequence diagram. – Lullaby

Thanks, guys. added a diagram to better explain my problem – Kurland

as the diagram shows, I need to open them and close them not on start-up. but on the fly. – Kurland

Answer 1

It is possible to create JdbcTemplate on the fly to query the MySQL database. The following example shows how to do it:

DriverManagerDataSource dataSource = new DriverManagerDataSource();
dataSource.setDriverClassName("com.mysql.jdbc.Driver");
dataSource.setUrl("jdbc:mysql://host:port/database");
dataSource.setUsername("username");
dataSource.setPassword("password");

JdbcTemplate template = new JdbcTemplate(dataSource);
SqlRowSet sqlRowSet = template.queryForRowSet("SELECT FOO FROM BAR;");

A similar approach can be used to work with MongoDB — create MongoTemplate to work with data. Another way is to use the native MongoDB library. Here you can find more information on how to use it.

If the libraries from Spring Boot are used don't forget to disable autoconfiguration, because there are no credentials on startup.

Lullaby

Hi, in my project, configured conn is postgresql db and want to create dynamic connection once a day to create query to mssql db. I have tried your code sample, however, it throws: "Could not load JDBC driver class [com.microsoft.sqlserver.jdbc.SQLServerDriver]" While I have added <dependency> <groupId>com.microsoft.sqlserver</groupId> <artifactId>mssql-jdbc</artifactId> <version>7.2.1.jre11</version> <scope>test</scope> </dependency> Do you have any idea about it? – Cultivation

Does spring takes care of closing the connections, if we follow this approach? – Dogwood

The docs (docs.spring.io/spring-framework/docs/current/javadoc-api/org/…) state that JdbcTemplate "can be used within a service implementation via direct instantiation with a DataSource reference". However, they also say that "The DataSource should always be configured as a bean in the application context" so it seems that instantiating a datasource on the fly might be a problem (don't know why it would be so, because the above code works). – Levey

Answer 2

You can configure multiple data sources in you application. All you need is to mention their driverClassName connectionUrl username password. You can find an example here

PS: Don't forget to mention their dependencies

Mosstrooper

The solution you provide here is assuming that I know which type of DB and the connections details before running. I need to init the connections and close them on the fly. – Kurland

You mentioned that your request contains these information, right ? With the If Else block you can use respective data source. – Mosstrooper