Connection Pooling Strategy For A JSP Site?

I'm doing some work on a JSP site that was coded very badly. 开发者_运维知识库For every single database call the original programmer opens and closes a database connection. I'm adding in some new database operations and I would like to do things a little bit better( something like hibernate will come later when more of the site can be rebuilt ). I was thinking of creating a single database connection when a session starts ( after logging in ), storing it in the session object and then closing it in session listener end of session handling function when the session closes. Is this a sound strategy? Thoughts?

I was thinking of creating a single database connection when a session starts ( after logging in ), storing it in the session object and then closing it in session listener end of session handling function when the session closes. Is this a sound strategy? Thoughts?

No. Create a container managed connection pooled data source and get it by JNDI on webapp's startup.

How to create the data source depends on the container in question. In case of for example Tomcat 6.0, you can read it up here: Tomcat JNDI Resources HOW-TO - JDBC Data Sources. In case of for example Glassfish 3, you can do it in the webbased admin console on http://localhost:4848.

Here's how you can finally get it from JNDI:

DataSource dataSource = (DataSource) new InitialContext().lookup(jndiName);
// ...

You can call getConnection() on it. It's sufficient to get the data source only once during webapp's startup, for example in a static initializer block of your database connection manager or perhaps in a ServletContextListener.

The connection should always be acquired and closed in the shortest possible scope. Get it inside the try block where you're executing the query and close it in the finally of the very same try block. Do not keep the connection open longer than necessary. The connection pool will take care about keeping them open, so the performance is already greatly improved that way.

Without commenting on your idea (because I've never done it that way so I don't know for sure), I can say that I've always seen it done something like this (see accepted answer):

How to reuse the same connection with a Spring's JdbcTemplate?

For starters you use a Common DBCP's PoolingDataSource instance stored in your ServletContext (or "application scope" in JSP parlance).

Ideally I would look to migrating to Spring (or Guice or similar).