• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Help with closing jdbc connections

 
Ranch Hand
Posts: 184
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Ranch Hand
Posts: 331
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Chris Stewart
Ranch Hand
Posts: 184
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Consider Paul's rocket mass heater.
reply
    Bookmark Topic Watch Topic
  • New Topic