How to close database connection explicitly when session is ended?

Luigi Viggiano

You need to be notified by the web server when the session expires. To do that you need to implement the HttpSessionListener (Servlet 2.3 spec) interface and configure it in the deployment descriptor of the web application. If you have an older Servlet container (implementing Servlet 2.2 spec) you can implement HttpSessionBindingListener and put it in session, so when the object is unbound.


public class MyConnectionHolder implements HttpSessionBindingListener {
  //transient to avoid "passivation"
  private transient Connection conn;

  public Connection getConnection() {
    if (null == conn) {
      //connect to the DB...    
    return conn;

  public void valueBound(HttpSessionBindingEvent event) {
    // you may want to initialize 
    // the connection there...
    conn = getConnection();
  public void valueUnbound(HttpSessionBindingEvent event) {
    conn = null;

Note: I've not included exception handling, but you'll need to do.

So in your JSP you'll put the object in session to create the connection (this is just an example):


  MyConnectionHolder connHolder = new MyConnectionHolder();
  session.setAttribute("connHolder", connHolder);

Then to use it:


  MyConnectionHolder connHolder = (MyConnectionHolder)session.getAttribute("connHolder");
  Connection conn = connHolder.getConnection();

When the session expires, the connection will be closed automatically.

My habit is to get a Connection from a ConnectionPool only when I need it (before of a query), and release it to the pool as soon as I've finished to use it (after the query). I believe it's the best policy.

[That is, if you use a ConnectionPool, then you don't have to worry about when the session ends. You just release it when the *query* ends (or the Request). - Alex]