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:
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?
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
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.
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?
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?
Joined: Jan 07, 2004
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?!
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.