JDBC - Connect Multiple Databases

Question

I am working on an application where I need to connect N number of database systems [N ranges any where between 1 to 350].

The idea is - the user will be presented with a list of databases and will be asked to select any or all of the databases from the list.

Once the databases are selected, I need to connect to each of the database and execute a stored procedure.

I am planning to use plain old JDBC and obtain connection for each of them one a time [or by running them in multiple threads] and execute the store procedure and close the connection.

And all this should happen in a transaction. What is the best way to do this?

If not JDBC...any other efficient way?

Update -

The stored procedure is actually involved in running some sql - for example updating a column, grant permission for a user etc.

Answer 1

I'd create a threadpool with a reasonable maximum amount of threads, between ten and twenty threads maybe, with help of Executors#newFixedThreadPool() and invoke the separate DB connecting and SP executing tasks each as a Callable using ExecutorService#invokeAll(). You would like to play with the threadcount and profile which yields the best performance after all.

Each Callable implementation should take the connection details and SP name as constructor argument so that you can reuse the same implementation for different DB calls.

---

Update: OK, it's a webapplication. You don't want to waste threads. If it is supposed to be used by a single concurrent user, then you should really ensure that the threadpool is properly shutdown at end of request or at highest end of session. But if it is supposed to be used by multiple concurrent users, then you'd like to share the threadpool in the application scope. Also here, you need to ensure that it is properly shutdown when the webapp shuts down. The ServletContextListener is useful here.