pooling: ConnectionPoolDataSource & dbcp

[dougp]

i want to use connection pooling in my application. should i use javax.sql.ConnectionPoolDataSource or commons dbcp? what are the pros/cons of each approach?

[dougp]

from the digging i've done so far on google, javax.sql.ConnectionPoolDataSource does not seem to be very useful. the actual db pooling is done in the middle tier by the app server..

[lblaauw]

i want to use connection pooling in my application. should i use javax.sql.ConnectionPoolDataSource or commons dbcp? what are the pros/cons of each approach?

Well you could drop in proxool connection pooling.

Greetings,
Leo

[Juergen Hoeller]

javax.sql.ConnectionPoolDataSource is just a callback interface defined by JDBC, to allow concrete connections pools and JDBC drivers to interact more closely. It's basically an alternative to the java.sql.Driver interface. In either case, you need a concrete connection pool DataSource in front of it, which is what your application will access.

Essentially, don't bother with the ConnectionPoolDataSource interface too much. A Commons DBCP BasicDataSource or C3P0 ComboPooledDataSource with a JDBC driver class name specified will work nicely for typical requirements.

Juergen

[ctaggart]

I've heard mixed reviews about the Jakarta Commons Pool and DBCP. The Hibernate forum guys said they may deprecate support for it in a future release of Hibernate. I would take a look at xapool or c-jdbc. c-jdbc does a lot more and is more difficult to setup, but seams to be quite powerful. Here is the link and sample spring definition for xapool. I based it on the JDBC connection example, linked off their homepage.

http://xapool.experlog.com/

  <bean id="dataSource" class="org.enhydra.jdbc.pool.StandardPoolDataSource" destroy-method="stopPool">
    <constructor-arg index="0">
      <bean class="org.enhydra.jdbc.standard.StandardConnectionPoolDataSource">
        <property name="driverName"><value>com.mysql.jdbc.Driver</value></property>
        <property name="url"><value>jdbc&#58;mysql&#58;//localhost/dbname</value></property>
      </bean>
    </constructor-arg>
    <property name="user"><value>root</value></property>
    <property name="password"><value>mypass</value></property>
    <property name="minSize"><value>1</value></property>
    <property name="maxSize"><value>5</value></property>
    <property name="jdbcTestStmt"><value>select 1</value></property>
  </bean>

The Spring bean for c-jdbc isn't difficult, but the rest of the setup is a bit more work. Here is the bean.

<bean id="dataSource" class="org.objectweb.cjdbc.driver.DataSource">
    <property name="url"><value>jdbc&#58;cjdbc&#58;//127.0.0.1&#58;25322/vdb?user=vuser</value></property>
  </bean>

I tried out both today with Hibernate with good results.

Cameron