Issues with Hibernate 4.3.5 c3p0-0.9.1

Question

I am using Hibernate 4.3.5 I am trying to connect to Database (Oracle) And I have tried connection pooling with c3p0-0.9.1.

I have set the following Properties to enable Connection pooling with c3p0-0.9.1.

  <property  name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>         
  <property name="hibernate.c3p0.min_size">0</property>
  <property name="hibernate.c3p0.max_size">5</property>
  <property name="hibernate.c3p0.timeout">300</property>
  <property name="hibernate.c3p0.max_statements">0</property>
  <property name="hibernate.c3p0.idle_test_period">300</property>

After setting these properties, Below Information is getting logged and I am unable to connect to the database.

     09:58:39,573  INFO MLog:80 - MLog clients using log4j logging.
     09:58:39,698  INFO C3P0Registry:204 - Initializing c3p0-0.9.1 [built 16-January-2007 14:46:42; debug? true; trace: 10]
     09:58:39,854  INFO AbstractPoolBackedDataSource:462 - Initializing c3p0 pool...  com.mchange.v2.c3p0.PoolBackedDataSource@7eaf7e [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@6e3303f6 [ acquireIncrement -> 3, 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 -> 2xstr79341a29wl20dow|45ef76, idleConnectionTestPeriod -> 300,  initialPoolSize -> 5, maxAdministrativeTaskTime -> 0, maxConnectionAge -> 0, maxIdleTime -> 100, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 10, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 5, nestedDataSource ->  com.mchange.v2.c3p0.DriverManagerDataSource@f55211aa [ description -> null, 
     driverClass -> null,  factoryClassLocation -> null, identityToken ->  2xstr79341a29wl20dow|16d0064, jdbcUrl ->  `**Driver URL**`, properties -> {user=******, password=******} ],  preferredTestQuery -> null, propertyCycle -> 0, testConnectionOnCheckin -> false,  testConnectionOnCheckout -> false,   unreturnedConnectionTimeout -> 0,  usesTraditionalReflectiveProxies -> false; userOverrides: {} ], dataSourceName -> null,     factoryClassLocation -> null, identityToken -> 2xstr79341a29wl20dow|11400c8, numHelperThreads -> 3 ]
         09:58:59,854  WARN ThreadPoolAsynchronousRunner:608 -  com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@13780ae -- APPARENT DEADLOCK!!! Creating emergency threads for unassigned pending tasks!
         09:58:59,854  WARN ThreadPoolAsynchronousRunner:624 -    com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector@13780ae -- APPARENT   DEADLOCK!!!     
    Complete Status: 
    Managed Threads: 3
    Active Threads: 3
    Active Tasks: 
      com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@1e105a1 (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0)
      com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@389632 (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2)
      com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@10cd3b6 (com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1)
Pending Tasks: 
      com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@11f5d8c
      com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@36b8db
       Pool thread stack traces:
          Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0,5,main]
          java.lang.Thread.sleep(Native Method)
        com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1805)
    com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
     Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2,5,main]
     java.lang.Thread.sleep(Native Method)
     com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1805)
    com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
        Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1,5,main]
        java.lang.Thread.sleep(Native Method)
       com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1805)
    com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)

After This scary logging I am getting following Exception

     09:59:18,135  WARN BasicResourcePool:1841 - com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask@10cd3b6 -- Acquisition Attempt Failed!!! Clearing pending acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of allowed acquisition attempts (30). Last acquisition attempt exception: 
                java.sql.SQLException: ORA-01017: invalid username/password; logon denied

                       at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
                       at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
                       at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:283)
                       at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:278)
                       at oracle.jdbc.driver.T4CTTIoauthenticate.receiveOauth(T4CTTIoauthenticate.java:785)
                       at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:362)
                       at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:414)
                       at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:165)
                       at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:35)
                        at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:801)
                       at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:135)
                       at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182)
                       at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171)
                       at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0PooledConnectionPool.java:137)
                       at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
                       at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
                       at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
                       at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
        HibernateLog --> 09:59:18 WARN  org.hibernate.engine.jdbc.spi.SqlExceptionHelper - SQL  Error: 0, SQLState: null
       09:59:18,135  WARN SqlExceptionHelper:144 - SQL Error: 0, SQLState: null
       HibernateLog --> 09:59:18 ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - Connections could not be acquired from the underlying database!
       09:59:18,135 ERROR SqlExceptionHelper:146 - Connections could not be acquired from the underlying database!
       09:59:18,135 ERROR LoginDAO:111 - GetHostAddrCould not open connection
      org.hibernate.exception.GenericJDBCException: Could not open connection
           at   org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
           at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:126)
           at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:112)
           at  org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:235)
           at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:171)
           at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.connection(StatementPreparerImpl.java:63)
            at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:162)
            at org .hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:186)
            at  org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:160)
           at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1884)
           at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1861)
           at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1838)
           at org.hibernate.loader.Loader.doQuery(Loader.java:909)
           at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:354)
           at org.hibernate.loader.Loader.doList(Loader.java:2553)
           at org.hibernate.loader.Loader.doList(Loader.java:2539)
            at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2369)
           at org.hibernate.loader.Loader.list(Loader.java:2364)
           at org.hibernate.loader.criteria.CriteriaLoader.list(CriteriaLoader.java:126)
           at org.hibernate.internal.SessionImpl.list(SessionImpl.java:1682)
           at org.hibernate.internal.CriteriaImpl.list(CriteriaImpl.java:380)

**Here the username and password for database is absolutely correct **

After this exception I am not able to connect to database but In my log My application is trying to connect to database because I can see same kind of logging in my console.

Following are my configuration.

        c3p0-0.9.1.jar
        Hibernate-c3p0-4.3.5.Final.jar
        Struts2 -fullhibernatecoreplugin-2.2.2-GA.jar

I am using struts2 and Hibernate 4.3.5. Can somebody help me on this. I googles alto but cant find the right and VIVID solution.

Ans I can connect to the Database and my application is running good if I dont use connection pooling with c3p0

Thanks in advance.

EDIT : ** I am configuring hibernate with mixed properties Like I am passing the username and password by the java program. **My dumb guess is that c3p0 is not finding the username and password which are being set by java program, cause I have tried this connection pooling with sample console java program Where I can connect to DB even though I am getting Logon Denied exception

Here I need to set username and password programmatically, is there any way to get rid off this ? .

Answer 1

I'd suggest you provide your own C3P0 data source to Hibernate like so:

1. You create the C3P0 DataSource programatically:

   @org.springframework.context.annotation.Configuration
   public class C3P0Configuration {
   
       @Bean
       public ComboPooledDataSource dataSource() {
           ComboPooledDataSource actualDataSource = new ComboPooledDataSource();
           actualDataSource.setJdbcUrl("set-your-url");
           actualDataSource.setUser("set-your-user");
           actualDataSource.setPassword("set-your-user");
           actualDataSource.setMinPoolSize(0);
           actualDataSource.setMaxPoolSize(5);
           return actualDataSource;
       }
   }
   

2. You pass the dataSource to your Hibernate SessionFactory:

   <bean id="mySessionFactory" class="org.springframework.orm.hibernate3.LocalSessionFactoryBean" dependsOn="dataSource">
       <property name="dataSource" ref="dataSource"/>
       <property name="mappingResources">
           <list>
           <value>your-hibernate-config.hbm.xml</value>
           </list>
       </property>
       <property name="hibernateProperties">
           <value>
               hibernate.dialect=org.hibernate.dialect.Oracle10gDialect
           </value>
       </property>
   </bean>
   

   or if you build the session factory programatically too, make sure you supply this property:

   properties.put("hibernate.connection.datasource", dataSource);
   

If you don't use Spring, you would have to have access to the session factory instantiation process, because you need to build the data source prior to the session factory in order to supply it as the aforementioned Hibernate property.