What is a good tool for the investigation of Database Connection usage in Java?

Have a look at log4jdbc. It enables you to have a look at all stuff going over your jdbc, including opening/closing connections as well as connection number information.

Someone showed me ConnLeakFinder recently, "a simple tool to pinpoint jdbc connection leaks in java code". I haven't tested it myself so far but it should allow you To see who did not close the connection after use. See Connection+Leak+How+To+Find.htm.

But indeed, you should conslder using a connection pool (for example c3p0).

Not a specific tool, but rather a debugging technique for tracking down which code is responsible for open connections or other resources.

I am assuming you are using a consistent method on the java side to get a db connection (pooled or not doesn't matter).

The idea is to create a very light wrapper class around your connection factory/pool or whatever it is. The wrapper will implement whatever jdbc interface makes sense so you can swap it in for your normal connection object but most methods will just transparently call/return the underlying connection.

If you are using some sort of IoC framework (e.g. spring) you should be able to easily swap out the connection/factory class at a config level. Now all your java code will be using your new db connection wrapper.

If you are using a pool, then calling connection.close() usually just returns the object to the pool instead of destroying the connection. So this technique works for normal connection leak or just "not returned to pool (pool exhausted)" leak.

Now we just need to log the interesting bits and set a trap for leaked connections.

Stack trace to identify creator

In the constructor or factory method for your connection wrapper create a new Throwable object and store it as a local variable within your wrapper for later. We use a Throwable because it is faster/cheaper than using Thread.currentThread().getStackTrace().

Set the "trap"

Implement the finalize method in your wrapper class. This is a cleanup method called by the GC when the object is being destroyed because it is no longer used.

The finalize method should check "am I closed?". If already closed, then everything is fine... however if the connection is being GCed and it hasn't been closed... then this is a "leaked" connection.

Now the Throwable comes back into play. We can grab the Throwable and output a nice log message saying something like: "I'm a leaked connection and here is a stack trace implicating my creator."

Expanding the idea

This method can be adapted for a variety of situations. You can of course keep other types of data in your wrapper for troubleshooting your specific issue. For instance creation time. Then you can poll for long-lived connections and again implicate the creator. Or you can poll existing connections and parse the Throwable stack traces to get data on which code is using how many connections over time.

There is probably an off-the-shelf tool that can also do these types of things, but the amount of code required to apply this technique is very minimal in most cases (assuming you have an easy way to swap our your db connection factory without search-replacing your whole codebase).