I have following setup: JSP/Servlet => Presentation layer. Hibernate and OpenJPA => Data access layer.
To connect to the database I am adding the configuration information in a "persistence.xml" file. Here are the entries for the "persistence.xml" file:
<property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
<property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
<property name="hibernate.connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>
<property name="hibernate.connection.url" value="jdbc:mysql://xxx.xxx.xxx.xxx:xxxx/test?autoReconnect=true"/>
<property name="hibernate.connection.username" value="xxx"/>
<property name="hibernate.connection.password" value="xxx"/>
<property name="hibernate.generate_statistics" value="true"/>
<property name="hibernate.cache.provider_class" value="org.hibernate.cache.OSCacheProvider"/>
<property name="hibernate.cache.use_second_level_cache" value="true"/>
<property name="hibernate.show_sql" value="true"/>
<property name="hibernate.format_sql" value="true"/>
<property name="hibernate.c3p0.min_size" value="5"/>
<property name="hibernate.c3p0.max_size" value="20"/>
<property name="hibernate.c3p0.timeout" value="1800"/>
<property name="hibernate.c3p0.max_statements" value="50"/>
<property name="hibernate.c3p0.idle_test_period" value="1800"/>
<property name="c3p0.idleConnectionTestPeriod" value="1810"/>
I am getting org.hibernate.exception.JDBCConnectionException: could not execute query error. After 13 or more hours of inactivity. This error is not consistently reproduced. I am not able to reproduce the error on my test setup but this error is coming on the production server.
I have searched the forum there are some entry which is similar to mine but they do not answer my question. The links are:
Getting org.hibernate.exception.JDBCConnectionException: could not execute query even through JNDI
Keep getting org.hibernate.exception.JDBCConnectionException: could not execute query
Am I missing some setting or doing something wrong?
For me it was c3p0 version issue. On using the latest c3p0 version the issue was not observed. Also some connections leak use to happen when exception was thrown. Add the connection releasing code in the finally block worked.