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.

Solution 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.

Solution 2

With the spring configuration you are using dbcp instead of cp30. Spring is creating an instance of the data source / connection pool. To configure simillar parameters for dbcp set them has properties directly

<property name="maxActive" value="5"/>
<property name="minIdle" value="2"/>

etc. You can find out about the properties available by looking at the javaodoc for BasicDataSource or from the configuration page http://commons.apache.org/dbcp/configuration.html.

Comments

[gkamal]

When using with spring, it is probably not a good idea to let hibernate manage the connections directly. That won't probably go well with spring transaction management.

[vandershraaf]

If I use the first method, do I need any external artifact to be included to the classpath?

[vandershraaf]

By the way, I used the second method and it works well. Thanks a lot

[Soronthar]

To use the first method, you need the c3p0 jar in your classpath.

[Less]

@Soronthar Hmm... I also tried both of these approaches, and first one (configuring in datasource) obviously works, based on the console debug output. However, with the second approach, I'm not getting any output to convince me that c3p0 is activated... I enabled spring's transaction management, and also I don't have the following props in my config:

[Less]

<prop key="transaction.factory_class"></prop> <prop key="hibernate.transaction.factory_class"></prop> <prop key="hibernate.cglib.use_reflection_optimizer"></prop> <prop key="hibernate.jdbc.batch_size">0</prop> Any idea what could be the problem?

[macemers]

If pooling at datasource level (first approach), how to integrate with spring's session factory?