c3p0 connection pooling library with Hibernate 3.2.6/JPA - Cannot open connection

Question

My application have two quartz jobs readning two different tables from Oracle DB in the same time. I am trying to get a connection from pool using c3p0 with Hibernate and Oracle. When Tomcat 7 starts it seems like C3P0 is initialized:

2021-02-15 18:47:26,271 [INFO] Hibernate EntityManager 3.2.1.GA
2021-02-15 18:47:26,290 [INFO] Hibernate Annotations 3.2.1.GA
2021-02-15 18:47:26,299 [INFO] Hibernate 3.2.6
...
2021-02-15 18:47:27,473 [INFO] Initializing c3p0-0.9.1 [built 16-January-2007 14:46:42; debug? true; trace: 10]
2021-02-15 18:47:27,621 [INFO] Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@add8f7b3 [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@e6d03554 [ acquireIncrement -> 1, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, ... ]

2021-02-15 18:47:28,240 [INFO] RDBMS: Oracle, version: Oracle Database 11g Release 11.1.0.0.0 - Production
2021-02-15 18:47:28,241 [INFO] JDBC driver: Oracle JDBC driver, version: 10.2.0.4.0
2021-02-15 18:47:28,280 [INFO] Using dialect: org.hibernate.dialect.Oracle10gDialect

But after a few lines, it is still showing Hibernate built-in connection pool, I don't know if that's the way it should be? :

2021-02-15 18:47:37,726 [INFO] Using Hibernate built-in connection pool (not for production use!)
2021-02-15 18:47:37,726 [INFO] Hibernate connection pool size: 20

Anyway, after first job successful got data from Oracle DB, second job could not obtain a connection from pool because it is closed:

2021-02-15 18:48:38,923 [WARN] SQL Error: 0, SQLState: null
2021-02-15 18:48:38,923 [ERROR] com.mchange.v2.c3p0.PoolBackedDataSource@add8f7b3 [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@e6d03554 [ acquireIncrement -> 1, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, debugUnreturnedConnectionStackTraces -> false, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, identityToken -> 2zgg5baf1aa5t7d1frz5fs|700f6fc5, idleConnectionTestPeriod -> 3000, initialPoolSize -> 10, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 300, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 20, maxStatements -> 50, maxStatementsPerConnection -> 0, minPoolSize -> 10, nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@2f7ccb64 [ description -> null, driverClass -> null, factoryClassLocation -> null, identityToken -> 2zgg5baf1aa5t7d1frz5fs|6a16a596, jdbcUrl -> jdbc:oracle:thin:@10.129.12.158:1520:orcl, properties -> {user=, password=, autocommit=true, release_mode=auto} ], preferredTestQuery -> null, propertyCycle -> 0, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> false; userOverrides: {} ], dataSourceName -> null, factoryClassLocation -> null, identityToken -> 2zgg5baf1aa5t7d1frz5fs|63308385, numHelperThreads -> 3 ] has been closed() -- you can no longer use it.
2021-02-15 18:48:38,923 [DEBUG] [docadmin] ccmwsWebQuartz::GenerateDocumentTypeOfRequestJob.generateDocumentTypeOfRequest() - errororg.hibernate.exception.GenericJDBCException: Cannot open connection
2021-02-15 18:48:38,923 [DEBUG] ccmwsWebQuartz::GenerateDocumentTypeOfRequestJob.generateRequest(): exception: org.hibernate.exception.GenericJDBCException: Cannot open connection 1613411318923 ms. javax.persistence.PersistenceException: org.hibernate.exception.GenericJDBCException: Cannot open connection at org.hibernate.ejb.AbstractEntityManagerImpl.throwPersistenceException(AbstractEntityManagerImpl.java:629) at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:73) at com.ct.contentintegration.scheduler.GenerateDocumentTypeOfRequestJob.generateDocumentTypeOfRequest(GenerateDocumentTypeOfRequestJob.java:165) at com.ct.contentintegration.scheduler.GenerateDocumentTypeOfRequestJob.generateRequest(GenerateDocumentTypeOfRequestJob.java:89) at com.ct.contentintegration.scheduler.GenerateDocumentTypeOfRequestScheduler.execute(GenerateDocumentTypeOfRequestScheduler.java:23) at org.quartz.core.JobRunShell.run(JobRunShell.java:202) at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:529)
Caused by: org.hibernate.exception.GenericJDBCException: Cannot open connection

Configuration file look like this: persistence.xml

<?xml version="1.0" encoding="UTF-8"?>
<!-- Persistence deployment descriptor for dev profile -->
<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="jpaCcmwsWebJob">

        <provider>org.hibernate.ejb.HibernatePersistence</provider>

<!--                        RAZVOJ                               -->

        <properties>

            <property name="hibernate.connection.url" value="jdbc:oracle:thin:@10.129.12.158:1520:orcl" />

            <property name="hibernate.dialect" value="org.hibernate.dialect.Oracle10gDialect" />

            <property name="hibernate.connection.driver_class" value="oracle.jdbc.OracleDriver" />

            <property name="hibernate.connection.password" value="docadmin2010" />

            <property name="hibernate.connection.username" value="docadmin" />

            <property name="hibernate.show_sql" value="false" />
            

            
            <property name="hibernate.c3p0.acquire_increment" value="1" />
            <property name="hibernate.c3p0.min_size" value="10" />
          <property name="hibernate.c3p0.max_size" value="20" />
          <property name="hibernate.c3p0.timeout" value="300" />
          <property name="hibernate.c3p0.max_statements" value="50" />
          <property name="hibernate.connection.provider_class" value="org.hibernate.connection.C3P0ConnectionProvider" />
          <property name="hibernate.c3p0.idle_test_period" value="3000" />

        </properties>

    </persistence-unit>

</persistence>

Oracle connection type is Basic.

Any ideas what I was doing wrong ?

Alexandros Pan Alexandros Pan · Accepted Answer · 2021-02-15T20:45:10

That's an old hibernate version.

Which are your dependencies for hibernate-c3p0 and c3p0?

Checking from another post, it seems that the connection provider class for c3p0 was org.hibernate.connection.C3P0 but was deprecated later for org.hibernate.connection.C3P0ConnectionProvider.

Maybe based on your hibernate version you should try using the older connection provider class.

hibernate connection pool

c3p0 connection pooling library with Hibernate 3.2.6/JPA - Cannot open connection

1 Answers