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 ]
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.
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.
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