How to create one database connection per user on a Java Servlet application?

Question

I am working on a website using Java Servlets and my research showed me that it is best to keep one database connection per user (rather than have only one connection sitting all the time on the background or connect to the database every time that a transaction needs to be made). I don't know how to accomplish this, however. What I am currently doing is in my Data Access Object class I have a

private static Connection conn;

and I have a HTTPSessionListener - on sessionCreated event I connect to the Database using this static "conn" variable, and on sessionDestroyed event I disconnect the "conn" variable:

...in my "MySessionListener"...

public void sessionCreated(HttpSessionEvent sessionEvent) {
    System.out.println("Session created!");
    DAO.connect();

}

public void sessionDestroyed(HttpSessionEvent sessionEvent) 
{
    System.out.println("Session destroyed");
    String user = (String) sessionEvent.getSession().getAttribute("userid" );
    if (user != null) DAO.signUserOut(user);
    DAO.disconnect();
}

Now the problem with this is that:

1. I am afraid that this way I essentially degrade to only having one connection that everyone shares(instead of a connection per user as I wanted), just that I disconnect from time to time if there are no users. Correct?
2. If multiple users are online and one closes their session, they will close the connection for everyone until someone else starts a session and creates a new connection for everyone, correct? I cannot test this very well because I am testing it locally on my laptop, with 3 browsers, but even when I close a browser that had my website on, the session doesn't die immediately and I am not sure what exactly is going on. All i know is that sometimes I get an exception saying "No transactions allowed after connection is closed".

Answer 1

There ate two issues here:

• http session timeout
• database session connectivity

You seem to be mixing the two session concepts.

HTTP session

You need to further familiarize yourself with client-server http mechanism. Closing the browser does not close the http session. When you close the browser, you have to code into your pages "on close". "On close"?? Absolutely not - there is no such thing as onclose in html/javascript. But there is onunload (as well as onload).

Why isn't there "onclose" in javascript/html? I think the people who invented http/html were paranoid over many contingencies. Perhaps, rightly so. Perhaps, we have to understand the mindset and motivation of html/http invention. So, you have no choice but concoct a chain-reaction of onunload events. ONUNLOAD/ONLOAD are html page events, not browser events. The whole html mechanism is page driven not browser driven. Therefore, when you close the browser, it would trigger onunload event for every tab on the browser.

You will have to make use of page onunload to inform the server that the user has intention to close the session. Otherwise, the server would have to depend on session timeout value to end the session. What if the user closes the browser on one of your pages on which you did not code in the onunload event? Too bad - that is why I wrote "concoct a chain reaction of onunload" on every page. Which is very tiresome and bothersome.

Sometimes. especially for highly mathematical servlets, the server takes a long time to respond. Then the client page would need an indication to differentiate between a server still processing a response vs the server has gone dead - session timeout enforced on the browser. e.g. http://support.microsoft.com/kb/813827 (How to change the default keep-alive time-out value in Internet Explorer).

May be, the server should poke at the browser page once a while to see if the browser session is still alive. Nope. Http is client pull technology. The server cannot push responses to the client. Why not? Why so silly? You have to read up on the whole http/html mindset/paranoia to understand. The browser can poke at the server but not vice versa.

Therefore AJAX and comet was invented/concocted. To simulate, to pretend on server push. With ajax you have some means for the server to psuedo-poke at the client. And that is what you have to do -- use ajax e.g. jquery or gwt. I prefer gwt.

What if the client computer had a power failure or the OS hit a blue screen, or the browser process was abruptly terminated? There would be no opportunity to trigger the onunload event for any of the pages.

Database connection session

Alex's answer hit the nail -- connection pooling. However, there are situations when I needed to have a database connection per session. Hmmm ... how do I do it? Yes, I store the connection as the session attribute. Therefore, there would be as many db connections as there are sessions. Which, essentially has the same effect as what you are currently doing.

Developing stateful web applications for a stateless(despite the cookies) and presumed unstable client requires cautiousness. What if the user presses the back button after logging out? The backed/prev page might contain an action that causes the server to use a db connection, which was already closed by the log-out page before the user pressed the back button. Or, it may be the server timed out due to client not having poked at the server for a duration longer than the session keep-alive timeout value.

Therefore, before developing a "multi-tier" client-server app, you have to sit down and chart out all your contingencies, with a good understanding of the mindset/paranoia of http technology. You need to infect yourself with http's compulsive obsessions in order to design your applications.

Answer 2

Typically this is achieved using connection pool. You can configure it to have specific number of connections available and the pool manages open and closing connections. Your code will only take available connection from the pool and return it when done.

See this (fairly generic) Wikipedia article.

Some well known pools are DBCP and C3P0.