What pooled data source should I use for Spring 3.1.0, Hibernate 4.0.1.Final, and MySQL 5.1?

Question

I'm using Spring 3.1.0.RELEASE, Hibernate 4.0.1.Final, and MySQL 5.1. What is the pooled data source I should be using? I'm currently using (snippet from application context file) ...

<bean id="dataSource" class="org.springframework.jdbc.datasource.DriverManagerDataSource">
    <property name="driverClassName">
        <value>com.mysql.jdbc.Driver</value>
    </property>
    <property name="url">
        <value>jdbc:mysql://localhost:3306/myproj</value>
    </property>
    <property name="username">
        <value>myproj</value>
    </property>
    <property name="password">
        <value>password</value>
    </property>
</bean>

but this isn't a pooled data source, creating JDBC connections on each call. I used to have this Hibernate config (hibernate.cfg.xml) ...

<hibernate-configuration>
<session-factory>
    <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
    <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/myproj</property>
    <property name="hibernate.connection.username">myproj</property>
    <property name="hibernate.connection.password">password</property>
    <property name="hibernate.connection.pool_size">10</property>
    <property name="show_sql">true</property>
    <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
    ...

but because of a bug in Spring 3.1.0, I can't use a hibernate.cfg.xml file when configuring my session factory bean (which I tried to do like this -- snippet from the spring application context file ...)

<bean class="org.springframework.orm.hibernate4.LocalSessionFactoryBean" id="sessionFactory">
    <property name="configLocation"> 
        <value>classpath:hibernate.cfg.xml</value> 
    </property>

Answer 1

You can use Apache DBCP, which should be a drop in replacement something like this:

<bean id="dataSource" 
    class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
    <property name="driverClassName" value="${jdbc.driverClassName}"/>
    <property name="url" value="${jdbc.url}"/>
    <property name="username" value="${jdbc.username}"/>
    <property name="password" value="${jdbc.password}"/>
    <property name="maxActive" value="10"/> 
    <property name="minIdle" value="5"/> 
    <!-- SELECT 1 is a simple query that returns 1 row in MySQL -->
    <property name="validationQuery" value="SELECT 1"/> 
</bean>

a few things to note

• you can configure max number of connections.
• you can configure min number of idle connections.
• a query that will get executed to validate the connection is still valid.

Further options exist to configure when validation happens.

Answer 2

If you want to use something mature and performant (e.g. not Apache DBCP), use BoneCP.

Here are the options you can tweak:

<!-- BoneCP configuration -->
<bean id="dataSource" class="com.jolbox.bonecp.BoneCPDataSource" destroy-method="close">
   <property name="driverClass" value="com.mysql.jdbc.Driver" />
   <property name="jdbcUrl" value="jdbc:mysql://127.0.0.1/yourdb" />
   <property name="username" value="root"/>
   <property name="password" value="abcdefgh"/>
   <property name="idleConnectionTestPeriod" value="60"/>
   <property name="idleMaxAge" value="240"/>
   <property name="maxConnectionsPerPartition" value="30"/>
   <property name="minConnectionsPerPartition" value="10"/>
   <property name="partitionCount" value="3"/>
   <property name="acquireIncrement" value="5"/>
   <property name="statementsCacheSize" value="100"/>
   <property name="releaseHelperThreads" value="3"/>
</bean>

BoneCP forum is very active, and committers are quite responsive.

Another one you can look at (would hear about) is C3PO, although BoneCP performs a lot better.

Answer 3

Apache DBCP is a widely used pool. But do not use its option testWhileIdle. When enabled, It's background evictor thread locks all new connections serving while checking dead connections. It's unacceptable in any non-toy environment. Besides this, we have no problems with it.

You may read more about pools in this SO thread, but keep in mind, that all flames about "fastest pool" only make sense with specific tuning under specific load.