What combined data source should be used for Spring 3.1.0, Hibernate 4.0.1.Final, and MySQL 5.1?

Question

I am using Spring 3.1.0.RELEASE, Hibernate 4.0.1.Final and MySQL 5.1. What is a federated data source that I should use? I am 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 it is not a federated data source that creates JDBC connections for each call. I used this Hibernate configuration (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 due to an error in Spring 3.1.0, I cannot use the hibernate.cfg.xml file when setting up a factory bean session (which I tried to do this: fragment from 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 replacement for 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 the maximum number of connections.
• You can configure the minimum number of idle connections.
• The request that will be executed to verify the connection remains valid.

Additional parameters exist for tuning during validation.

Answer 2

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

Here are the options you can configure:

 <!-- 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> 

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

Another that you can watch (hear) is C3PO , although BoneCP works much better.

Answer 3

Apache DBCP is a widely used pool. But do not use your own version of testWhileIdle . When this option is enabled, all new connections that are used to check for dead connections are blocked in the evictor background thread. This is unacceptable in any non-toy environment. In addition, we have no problem with this.

You can learn more about the pools in this SO stream , but keep in mind that all the flames about the “fastest pool” make sense only with a certain setting at a certain load.

Answer 4

It mostly depends on your application.

Below are the conditions (taken from here ):

• For test or stand-alone environments outside the J2EE container, use DriverManagerDataSource
• For use in a J2EE container, it is recommended that you use the JNDI DataSource provided by the container.
• To use a connection pool data source outside the J2EE container, consider Apache Jakarta Commons DBCP or C3P0 .