Using Pooled Data Connections with RS Libs Data Hibernate Library

The standard way of configuring a data source for RSLib’s Data Hibernate Library is to use the driver’s DataSource implementation as shown here:

<?xml version="1.0" encoding="UTF-8"?>
<dbconfig>
   <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
 
   <datasource class="com.mysql.jdbc.jdbc2.optional.MysqlXADataSource">
      <property name="url">jdbc:mysql://db-host:3306/db-name</property>
      <property name="user">db-user</property>
      <property name="password">db-password</property>
   </datasource>
</dbconfig>

However, this setup will result in creating a new connection each time you start a new transaction. This is most likely not what you want in a productive application. Instead you shall use a pooled DataSource, as the c3p0 project provides:

<?xml version="1.0" encoding="UTF-8"?>
<dbconfig>
   <property name="hibernate.dialect">org.hibernate.dialect.MySQLDialect</property>
 
   <datasource class="com.mchange.v2.c3p0.ComboPooledDataSource">
      <property name="driverClass">com.mysql.jdbc.Driver</property>
      <property name="jdbcUrl">jdbc:mysql://db-host:3306/db-name?autoReconnect=true</property>
      <property name="user">db-user</property>
      <property name="password">db-password</property>
   </datasource>
</dbconfig>

I stumbled across this problem while implementing a project and receiving a

java.net.SocketException: No buffer space available (maximum connections reached?): connect

exception.

Update July 26nd: You will need the autoReconnect=true parameter with MySQL to avoid problems with connections in the pools not being used for quite a long time.

Update August 18th: This URL parameter doesn’t work correctly. The solution is to add a c3p0.properties file in your classpath:

c3p0.maxPoolSize=50
c3p0.minPoolSize=5
c3p0.timeout=0
c3p0.maxStatements=0
c3p0.maxStatementsPerConnection=0
c3p0.idleConnectionTestPeriod=300
c3p0.acquireIncrement=1
c3p0.validate=true
c3p0.preferredTestQuery=SELECT 1;
c3p0.testConnectionOnCheckin=false
c3p0.testConnectionOnCheckout=true

(Don’t try to set these properties via your hibernate.cfg file. Some of them cannot be set there!)

Leave a Reply