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.