Monitoring Database Connection Pool

[William Frederico]

Hi,

I'm using Struts on Tomcat with Commons DHCP to handle the connection pooling. Straightforward JDBC calls, so I'm manually managing closing the ResultSets, Statement, and Connections.

We recently have found that we haven't always been handling the closing of connections properly and occassionally end up with a max available connections exceeded message.

We went back and fixed the code (finally statements, make sure we close the connection, no double opening of connection, etc.) but we want to make sure we got evenything.

Is there any way to interactively tell how many database connections are open / how many are active / how many are hung up so we can see if we were successful and troubleshoot future issues?

Thanks for any help!

[Alexandru Popescu]

This is quite interesting question. I would like to ask you to define what do you mean by interactively?

./pope

[Lasse Koskela]

org.apache.commons.pool.ObjectPool offers methods for querying for the number of active and idle objects in the pool. If you can somehow access the internal ObjectPool instance of your DBCP pool, this might be the missing link. (I have no idea whether you can access the ObjectPool but I think it's worth a look)

[William Frederico]

Ali - maybe "interactively" was a bad word to use, what I really want to do is just display a var on the JSP pages as we're using the app so that I can see at what point the database connections get Darned to Heck (or just have a jsp page I can call at any point.)

Lasse - thanks, I'll dig into the API. I will post the solution if I find it, if anyone manages to put it together before then I would appreciate the help.

[William Frederico]

Just saw my original post - I'm using DBCP, not dhcp. Not sure if dhcp is anything real ... but for the record I'm using DBCP...

[Alexandru Popescu]

Going on the Lasse's idea I have found out this:

For example the Tomcat servlet container presents a DBCP DataSource as a JNDI Datasource.

You can investigate more on DBCP site

./pope

[William Frederico]

Ok, looks like the methods are available in the BasicDataSource class I need (at least they look like the right ones.) I can write an Action that pulls this info together and displays it on a JSP. Will gladly provide that when I get a chance.

QUESTION
Ideally I would be able to stick all of the logic right into a JSP (no Action, thus no entry needed in struts-sonfig, thus I can just drop the JSP into any application.) Doesn anybody know how I can get a reference to the servlet (or the struts datasource) from inside of the JSP without using an Struts Action/Struts Form bean?

inside the Action you use this code:

DataSource dataSource = (DataSource)servlet.getServletContext().getAttribute(Action.DATA_SOURCE_KEY);

is this possible?

[Alexandru Popescu]

Isn't it possible just to do: getServletContext().getAttribute(<JNDI> ?
(I think of this because a JSP is a servlet in fact)

./pope