Connection pool or data source? Which should I put in JNDI?

Question

Does it make more sense to have to connection pool at the JNDI level or at the webapp level? For example, I could create at simply javax.sql.DataSource thusly:

<Context antiJARLocking="true">
  <Resource name="jdbc/myDataSource" 
    auth="Container"
    type="javax.sql.DataSource" 
    driverClassName="com.mysql.jdbc.Driver"
    url="jdbc:mysql://localhost/myDataSource" user="user" password="password" />
</Context>

and then configure the pool in Spring thusly:

<bean id="myDataSource" class="com.mchange.v2.c3p0.DataSources"
  factory-method="pooledDataSource">
  <constructor-arg>
    <jee:jndi-lookup jndi-name="java:comp/env/jdbc/myDataSource" />
  </constructor-arg>
</bean>

Or, I could configure the pool directly in JNDI itself:

<Resource name="jdbc/myDataSource" 
  auth="Container"
  factory="org.apache.naming.factory.BeanFactory"
  type="com.mchange.v2.c3p0.ComboPooledDataSource" 
  driverClassName="com.mysql.jdbc.Driver"
  jdbcUrl="jdbc:mysql://localhost/myDataSource" 
  user="user" password="password"
  minPoolSize="3" 
  maxPoolSize="15" 
  maxIdleTime="5000"
  idleConnectionTestPeriod="300" 
  acquireIncrement="3" />

Leaving this spring:

<jee:jndi-lookup id="myDataSource" jndi-name="java:comp/env/jdbc/myDataSource" />

In both cases, the myDataSource spring bean would be a c3p0 connection pooled data source, but which one is better? I am thinking that having the pool in JNDI makes the most sense, but the downside to that is that you must push your c3p0 lib to the servlet container level which could cause conflicts with existing servlets if they currently use a different version. However, putting it in JNDI means your applications dont have to worry about pooling at all. What do y'all think?

Answer 1

You don't need to pool the data source, obtained from JNDI, as it is already pooled :)

The only difference between having a container-manager pool v.s. application pool is that in 1st case you have an ability to monitor the workload on the pool using the standard interfaces (e.g. JBoss console). Then administrator of the application server manages the decision about increasing the pool size, if necessary. He may also switch applications to another DB server (e.g. planned migration from MySQL to Oracle). The disadvantage is that you need slightly more efforts to setup JNDI test data source for your unit tests (see here).

And in 2nd case, yes, you have to package either DBCP or c3p0 plus the JDBC driver together with you application. In this case it is not so easy to collect the statistics about all pools for all application running in Tomcat. Also migration to newer JDBC driver (MySQL 4 to MySQL 5) cannot be done for all applications at once. And connection properties are wired to your application, even if you use a .property file (so changing that needs reassembling and redeployment of the project). Perhaps you don't need all that, as you have only application, one DB and no management overhead.

More topics on this subject:

• 10 pools with 5 connections v.s. 1 pool with 50 connection (that is what happens when each application starts using it's own pool)
• DBCP vs C3P0
• Connection pool implementations
• Why to use a connection pool?