Hibernate version:2.1

Mapping documents:hibernate.properties file
## PostgreSQL

hibernate.dialect=net.sf.hibernate.dialect.PostgreSQLDialect
hibernate.connection.driver_class=org.postgresql.Driver
hibernate.connection.url=jdbc:postgresql://172.16.5.6/PhoenixColorSystem
hibernate.connection.username=postgres
hibernate.connection.password=postgres
hibernate.connection.provider_class = net.sf.hibernate.connection.C3P0ConnectionProvider
hibernate.c3p0.acquire_increment=1
hibernate.c3p0.idle_test_period=100
hibernate.c3p0.min_size=2
hibernate.c3p0.max_size=10
hibernate.c3p0.timeout=1800
hibernate.c3p0.max_statement=50
hibernate.c3p0.validate=false
hibernate.c3p0.idle_test_period=1800
hibernate.query.substitutions yes 'Y', no 'N'


#################################
### Hibernate Connection Pool ###
#################################

hibernate.connection.pool_size 100


Code between sessionFactory.openSession() and session.close():
public static Session getSession() throws Exception
{
Session pcsSession = (Session) localThread.get();
if (pcsSession == null)
{
if (sessionFactory == null)
{
init();
}
pcsSession = sessionFactory.openSession();
localThread.set(pcsSession);
}
return pcsSession;
}

// The init method
private static synchronized void init() throws Exception
{
if (sessionFactory != null)
{
/**
* Perfrom this to check if more than one user might be
* trying this at same time.
*/
return;
}
Configuration cfg = null;
try
{
cfg = new Configuration();
cfg.addClass(com.test.Test.class);
sessionFactory = cfg.buildSessionFactory();
logger.info("HibernateSession Initialized SessionFactory:" + sessionFactory);
}
catch (Exception pEx)
{
logger.error("Could not intialize Hibernate Session Factory: "+ pEx.getMessage());
throw new Exception("Could not intialize Hibernate session factory: "+ pEx.getMessage());
}
}

Full stack trace of any exception that occurs:
2004-11-08 17:20:55,531 [PoolThread-9] INFO - processing one-to-one association property references
2004-11-08 17:20:55,531 [PoolThread-9] INFO - processing foreign key constraints
2004-11-08 17:20:55,562 [PoolThread-9] INFO - Using dialect: net.sf.hibernate.dialect.PostgreSQLDialect
2004-11-08 17:20:55,562 [PoolThread-9] INFO - Maximim outer join fetch depth: 1
2004-11-08 17:20:55,562 [PoolThread-9] INFO - Use outer join fetching: true
2004-11-08 17:20:55,562 [PoolThread-9] INFO - Initializing connection provider: net.sf.hibernate.connection.C3P0ConnectionProvider
2004-11-08 17:20:55,578 [PoolThread-9] INFO - C3P0 using driver: org.postgresql.Driver at URL: jdbc:postgresql://172.16.5.6/PhoenixColorSystem
2004-11-08 17:20:55,578 [PoolThread-9] INFO - Connection properties: {user=postgres, password=postgres}
2004-11-08 17:20:55,640 [PoolThread-9] INFO - No TransactionManagerLookup configured (in JTA environment, use of process level read-write cache is not recommended)
2004-11-08 17:20:55,718 [PoolThread-9] ERROR - Initializing c3p0 pool... com.mchange.v2.c3p0.PoolBackedDataSource@185e155 [ connectionPoolDataSource -> com.mchange.v2.c3p0.WrapperConnectionPoolDataSource@16b152b [ acquireIncrement -> 1, acquireRetryAttempts -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure -> false, checkoutTimeout -> 0, connectionTesterClassName -> com.mchange.v2.c3p0.impl.DefaultConnectionTester, factoryClassLocation -> null, forceIgnoreUnresolvedTransactions -> false, idleConnectionTestPeriod -> 1800, initialPoolSize -> 2, maxIdleTime -> 1800, maxPoolSize -> 10, maxStatements -> 0, maxStatementsPerConnection -> 0, minPoolSize -> 2, nestedDataSource -> com.mchange.v2.c3p0.DriverManagerDataSource@1bd427 [ description -> null, driverClass -> null, factoryClassLocation -> null, jdbcUrl -> jdbc:postgresql://172.16.5.6/PhoenixColorSystem, properties -> {user=******, password=******} ] , preferredTestQuery -> null, propertyCycle -> 300, testConnectionOnCheckin -> false, testConnectionOnCheckout -> false, usesTraditionalReflectiveProxies -> false ] , factoryClassLocation -> null, numHelperThreads -> 3, poolOwnerIdentityToken -> 185e155 ]
2004-11-08 17:20:55,921 [PoolThread-9] ERROR - Rolling back potentially unresolved txn...

Name and version of the database you are using: PostgreSQL 7.4

The generated SQL (show_sql=true):

Debug level Hibernate log excerpt:

Hi.

As far as I can tell nothing is really wrong here. c3p0's messages are prefixed with ERROR becuase c3p0 logs to System.error, not because it has encountered an error. When c3p0 encounters an error, you'll see a clear stack trace, not just messages.

The two c3p0-generated messages here are c3p0's initialization dump (useful fro verifying that c3p0 is configured as you'd like) and "Rolling back potentially unresolved txn...", which is a message the latest prerelease version of c3p0 generates when you close a transactional Connection on which your last operation prior to close was not commit(), rollback(), or setAutoCommit( ... ). This is to call attention to the fact that c3p0 may be automatically rolling back some work because you failed to commit() or rollback() yourself. It's a warning; you can choose to ignore it, or change your code so it doesn't happen.

It looks like there are a couple of minor errors in your hibernate.properties: hibernate.c3p0.idle_test_period is repeated and hibernate.c3p0.max_statement should be hibernate.c3p0.max_statements.

hope this helps,
Steve (c3p0 maintainer)

Hi Steve

Thanks a Lot for the explanation. So my c3P0 is working fine. One Small doubt, How do i see that the connection are being pooled, like no where i am able to see the number of connections in the pool being displayed in the Log. Should i need to configure any other thing to see the current number of connections in the pool.

Thanks in advance.

Bapi

Bapi,

For the moment, c3p0 does not log its Connection count and status, but you can get that information programmatically. If you cast your c3p0 DataSource to a com.mchange.v2.c3p0.PooledDataSource, you can call methods like getNumConnections(), getNumBusyConnections(), and getNumIdleConnections().

Hope this helps!

Steve

Steve,

How can i get my c3p0 DataSource instance.
My Base DAO is as follows..
public class BaseDAO
{
private static Logger logger = Logger.getLogger(BaseDAO.class);
private static SessionFactory sessionFactory = null;
private static final ThreadLocal localThread = new ThreadLocal();

static
{
try
{
init();
}
catch (Exception pEx)
{
logger.error("Couldnot initialize the BaseDAO.class: " + pEx.getMessage());
}
}

public BaseDAO()
{
super();
}

public static Session getSession() throws Exception
{
Session testSession = (Session) localThread.get();
if (testSession == null)
{
if (sessionFactory == null)
{
init();
}
testSession = sessionFactory.openSession();
localThread.set(testSession);
}
return testSession;
}

private static synchronized void init() throws Exception
{
if (sessionFactory != null)
{
return;
}
Configuration cfg = null;
try
{
cfg = new Configuration();

cfg.addClass(test.class);
sessionFactory = cfg.buildSessionFactory();
logger.info("HibernateSession Initialized SessionFactory:" + sessionFactory);
}
catch (Exception pEx)
{
logger.error("Could not intialize Hibernate Session Factory: "+ pEx.getMessage());
throw new Exception("Could not intialize Hibernate session factory: "+ pEx.getMessage());
}
}

public static void closeSession() throws HibernateException, SQLException
{
Session testSession = (Session) localThread.get();
String sessionHashCode = testSession.toString();
localThread.set(null);
if (testSession != null)
{
testSession.close();
}
}

bapsred,

I'm not sure there's an easy way to do this from within hibernate. If an app server is managing your c3p0 Connection pool, you could look it up under the same name that your app uses (from within the same JVM, but from some external-to-hibernate servlet or somesuch), cast it to a PooledDataSource, and call the appropriate methods. But if it's all within hibernate, I don't recall there being an easy way to get at the underlying DataSource, since hibernate defines a standard ConnectionProvider interface to abstract away the details of Connection acquisition.

(I could be wrong about this. I'm c3p0's maintainer, and a hibernate admirer, but no hibernate expert.)

good luck!
Steve