C3P0 Configurations! Where and How?

Question

We are implementing a Web App using JPA2.0 and Hibernate3.0. Connection pool configurations are set in persistence.xml located in META-INF folder.

---

persistence.xml:

<persistence xmlns="http://java.sun.com/xml/ns/persistence" version="2.0">
    <persistence-unit name="MyPU" transaction-type="RESOURCE_LOCAL">
        <!-- Entity Classes-->
        <properties>
            <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
            <property name="hibernate.dialect" value="org.hibernate.dialect.MySQL5InnoDBDialect"/>
            <property name="hibernate.show_sql" value="true"/>
            <property name="bytecode.provider"   value="org.hibernate.bytecode.javassist.BytecodeProviderImpl"/>
            <property name="hibernate.connection.username" value="{username}"/>
            <property name="hibernate.connection.password" value="{password}"/>
            <property name="hibernate.hbm2ddl.auto" value="update"/>
            <property name="hibernate.format_sql" value="true"/>
            <property name="hibernate.connection.url" value="{jdbc url}"/>

            <property name="hibernate.c3p0.min_size" value="1"/>
            <property name="hibernate.c3p0.timeout" value="1000"/>
            <property name="hibernate.c3p0.acquire_increment" value="1"/>
            <property name="hibernate.c3p0.idle_test_periods" value="600"/>
            <property name="hibernate.c3p0.testConnectionOnCheckin" value="true"/>
            <property name="hibernate.c3p0.preferredTestQuery" value="SELECT 1;"/>
       </properties>
    </persistence-unit>
</persistence>

---

We have a problem with connection pool configurations. It seems the configurations have no effect and the connection will be broken after 8 hours. Do we need another configuration file like hibernate.cfg.xml or hibernate.properties?

Sybilla

Possible duplicate of Best configuration of c3p0 – Lys

Answer 1

I had this same problem with the proprieties that I put in persistence.xml didn't affect c3p0.

Looking into http://www.mchange.com/projects/c3p0/index.html#configuration_files I tried to put an xml file named c3p0-config.xml and put it in WEB-INF/classes and it work perfectly.

Here is an example of a c3p0-config.xml file:

<c3p0-config>
  <default-config>
    <property name="automaticTestTable">con_test</property>
    <property name="checkoutTimeout">30000</property>
    <property name="idleConnectionTestPeriod">30</property>
    <property name="initialPoolSize">10</property>
    <property name="maxIdleTime">30</property>
    <property name="maxPoolSize">100</property>
    <property name="minPoolSize">10</property>
    <property name="maxStatements">200</property>

    <user-overrides user="test-user">
      <property name="maxPoolSize">10</property>
      <property name="minPoolSize">1</property>
      <property name="maxStatements">0</property>
    </user-overrides>

  </default-config>
</c3p0-config>

Bakeman

Philipi Willemann is right, if you add the c3p0 config xml, the properties will be read correctly. N.B. if you add some configuration properties which are already described in the hibernate.config file, hibernate will ignore them. For reference you can find more information here mchange.com/projects/c3p0/index.html#configuration_files under "Overriding c3p0 defaults via c3p0-config.xml" section – Repertoire

Answer 2

Good question, bad title. :) I think I answered this question on your re-post: Best configuration of c3p0

Rooker

Answer 3

I had the same exact issue, my problem was that my web application container (Tomcat) was managing my database connections. I had to move the c3p0 configuration from my persistence.xml file to Tomcat's context.xml. The link Domenic D provided is a great place to start if that's your problem.

Dou

Answer 4

There is a typo in your settings, it should be idle_test_period not idle_test_periods.

See this post for information about the setting: The use of c3p0.idle_test_period.

Horatio