how can I have a datasource object that is not a b

Question

This might sound weird. But I want to know how/if I can create datasource objects during runtime and not when the container start-up.

Here is the problem I am working on:

I have a MySql database which stores the URL, userName and password for other SQL Servers that I need to connect and do overnight processing. This list of SQL Servers changes everytime. so it cannot be hard-coded in properties files. Further, the no of SQL servers is about 5000 or more.

The business logic involves reading the MySQL database (which is currently a datasource bean created during container start-up) and for each entry in SQL_SERVER_MAPPING table in MySQL database, I need to connect to that database and run reports.

I was thinking of doing something along this line for each of the SQL server instances

public DataSource getdataSource(String url, String u, String p, String class) {
    return DataSourceBuilder
        .create()
        .username(u)
        .password(p)
        .url(url)
        .driverClassName(class)
        .build();
}

public JdbcTemplate jdbcTemplate(DataSource datasource) { 
        return new JdbcTemplate(dataSource); 
    } 

Here is a builder that generates datasource for a given url and create the necessary jdbcTemplate from it. so basically create one for each of SQL server configurations. My concern is I will be creating about 5000 datasources and 5000 jdbcTemplate or perhaps even more. That doesn't sound right to me. what is the right way to get around here?

is there a way to remove datasource objects as soon I am done with it or recycle them?

should I cache these dataSource objects in my Spring application, so I dont have to create one each time and discard it. but this implies, I need to cache 5000 (or probably more in the future).

Spring docs says

The DataSource should always be configured as a bean in the Spring IoC container. In the first case the bean is given to the service directly; in the second case it is given to the prepared template.

so that makes things harder for me.

Thanks

Answer 1

You can define a bean myBean with scope prototype and use the getBean(String name, Object... args) method of a BeanFactory. args would be the args sent to the constructor (in your case these would be db connections). The bean would return a jdbcTemplate constructed with a datasource defined from the connection properties. This template can be further used in other classes.

Since the scope of the bean is prototype, the created instances will be garbaged collected after the current object is used. This can help if you have memory constraints, the really heavy lifting in terms of creating objects is done when getting the actual DB connections. Caching would be a good solution in case of heavy re-usage of connections.

See an example of this bean and method usage here: spring bean with dynamic constructor value