JPA PersistenceException (GenericJDBCException: could not execute query) af

Hi everyone!

I have a problem connection when I work with hibernate (with LazyConnection) using JPAContainer. I have an application working with a JPA container using LazyConnection,
when I deploy it works perfectly but after an inactivity period time it shows me an exception telling me that the Connection is closed
(
this problem solves if I restart Tomcat, but after idle time this exception comes back
). The exception is:

exception
javax.servlet.ServletException: javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute query com.vaadin.terminal.gwt.server.AbstractApplicationServlet.handleServiceException(AbstractApplicationServlet.java:1010) com.vaadin.terminal.gwt.server.AbstractApplicationServlet.service(AbstractApplicationServlet.java:548) javax.servlet.http.HttpServlet.service(HttpServlet.java:723) com.gmv.uab.sat20.utils.spring.LazyHibernateServletFilter.doFilter(LazyHibernateServletFilter.java:30)

root cause
javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: could not execute query org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1214) org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1147) org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:307) org.hibernate.ejb.criteria.CriteriaQueryCompiler$3.getSingleResult(CriteriaQueryCompiler.java:264) com.vaadin.addon.jpacontainer.provider.LocalEntityProvider.doGetEntityCount(LocalEntityProvider.java:543) com.vaadin.addon.jpacontainer.provider.CachingSupport$FilterCacheEntry.getEntityCount(CachingSupport.java:157) com.vaadin.addon.jpacontainer.provider.CachingSupport.getEntityCount(CachingSupport.java:826) com.vaadin.addon.jpacontainer.provider.CachingMutableLocalEntityProvider.getEntityCount(CachingMutableLocalEntityProvider.java:130) com.vaadin.addon.jpacontainer.JPAContainer.size(JPAContainer.java:912) com.vaadin.ui.AbstractSelect.size(AbstractSelect.java:731) com.vaadin.ui.Table.refreshRenderedCells(Table.java:1537) com.vaadin.ui.Table.enableContentRefreshing(Table.java:2985) com.vaadin.ui.Table.setContainerDataSource(Table.java:2555) com.gmv.uab.sat20.ui.workarea.DefaultWorkArea.<init>(DefaultWorkArea.java:138) com.gmv.uab.sat20.ui.workarea.ContextWorkAreaBasic.getContextWorkArea(ContextWorkAreaBasic.java:17) com.gmv.uab.sat20.ui.Sat20Screen.<init>(Sat20Screen.java:152) com.gmv.uab.sat20.Sat20Application.init(Sat20Application.java:51) com.vaadin.Application.start(Application.java:551) com.vaadin.terminal.gwt.server.AbstractApplicationServlet.startApplication(AbstractApplicationServlet.java:1219) com.vaadin.terminal.gwt.server.AbstractApplicationServlet.service(AbstractApplicationServlet.java:484) javax.servlet.http.HttpServlet.service(HttpServlet.java:723) com.gmv.uab.sat20.utils.spring.LazyHibernateServletFilter.doFilter(LazyHibernateServletFilter.java:30)

root cause
org.hibernate.exception.GenericJDBCException: could not execute query org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLStateConverter.java:140) org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:128) org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66) org.hibernate.loader.Loader.doList(Loader.java:2536) org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276) org.hibernate.loader.Loader.list(Loader.java:2271) org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:452) org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363) org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196) org.hibernate.impl.SessionImpl.list(SessionImpl.java:1268) org.hibernate.impl.QueryImpl.list(QueryImpl.java:102) org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:274) org.hibernate.ejb.criteria.CriteriaQueryCompiler$3.getSingleResult(CriteriaQueryCompiler.java:264) com.vaadin.addon.jpacontainer.provider.LocalEntityProvider.doGetEntityCount(LocalEntityProvider.java:543) com.vaadin.addon.jpacontainer.provider.CachingSupport$FilterCacheEntry.getEntityCount(CachingSupport.java:157) com.vaadin.addon.jpacontainer.provider.CachingSupport.getEntityCount(CachingSupport.java:826) com.vaadin.addon.jpacontainer.provider.CachingMutableLocalEntityProvider.getEntityCount(CachingMutableLocalEntityProvider.java:130) com.vaadin.addon.jpacontainer.JPAContainer.size(JPAContainer.java:912) com.vaadin.ui.AbstractSelect.size(AbstractSelect.java:731) com.vaadin.ui.Table.refreshRenderedCells(Table.java:1537) com.vaadin.ui.Table.enableContentRefreshing(Table.java:2985) com.vaadin.ui.Table.setContainerDataSource(Table.java:2555) com.gmv.uab.sat20.ui.workarea.DefaultWorkArea.<init>(DefaultWorkArea.java:138) com.gmv.uab.sat20.ui.workarea.ContextWorkAreaBasic.getContextWorkArea(ContextWorkAreaBasic.java:17) com.gmv.uab.sat20.ui.Sat20Screen.<init>(Sat20Screen.java:152) com.gmv.uab.sat20.Sat20Application.init(Sat20Application.java:51) com.vaadin.Application.start(Application.java:551) com.vaadin.terminal.gwt.server.AbstractApplicationServlet.startApplication(AbstractApplicationServlet.java:1219) com.vaadin.terminal.gwt.server.AbstractApplicationServlet.service(AbstractApplicationServlet.java:484) javax.servlet.http.HttpServlet.service(HttpServlet.java:723) com.gmv.uab.sat20.utils.spring.LazyHibernateServletFilter.doFilter(LazyHibernateServletFilter.java:30)

root cause
java.sql.SQLException: Closed Connection oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112) oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146) oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208) oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:840) oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:759) org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:534) org.hibernate.jdbc.AbstractBatcher.getPreparedStatement(AbstractBatcher.java:452) org.hibernate.jdbc.AbstractBatcher.prepareQueryStatement(AbstractBatcher.java:161) org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1700) org.hibernate.loader.Loader.doQuery(Loader.java:801) org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274) org.hibernate.loader.Loader.doList(Loader.java:2533) org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276) org.hibernate.loader.Loader.list(Loader.java:2271) org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:452) org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:363) org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196) org.hibernate.impl.SessionImpl.list(SessionImpl.java:1268) org.hibernate.impl.QueryImpl.list(QueryImpl.java:102) org.hibernate.ejb.QueryImpl.getSingleResult(QueryImpl.java:274) org.hibernate.ejb.criteria.CriteriaQueryCompiler$3.getSingleResult(CriteriaQueryCompiler.java:264) com.vaadin.addon.jpacontainer.provider.LocalEntityProvider.doGetEntityCount(LocalEntityProvider.java:543) com.vaadin.addon.jpacontainer.provider.CachingSupport$FilterCacheEntry.getEntityCount(CachingSupport.java:157) com.vaadin.addon.jpacontainer.provider.CachingSupport.getEntityCount(CachingSupport.java:826) com.vaadin.addon.jpacontainer.provider.CachingMutableLocalEntityProvider.getEntityCount(CachingMutableLocalEntityProvider.java:130) com.vaadin.addon.jpacontainer.JPAContainer.size(JPAContainer.java:912) com.vaadin.ui.AbstractSelect.size(AbstractSelect.java:731) com.vaadin.ui.Table.refreshRenderedCells(Table.java:1537) com.vaadin.ui.Table.enableContentRefreshing(Table.java:2985) com.vaadin.ui.Table.setContainerDataSource(Table.java:2555) com.gmv.uab.sat20.ui.workarea.DefaultWorkArea.<init>(DefaultWorkArea.java:138) com.gmv.uab.sat20.ui.workarea.ContextWorkAreaBasic.getContextWorkArea(ContextWorkAreaBasic.java:17) com.gmv.uab.sat20.ui.Sat20Screen.<init>(Sat20Screen.java:152) com.gmv.uab.sat20.Sat20Application.init(Sat20Application.java:51) com.vaadin.Application.start(Application.java:551) com.vaadin.terminal.gwt.server.AbstractApplicationServlet.startApplication(AbstractApplicationServlet.java:1219) com.vaadin.terminal.gwt.server.AbstractApplicationServlet.service(AbstractApplicationServlet.java:484) javax.servlet.http.HttpServlet.service(HttpServlet.java:723) com.gmv.uab.sat20.utils.spring.LazyHibernateServletFilter.doFilter(LazyHibernateServletFilter.java:30)

That i said before I not seems a code problem because when I restart Tomcat the application comes to work and execute the query correctly but after an inactivity period time the Exception occours again. Could be a JPA Persistance configuration problem? The database is Oracle 10g but doesn’t seem that the problem could be that.

This is my
persistance.xml
configuration file:

<?xml version="1.0" encoding="UTF-8" ?>
<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_2_0.xsd" version="2.0">
    <persistence-unit name="applicationTest" transaction-type="RESOURCE_LOCAL">
        <provider>org.hibernate.ejb.HibernatePersistence</provider>        
        <properties>
            <property name="hibernate.connection.provider_class" value="org.hibernate.connection.DriverManagerConnectionProvider" />
            <property name="hibernate.connection.driver_class" value="oracle.jdbc.driver.OracleDriver" />
            <property name="hibernate.connection.url" value="jdbc:oracle:thin:@localhost:1555:test" />
            <property name="hibernate.dialect" value="org.hibernate.dialect.OracleDialect"/>
            <property name="hibernate.connection.user" value="test" />
            <property name="hibernate.connection.password" value="testpass" />
            <property name="hibernate.connection.show_sql" value="true"/>
            <property name="hibernate.connection.release_mode" value="after_transaction" />
            <!-- Configuring Connection Pool -->
            <property name="hibernate.c3p0.min_size" value="5" />
            <property name="hibernate.c3p0.max_size" value="20" />
            <property name="hibernate.c3p0.timeout" value="500" />
            <property name="hibernate.c3p0.max_statements" value="50" />
            <property name="hibernate.c3p0.idle_test_period" value="2000" />
            <property name="hibernate.c3p0.preferredTestQuery" value="select 1"/>
            <property name="hibernate.c3p0.testConnectionOnCheckout" value="true"/>    
        </properties>
    </persistence-unit>
</persistence> 

Please, help me! I’ve been working on this problem two days and I don’t know what else to do.

Thanks in advance,
Best regards!

Hello,

Not directly related to vaadin :wink:

Anyway, some articles relates that hibernate.c3p0.testConnectionOnCheckout has to be set on a c3p0.properties file (somewhere in the classpath), not in the xml.
http://kurinchilamp.kurinchilion.com/2013/03/how-to-fix-hibernate-mysql-connection-timeout-issue-solved.html
http://www.databasesandlife.com/automatic-reconnect-from-hibernate-to-mysql/

Hope this helps
Regards
Sebastien