JDBC Database Connections in a Web App DAL

Question

I am building a small website for fun/learning using a fairly standard Web/Service/Data Access layered design.

For the Data Access Layer, what is the best way to handle creating Connection objects to call my SQL stored procedures and why? Bearing in mind I am writing a lot of the code by hand (I know I could be using Hibernate etc to do a lot of this for me)...

1) Should I create one static instance of the Connection and run all my querys through it or will this cause concurrency problems?

2) Should I create a Connection instance per database call and accept the performance overhead? (I will look into connection pooling at a later date if this is the case)

Answer 1

You should use one Connection per thread. Don't share connections across threads.

Consider using Apache DBCP. This is a free and standard way of configuring database connections and drawing them from a pool. It's the method used by high-performance web servers like Tomcat.

Furthermore, if you're using DBCP, since it's a pool (read: cached), there's little penalty to creating/closing connections frequently.

Answer 2

Standard way is to setup a DataSource. All application servers are able to do so via their admin console. The pool is then accessible by it's JNDI name (e.g. "jdbc/MyDB").

The datasource should in fact be a connection pool (and usually is). It caches connections, tests them before passing to application and does a lot of other important functions.

In your code you:

1. resolve JNDI name and cast it into DataSource
2. get a connection from the datasource
3. do your work
4. close the connection (it goes back to pool here)

You can setup the pool yourself (using any of freely available pool implementation), but it really doesn't make any sense if you're using an application server.

P.S. Since it's a web application a good way to make sure you have closed your connection after requist is to use HttpFilter. You can setup one in web.xml. When request comes, acquire the connection, put it into ThreadLocal. During the request, get connection from ThreadLocal, but never close it. After request, in the filter, close the connection.