Tomcat resource pooling and connection sharing across components

About a year ago I had a project that needed to tie together a host of different pieces, each of which needed connections to an Oracle database. This really shouldn't be all that difficult, done it many times with JBoss and other application servers. Done it with Tomcat as well... but not with Hibernate, Tomcat, BIRT, and Mondrian...all using the same connection pool. The application was a financial reporting tool that used AJAX for very rich GUI interaction that allowed a user to drill deeply down into financial data and display it a lot of different ways, run both traditional reports as well as multi-dimensional cubes; entirely data driven, every last bit of it.

So I first set out to configure Tomcat and Hibernate to use the same connection pool. I think I must have consulted every possible post I could find. I did get it all up and running, but it was simply too hard and every post I consulted seemed to have a different spin on how it should work; often the advice was entirely contradictory and each asserted that their solution was the correct one. I'm not going to go there and say that this is the correct way to do it... just the way that I did do it and that it worked.

Trying to hook up BIRT (Business Intelligence Reporting Tool) and make the reports use connection pool resources was also not a straight forward process. For those not familiar with BIRT, but needing to integrate reporting into an enterprise Java application I strongly recommend that you look into this fantastic open-source product. It's a part of the Eclipse project that really helps round out what is rapidly becomming a top flight set of tools. I've used Eclipse for years, since the dark early day's when it would do things like erase your files permanantly if you dragged and dropped them incorrectly in the IDE.
At any rate BIRT is Actuate's first forey into the open source arena. Actuate is of course another heavy hitter in the reporting space along with Crystal Reports and Cognos. The key difference between these all of course is that BIRT is open source and free, where the others cost many thousands of dollars and have large royalty structures. At the time of this writing BIRT's current state is 2.2, at the time of my implementation it was just shy of 2.0 so bear in mind that the one constant here is that no matter how fast we write things, the next version will be out before we finish and from what I can tell 2.1 appears to have addressed some or all of the issues with a connection pooling datasource.

I won't cover it in this post but the last piece I needed to bring into the connection pool fold was Mondrian, which is a multidimensional data cube that's now a part of the Pentaho offerings. If you've never looked at it you might want to give it a quick peek, tremendous power and flexibility with multidimensional data cube view and not acutally all that hard to setup and use! I might cover my experience with it's setup and use in a later post.