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 ?

score 0 · Answer 1 · answered Feb 15 '21 at 20:45

0

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

answered Feb 15 '21 at 20:45

Alexandros Pan

161
5

It's an old project still working in production.I have added c3p0-0.9.1.jar only, Don't have any other C3p0 related jars. Existing hibernate jars are: hibernate3.jar, hibernate-annotations.jar, hibernate-commons-annotations-3.3.0.jar, hibernate-entitymanager.jar. Do I need to upgrade hibernate version in order to implement c3p0 connection pooling? – user2257184 Feb 15 '21 at 21:30
Try including a newer version of hibernate-c3p0 like 3.3.2.GA and setting the hibernate.connection.provider_class to org.hibernate.c3p0.internal.C3P0ConnectionProvider – Alexandros Pan Feb 16 '21 at 00:53
I've included hibernate-core-3.3.2.GA.jar and hibernate-c3p0-3.3.2.GA.jar instead of hibernate3.jar, but error is the same. Beside that connection.provider_class is not org.hibernate.c3p0.internal.C3P0ConnectionProvider because package in that version is org.hibernate.connection.C3P0ConnectionProvider – user2257184 Feb 16 '21 at 09:18
If this runs once and then failes, can you add a sample of your code here to check – Alexandros Pan Feb 16 '21 at 17:41
When I used hibernate built-in connection pool, (without any C3P0 jars) these two jobs worked fine several hours or even all day, but eventually got error SQL Error: 17008, SQLState: null, connection closed. That's why I thought the code was correct, but I'll post it later laptop battery is empty :-) and there is no power supply. – user2257184 Feb 16 '21 at 18:01

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

1 Answers1