Storing a JDBC Connection in HttpSession

Question

I've recently inherited some code, within which I've found a JDBC connection being initialized in a filter and added the the HttpSession for each user. That connection is then reused throughout various portions of the web application for the user. This immediately stood out to me as a code smell. I'd like to go back to the developer who wrote it and explain why he shouldn't do that... But maybe I'm not so sure myself...

Besides taking up unnecessary space in memory and potentially limiting the available connections to the database, are there any other reasons why you wouldn't store a JDBC Connection in an session?

Answer 1

You have mentioned the obvious, one user to one database connection is not scalable. Your users should be completely decoupled from access to the data model. Here are a couple of issues you will run into.

• What happens if the connection becomes stale? That user will not be able to do anything useful without a database connection so they will likely have to log out and then back in to get a new connection. crazy.
• JDBC Connections are not thread safe. If a user decides to run a couple things at once, all hell will break loose.

Answer 2

Whole idea of keeping JDBC connections as high up in the stack as in HttpSession or even accessing the JDBC connections on servlet layer does not make much sense. It makes a lot more sense to pool the connections on application server and reduce the amount of open connections that way. The same application is then able to serve much more simultaneous users with much better performance.

In a large application it is unlikely that all requests will hit the database as the same information is likely to be found on cache anyway.

In clustered environment with non-sticky sessions the JDBC connection would not be even valid if the request would hit other box than the one where session was created.

In general, you should be storing only user specific information in the session and even then reduce the amount needed in sessions to minimum. More data in session means more data to transfer between application servers in a cluster when sessions are replicated (in case sessions are not stored in a central DB or cache).

Answer 3

While I have no way of knowing, I would venture a guess that your colleague started out with every request fetching a new connection of its own from the manager, just like it's done in beginner and demo programs. He soon discovered that this slowed requests down dramatically; so now he's avoiding connection creation by "pooling" them in user sessions.

This solves the performance problem for users making a 2nd and subsequent requests on a connection, but it's a very awkward and non-scalable solution. If your application's user base grows, the number of users with open sessions will quickly outgrow the maximum number of connections your DB can give you. And then there's problems with sessions or DB connections timing out...

The "industry standard" solution is to use connection pooling. Modern versions of Tomcat have connection pooling "built in," as do other Web application servers. If not, you can easily install your own. This allows you to manage a pool of connections completely independently of user sessions.

Another benefit of connection pooling is that, once the pool is "warmed up," i.e. a number of connections are in use and initialized, even "first requests per user session" receive a connection quickly. So overall throughput will be improved over your current situation.