File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Check JDBC connection leaks Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Check JDBC connection leaks" Watch "Check JDBC connection leaks" New topic

Check JDBC connection leaks

Dan Parsons
Ranch Hand

Joined: May 30, 2007
Posts: 70
I am always checking if any of my JDBC has Database leaks with our Oracle 9i database.

Here is what I use in SQL Plus:

If I dont close my connections and do alot of JDBC inserts and updates in my Web Application, the query will show many lines of JDBC Thin Client showing up.
When I do close my connections correctly and do alot of inserts and updates in my Web Application, the above query doesnt show any programs running so I assume I dont have any database leaks?

This is my way and would like to know how experienced Java developers check to make sure all their Database connections are closed?
[ October 23, 2007: Message edited by: Dan Parsons ]
Jeanne Boyarsky
author & internet detective

Joined: May 26, 2003
Posts: 33124

Originally posted by Dan Parsons:
would like to know how experienced Java developers check to make sure all their Database connections are closed?

For code we write, we check by only having one class that knows about connections. If this is the superclass, we can do all the work (creating sql statement, setting parameters and processing results) in the subclass. The superclass is known to be good through code review. Everything else is known to be good as it doesn't instantiate connections; just implement methods.

For code we inherit, we use profilers to see if there are leaks along with doing code reviews.

[OCA 8 book] [Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Other Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, TOGAF part 1 and part 2
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
I wouldn't get to used to that method... most Oracle configurations (in this day of SOx requirements and heightened security) do not allow discretionary users or applications select privileges on v$session. It will likely catch up to your location as well.
Stan James
(instanceof Sidekick)
Ranch Hand

Joined: Jan 29, 2003
Posts: 8791
I made a little thing that never exposes connections. It has logic like:

Those closes are in a finally block so it's impossible to forget to close things. It also eliminates a lot of duplicate setup and exception handling. There are similar update and insert methods that return the number of rows affected.

I understand Spring has a very nice JDBC abstraction layer that does something like this. I was not able to introduce any open source at this time so I didn't pursue it.

Would that kind of design work for you?

A good question is never answered. It is not a bolt to be tightened into place but a seed to be planted and to bear more seed toward the hope of greening the landscape of the idea. John Ciardi
I agree. Here's the link:
subject: Check JDBC connection leaks
It's not a secret anymore!