Configuring the built-in c3p0 pooling in Hibernate using Spring

Question

I learned that to configure c3p0 pooling in hibernate, we can have write the configuration in hibernate.cfg.xml such this:

<property name="hibernate.c3p0.min_size">2</property>
    <property name="hibernate.c3p0.max_size">5</property>
    <property name="hibernate.c3p0.timeout">600</property>
    <property name="hibernate.c3p0.max_statements">0</property>
    <property name="hibernate.c3p0.idle_test_period">300</property>
    <property name="hibernate.c3p0.acquire_increment">1</property>

However I configured Hibernate using Spring. When I tried to do below, it wouldn't work:

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource">
    <property name="driverClassName" value="com.mysql.jdbc.Driver" />
    <property name="url" value="jdbc:mysql://localhost/news_loader" />
    <property name="username" value="blah" />
    <property name="password" value="blah" /> 

    <property name="hibernate.c3p0.min_size" value="2" />
    <property name="hibernate.c3p0.max_size" value="5" />
    <property name="hibernate.c3p0.timeout" value="600" />
    <property name="hibernate.c3p0.max_statements" value="0" />
    <property name="hibernate.c3p0.idle_test_period" value="300"/>
    <property name="hibernate.c3p0.acquire_increment" value="1" />
</bean>

I've read about using the stand-alone c3p0 pooling which can be configured using Spring, but is there any way that I can configure the built-in c3p0 pooling in Hibernate using Spring?

Enlighten me coz i'm a beginner.

Answer 1

Here is a sample configuration (from our application) on how to configure c3p0 in the datasource:

<bean id="dataSourceGlobal" class="com.mchange.v2.c3p0.ComboPooledDataSource"
          destroy-method="close">
        <property name="driverClass" value="${driver}" />
        <property name="jdbcUrl" value="${server}" />
        <property name="user" value="${user}" />
        <property name="password" value="${passw}" /> 

        <!-- these are C3P0 properties -->
        <property name="acquireIncrement" value="${acquireIncrement}" />
        <property name="minPoolSize" value="${minPoolSize}" />
        <property name="maxPoolSize" value="${maxPoolSize}" />
        <property name="maxIdleTime" value="${maxIdleTime}" />
</bean>

We use an external property file to configure some parameters, but they can be configured directly in Spring too.

If you want hibernate to take care of the pooling, then you need to configure the Session properties:

<bean id="sessionFactory" class="org.springframework.orm.hibernate.LocalSessionFactoryBean">
    <!--suppress InjectionValueTypeInspection -->
    <property name="mappingResources" ref="hibernateMappingList" />
    <property name="hibernateProperties">
        <props>
            <prop key="hibernate.dialect">net.sf.hibernate.dialect.Oracle9Dialect</prop>
            <prop key="transaction.factory_class">
                net.sf.hibernate.transaction.JDBCTransactionFactory
            </prop>
            <prop key="hibernate.transaction.factory_class">
                net.sf.hibernate.transaction.JDBCTransactionFactory
            </prop>
            <prop key="hibernate.show_sql">false</prop>
            <prop key="hibernate.cglib.use_reflection_optimizer">false</prop>
            <prop key="hibernate.jdbc.batch_size">0</prop>

            <prop name="hibernate.c3p0.min_size" value="2" />
            <prop name="hibernate.c3p0.max_size" value="5" />
            <prop name="hibernate.c3p0.timeout" value="600" />
            <prop name="hibernate.c3p0.max_statements" value="0" />
            <prop name="hibernate.c3p0.idle_test_period" value="300"/>
            <prop name="hibernate.c3p0.acquire_increment" value="1" />
      </props>
    </property>
</bean>

You must use one of the approaches: either pool at the datasource or pool at the hibernate Session. Never use both, as it wastes resources.