java - How can I prevent Hibernate + c3p0 + MySql creating large numbers of sleeping connections?

Question

I'm using GWT with Hibernate, c3p0 and MySQL to produce a web app with a limited audience (max 50 users per day). During testing I found that Hibernate was opening a connection with each session but not closing it, irrespective of use of the close() method.

My current configuration is as follows:

hibernate.connection.driver_class=com.mysql.jdbc.Driver
hibernate.connection.url=
hibernate.connection.username=
hibernate.connection.password=
hibernate.dialect=org.hibernate.dialect.MySQLDialect
hibernate.current_session_context_class=thread
hibernate.c3p0.min_size=1
hibernate.c3p0.max_size=1
hibernate.c3p0.timeout=10
hibernate.c3p0.max_statements=50
hibernate.c3p0.idle_test_period=10
hibernate.c3p0.unreturned_connection_timeout=1
hibernate.connection.provider_class=org.hibernate.connection.C3P0ConnectionProvider

With each new connection to the application a new pool is created. For example if I set the pool size to 3, 2 connections to the application result in 6 connections until the application is closed.

The intended behaviour is to simply close or reuse the connections after each transaction. How can I achieve this?

Answer 1

During testing I found that Hibernate was opening a connection with each session but not closing it, irrespective of use of the close() method

When using a connection pool, calling Connection#close() doesn't physically close the connection but return it to the pool for future reuse. In other words, the connection stays open and that's the whole point of using a pool.

---

I call the following: AnnotationConfiguration().buildSessionFactory().getCurrentSession();

Well, that's the problem. You are creating a SessionFactory over and over (each creating its own pool) while you should create it only once for the lifetime of your application. If you are not using any particular framework, this is typically done in some utility class (the famous HibernateUtil class).

The official Hibernate Tutorial has a very basic example of such a class. Or see this one which is a bit richer.