Tomcat 5.5 connection pooling

[Tom McAmmond]

I've read all the tomcat docs on datasource connection pooling, and got it to work easily. However, I have a question.

My DAO code (some of it) looks something like this:

 
private Connection con;
private PreparedStatement select;
private PreparedStatement update;
 
public MyDAO() throws SQLException, NamingException {
Context init = new InitialContext();
Context ctx = (Context) init.lookup("java:comp/env");
DataSource ds = (DataSource) ctx.lookup("jdbc/dbname");
con = ds.getConnection();
select = con.prepareStatement("SELECT * FROM myTable");
update = con.prepareStatement("UPDATE myTable SET status = ?  WHERE index = ?;");
}
 
public close(){
con.close();
}

And then my servlet might do this:

  
MyDAO dao = new MyDAO();
dao.update(1, 2);
dao.close();

So the questions - are my connections being pooled? i.e., if I have a bunch of servlets that all create new DAO objects, run a query, and close the connection, does the connection just drop back into the pool? I think it does. However, isn't there a lot of overhead everytime a servlet needs to make a database query and constructs a new DAO object? Perhaps the DAO should be a singleton? Or maybe the lookup and the datasource.getConnection() just grabs a connection from the pool, so I'm fine?

Is it better to close the connection in the update() and select() methods, rather than require an explicit call to close?

I'm trying to understand this new design pattern (for me) - any advice is appreciated.

Thanks!
[ July 04, 2005: Message edited by: Tom McAmmond ]

[Joseph Marques]

There are two ways that you can get connection pooling:

1) Write it yourself. This is pain-staking and requires you to create individual connections and manage the access to them yourself, coordinating threading issues, and generally making your job an annoyance.

2) Let your provider take care of it. Even cheapo jdbc/odbc provider implement some form of minimal connection pooling. Basically, when you call,

ds.getConnection()

you are getting one of the available connections from the pool. My suggestion would be to turn your DAO into a semi-cache, where you lookup the DataSource once from the context and cache it's value. Then, you have other methods that simply use the DataSource to get new connection instances.

Furthermore, you should know that when you call conn.close(), you aren't actually closing the connection, you're releasing it back to the pool.

-joe