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