File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Closing database connections properly? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Closing database connections properly?" Watch "Closing database connections properly?" New topic
Author

Closing database connections properly?

Jeppe Sommer
Ranch Hand

Joined: Jan 07, 2004
Posts: 270
Hello.

I sometimes experience that a database connection is never getting closed in my web application. How do I ensure that the database connections i properly closed? What if the user loads the web site twice (starting a new thread for each load), before the first connection is closed?

Here is two cases:

Case 1:
If a user reloads the page twice, does it opens two database connections or does it overwrite the first connection being open? If it opens two connections how can we close both connections in the finally block? We only have one database connection object called "conn" to close?

Case 2:
Below I tried to avoid the user to open two connections by using a session value. The problem with this code is that if the user loads the page twice, then the page turns white, because thread number two never get access to the HTML code

Can anyone give me some good advises please?
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Are you using a connection pool? That would be the normal practice for a web application. If you are, when you close the connection in a finally block (as you always should) the connection is released back to the pool rather than closed. The pool itself may close the connection. Two users might use the same connection, depending on how the pool provides these.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Jeppe Sommer
Ranch Hand

Joined: Jan 07, 2004
Posts: 270
Yes, I am using a connection pool called Proxool (http://proxool.sourceforge.net/).

Then, does the code pick up two connections from the pool, if the user hits the page twice (before thread 1 i reaching the finally block) and if so, do we release both connections to the pool at once in the finally block?

Please take a look at case 2. If the user loads the page twice or more, before the session attribute is removed in the finally block, then we have a return statement. Does the thread never ends up in the finally block, if we have this return statement (see #point_1)? Maybe that is why some connections are never closed?

Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


Then, does the code pick up two connections from the pool, if the user hits the page twice (before thread 1 i reaching the finally block)

It might. You delegate that decision to the pool implementation.


Does the thread never ends up in the finally block, if we have this return statement (see #point_1)?

Yes. The finally block is always called. The only thing you can really do to stop this is to call System.exit() in the try block.

The most likely cause of connections not being closed is code that does not close connections.

Your use of a session variable looks dangerous. Why abandon the second request? Does it matter that two distinct HTTP requests might use two distinct connections?
Jeppe Sommer
Ranch Hand

Joined: Jan 07, 2004
Posts: 270
Your use of a session variable looks dangerous


Alright I won´t abandon the second request!

What about in servlets which make database updates... If the user loads the servlets twice before the servlets get redirected to the JSP page, then 2 rows would be inserted into the database. Should we use a session variable in servlets to avoid the user to create multiple records in the database? Let's say the servlet is creating a new order? If the user hits the reload button twice, two orders will be created twice?!
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336

Are you talking about updates or inserts? Updates should use some sort of locking mechanism (probably optimistic locking); inserts will need something to watch for duplicate requests (search these forums, this gets discussed a lot).

If multiple records in the database is a real issue, you probably also need to add constraints to your data model to prevent these too.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Closing database connections properly?