Dynamically access multiple databases?

Question

My question is very related to this one: http://stackoverflow.com/questions/932625/multiple-dynamic-data-sources-for-a-servlet-context. However I haven’t found a proper solution just yet and would like to ask it again.

I have a little JSF application that talks to a MS SQL Server via JDBC. Tomcat is used as the web container. The application retrieves and stores its data from a single database. A login screen is provided. If the credentials match the ones stored in the database then access is granted and I can play around with the application.

Now I would like to add more databases and provide a login screen which not only requests the username and password but the database name as well. Different databases are used because I would like to have some for testing and development. The backup plans are also not the same for every database.

Currently I use JNDI Resources to look up the databases in my code. However this forces me to edit context.xml and web.xml and to restart tomcat. I don’t want to do that. The restart forces me to run around an tell everyone: “Hey I am rebooting do you mind losing all your connections?”

Is the some more dynamic way to do that?

Answer 1

Create an array of datasources and let the user select which index in this array you want to use.

Answer 2

I dont know the answer but if you call OSQL -L from JNI you can get a list of available SQL database instances in the area. Then you can connect and get the list of databases within.

So:

1. user enters username and password
2. app runs OSQL -L to get the list of instances and provides a select list
3. user selects instance, jdbc uses credentials in step 1 to get a list of databases from the instance
4. jdbc uses selected database to connect.

Answer 3

For your purposes, you should really have three separate application server instances (either on three separate machines, or on the same machine listening to different ports, or different host headers etc). The development server instance should always look up the development database, the staging server looks up the staging database etc, and JNDI should be set up to reflect this. That's what JNDI is for.

That said, if you must set things up with just a single application server, you will probably need to look into writing a custom authentication realm that does this. You could either do the actual work of determining which data source to use yourself, or look into something like Hibernate Shards.

Answer 4

You could get the databases in SQL server using a select statement and eventually discard some of them which are not relevant to your applications.

ResultSet rs = stmt.executeQuery("show databases");