Need architecture advice for dynamic datasources

[Eric Schumacher]

I asked this in one of the newbie general forums, but I think it might be better asked where the experts live.


I need a nudge or a shove in the right direction. I'm a Java newbie and need some architecture advice on working with multiple db's in a servlet or J2EE situation. I'd like to be able ton narrow my vision/world a little and prevent getting overwhelmed with Java. I'm not a stranger to programming....just new to Java.

My challenge in a Nutshell.

I have a commercial product that uses Oracle. This product has one database for 'Site Administration' and any number of 'project' db's (all with the same 'project' schema). The 'Site Administration' db stores in one of it's tables the location and connection string information required to locate and connect to the 'Project' db's. The number (currently around 80) and location of the 'Project' db's can and does change during the day, week, month etc. The product allows users to create and remove db's at runtime.

I want to create an application that reports from the 'project' db's. I have some JSP reports going along with JFreeCharts already. At this point I'm trying to understand the *best* approach for dynamic runtime datasources. As I see this through my newbies eyes I'm not sure how to define or register or whatever the required datasources with the container because I will never know for certain what db's are present. I will not be able to define these before deployment.

Can someone help point me in the right direction? Can a person register JNDI stuff at runtime? How best to ensure I have connection pooling etc with runtime datasources?

My end target should be full enterprise stability/scalability so I want to make sure I archiect the JDBC stuff correctly before I build out the rest of the application.

I'm currently using Tomcat, but can move to Jboss or WebSphere when deployed in production. I do not yet have experience with either Jboss or Webphere (still keeping my world small to make learing Java easier).

Can anyone give me a shove in the right direction?

thanks!

[Jeanne Boyarsky]

Moving to EJB forum as requested by original poster.

[Eric Schumacher]

^bump^

I'm still pondering this issue and have some more questions.


Does anyone know of a good Connection Pool package? I've looked at both the dbconnectionbroker (http://javaexchange.com/) and the package at http://www.bitmechanic.com.

Would I be able to use container connection pooling if I used the the "hot deploy' feature of the container somehow to specify my datasources?

Anyone have a bit of advice? please?

[Chris Mathews]

Originally posted by Eric Schumacher:
Does anyone know of a good Connection Pool package? I've looked at both the dbconnectionbroker (http://javaexchange.com/) and the package at http://www.bitmechanic.com.

I suggest you take a look at Jakarta's Commons DBCP. It is an OSS database connection pooling product.

Originally posted by Eric Schumacher:
Would I be able to use container connection pooling if I used the the "hot deploy' feature of the container somehow to specify my datasources?

It all depends on your Application Server. For example, WebLogic provides a set of JMX Beans to manage the creation and configuration of datasources at runtime. Therefore, you wouldn't have any problems doing what you are talking about. Obviously this is a non-standard solution and it therefore not portable to other servers. Check the documentation for your Application Server(s).

If you use a third-party pooling service like the aforementioned Commons-DBCP then you could just bind your datasources to JNDI at server startup... avoiding the need to use Application Server specific code. Of course, this also has its drawbacks...

[Eric Schumacher]

Chris, thanks for your reply. This is good information.


Ok, I think I understand, but someone set me straight if I don't. Using the Commons-DBCP package I can in my application code create datasources that are essentially the same internally as what Tomcat provides when setting up datasources in the web.xml. This is good. Now at startup or during a session I can create datasource instances using this package and bind to JNDI as a way to provide access to the "dynamic" datasources in opther parts of my application.

Is there any advantage to using JNDI in this way over say creating a collection of these datasources and managing them in the application? I'm going to have to somehow manage removing datasources when they become unavailable and I'm not clear if it's better to use JNDI or my own solution. Is it better to use JNDI for possible clustering uses or something else?

If you use a third-party pooling service like the aforementioned Commons-DBCP then you could just bind your datasources to JNDI at server startup... avoiding the need to use Application Server specific code. Of course, this also has its drawbacks...

What are some of the drawbacks you refer to? I think the obvious one for me is the need for more code, but I think I have to go down this road due to the many many datasources I may be dealing with and the need for adding them at runtime. What are some other issues?

Thanks,
Eric Schumacher