Example:
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.close();
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.
