C3p0 connection pool for jpa in persistence.xml not working?

Question

I still get JDBC timeouts after trying to configure c3p0, following all the examples I could find. I use JPA, Hibernate, Tomcat, MySQL [AWS RDS]. Here is a snippet from persistence.xml:

<property name="javax.persistence.jdbc.url" value="jdbc:mysql://url..." /> <property name="javax.persistence.jdbc.driver" value="com.mysql.jdbc.Driver" /> <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" /> <property name="connection.provider_class" value="org.hibernate.connection.C3P0ConnectionProvider"/> <property name="hibernate.c3p0.acquire_increment" value="4" /> <property name="hibernate.c3p0.idle_test_period" value="3000" /> <property name="hibernate.c3p0.max_size" value="100" /> <property name="hibernate.c3p0.max_statements" value="15" /> <property name="hibernate.c3p0.min_size" value="5" /> <property name="hibernate.c3p0.timeout" value="100" /> 

And the error log:

 09:18:51.776 WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 0, SQLState: 08S01 09:18:51.777 ERROR org.hibernate.util.JDBCExceptionReporter - The last packet successfully received from the server was 38,491,585 milliseconds ago. ... 

Answer 1

I found the answer from this blog: http://blog.hpxn.net/2009/02/05/using-c3p0-and-hibernate-3/

The properties in persistence.xml were simply incorrect, you should write them starting with hibernate:

 <persistence xmlns="http://java.sun.com/xml/ns/persistence" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_1_0.xsd" version="1.0"> <persistence-unit name="mypersistenceunitname"> <properties> <property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver" /> <property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect" /> <property name="hibernate.hbm2ddl.auto" value="create-drop" /> <!-- Important --> <property name="hibernate.connection.provider_class" value="org.hibernate.connection.C3P0ConnectionProvider" /> <property name="hibernate.c3p0.max_size" value="100" /> <property name="hibernate.c3p0.min_size" value="0" /> <property name="hibernate.c3p0.acquire_increment" value="1" /> <property name="hibernate.c3p0.idle_test_period" value="300" /> <property name="hibernate.c3p0.max_statements" value="0" /> <property name="hibernate.c3p0.timeout" value="100" /> </properties> </persistence-unit> </persistence> 

And then you will see this fabulous magazine:

 2011-08-15 08:58:33 com.mchange.v2.c3p0.C3P0Registry banner INFO: Initializing c3p0-0.9.1 [built 16-January-2007 14:46:42; debug? true; trace: 10] 2011-08-15 08:58:33 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager INFO: Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@f3a94e12 [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@6297f706 .... 

Regards, Stefan (sorry for the generosity)

Answer 2

The configuration looks fine. Enable logging and make sure that C3P0 starts up with the appropriate settings.

  2010-10-16 11: 58: 31,271 INFO [main] ohcConnectionProviderFactory [ConnectionProviderFactory.java:173] Initializing connection provider: org.hibernate.connection.C3P0ConnectionProvider
 ...