Using Spring with Hibernate and c3p0 Connection Pool.

C3P0 is a very nice tool to manage database connections. I had hard time configuring Apache DBCP/2 so tried C3P0 and it was very easy to use.. There are many config options to set and the setting has to be done carefully so that we do not end up choking our database. Lets understand some the config options.

• testConnectionOnCheckin validates the connection when it is returned to the pool. testConnectionOnCheckOut would ensure active connections before use, would be too expensive to do.
• idleConnectionTestPeriod sets a limit to how long a connection will stay idle before testing it. Without preferredTestQuery, the default is DatabaseMetaData.getTables() - which is database agnostic, and although a relatively expensive call, is probably fine for a relatively small database. If you're paranoid about performance use a query specific to your database (i.e. preferredTestQuery="SELECT 1")
• maxIdleTimeExcessConnections will bring back the connectionCount back down to minPoolSize after a spike in activity, the connection is removed from the pool and returned back to db.
• numHelperThreads it will help c3p0 spaws helper threads to manage the connections and returning them back

My spring configuration goes as -

<bean id="dataSource"      class="com.mchange.v2.c3p0.ComboPooledDataSource">
    <property name="driverClass">
        <value>com.mysql.jdbc.Driver</value>
    </property>
    <property name="jdbcUrl">
        <value>jdbc:mysql://localhost:3306/api</value>
    </property>
    <property name="user">
        <value>root</value>
    </property>
    <property name="password">
        <value></value>
    </property>
    <property name="idleConnectionTestPeriod">
        <value>300</value>
    </property>
    <property name="maxIdleTimeExcessConnections" value="180"/>
    <property name="maxPoolSize">
        <value>100</value>
    </property>
    <property name="acquireIncrement">
        <value>1</value>
    </property>
    <property name="maxStatements">
        <value>0</value>
    </property>
    <property name="minPoolSize">
        <value>10</value>
    </property>
    <property name="unreturnedConnectionTimeout">
        <value>3600</value>
    </property>
    <property name="preferredTestQuery">
        <value>SELECT 1</value>
    </property>
    <property name="initialPoolSize">
        <value>10</value>
    </property>
    
</bean>

Things to keep in mind - 

Please try to check the number of connection the app has hooked up with the database. For MySql try  - 

SHOW STATUS WHERE variable_name = 'Threads_connected';


When using Hibernate try to take care of opening and closing of sessions. If sessions are not properly closed, the connections are not freed and eventually it will choke the database.

How to configure c3p0

Continue reading →