DB Connection pool in Servlet or Listener?

Question

Hi,

I'm thinking of using DB Connection Pool in J2EE. Database: MySQL

Servlets: http://www.webdevelopersjournal.com/columns/connection_pool.html

Listeners: http://onjava.com/onjava/2006/04/19/database-connection-pooling-with-tomcat.html

now which is a better option & why? Also, any working source code will help me save time.

Warm Regards

Answer 1

I really wouldn't go for a homegrown connection pool as in your 1st link. There are a lot of factors you need to take account with and those are surely not covered by the example given in that article. Forget about it, don't homegrow a connection pool, it's a way too important core piece of your webapplication.

Go for a container managed connection pool with an existing connection pooling implementation like mentioned in your 2nd link. You have nowadays the choice of under each DBCP and C3P0. DBCP is already inbuilt in Tomcat, but it's singlethreaded and thus not really performant when talking about a busy-visited website. Alternatively (and Tomcat-specific), there's also a tomcat-jdbc which removes this DBCP limitation, but as it's fairly new, I'd rather go for C3P0 as it's already been thoroughly developed and maintained for ages and is nowadays been very robust and performant.