wood burning stoves 2.0*
The moose likes JDBC and the fly likes Help with closing jdbc connections Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


JavaRanch » Java Forums » Databases » JDBC
Bookmark "Help with closing jdbc connections" Watch "Help with closing jdbc connections" New topic
Author

Help with closing jdbc connections

Chris Stewart
Ranch Hand

Joined: Sep 14, 2002
Posts: 184
I'm got a web application that has many servlets for action processing and jsps for presentation. I also have some "helper" java classes, one of which is a database class. In this class I have a method to get a jdbc connection and many methods each running a certain sql statement and returning the resultSet.
Here is some process flow you'll need to help with the question. The user completes a form and it gets posted to a servlet. The servlet gets the parameters and calls a method from my database class. That method returns a ResultSet which I then set as an Attribute in session. Then I redirect the user back to another jsp. I get the ResultSet from session and set it to a new ResultSet I instantiate (sp) in the jsp. After that I remove the resultset session attribute. Lastly I process through the ResultSet created in the jsp displaying the results of their search.
Now, what I need to do is close the jdbc connections properly. If I close the connection in the method within the database class I get errors about the connection already being closed and I'm getting a rull resultset. But since the connection object I use is a private variable I can only close it from the database class itself. Also, the connection object is a method variable for each method because I thought that if two people did the search at the same time and hit the database class together, they would be "updating" the same connection at the same time and it would cause problems. Am I wrong in thinking that?
Any help or direction is appreciated.
PS: I hope I didn't ramble too bad.
Blake Minghelli
Ranch Hand

Joined: Sep 13, 2002
Posts: 331
I think you should take a look at incorporating a connection pool instead. Constantly creating new connections can create a performance problem and a connection pool will take care of closing connections when needed.
For example, in one of our apps, we have a servlet that initializes a connection pool in its init() method and then "closes" the pool in its destroy() method. In the doXXX() method, the servlet gets a connection from the pool, uses it to do some processing, and then returns the connection back to the pool.
There are also many free connection pools out there.
Also, the connection object is a method variable for each method because I thought that if two people did the search at the same time and hit the database class together, they would be "updating" the same connection at the same time and it would cause problems. Am I wrong in thinking that?

A Connection object can support multiple threads, BUT you can't "share" a Statement safely. If you have one Statement object and user A is processing a ResultSet from it and then (before A is finished) user B executes another query using the same Statement, you'll get an exception. Keep your Statement objects local.


Blake Minghelli<br />SCWCD<br /> <br />"I'd put a quote here but I'm a non-conformist"
Chris Stewart
Ranch Hand

Joined: Sep 14, 2002
Posts: 184
I've used connection pools in weblogic before and have liked using them. Currently, I'm using Tomcat 4.1.18 and I'm not too sure it supports connection pools. I'll look into it though.
Originally posted by Blake Minghelli:
A Connection object can support multiple threads, BUT you can't "share" a Statement safely. If you have one Statement object and user A is processing a ResultSet from it and then (before A is finished) user B executes another query using the same Statement, you'll get an exception. Keep your Statement objects local.

Will I need to handle the threads myself or will the JVM/web server do that for me? Currently I keep all object local to their methods in my database class. Maybe if the destroy method you mentioned will work and bring my resultset back into the jsp without being null, I can make a class object of connection and a method that nulls connection. Then I can call that method in the destroy of each servlet.
 
GeeCON Prague 2014
 
subject: Help with closing jdbc connections