Too many connections using Hibernate and mysql

Question

I am using Hibernate 3 and mysql sever 5.5 for myweb application with spring 3.0

I am getting exception as Too many connections......

My java file where I create session is as follows:

public class DBConnection {

    static{

    }

    public Session getSession(){

         Session session = null;
         SessionFactory sessionFactory= null;
         sessionFactory = new Configuration().configure().buildSessionFactory();
        session = sessionFactory.openSession();
        return session;

    }

}

and I call this method where I need session

as

Session session=new DBConnection().getSession();

and after

transaction.commit();

I close session by using

session.close();

please help me in solving problem.......

my hibernate.cfg.xml is :

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
<session-factory>
  <property name="hibernate.connection.driver_class">com.mysql.jdbc.Driver</property>
  <property name="hibernate.connection.url">jdbc:mysql://localhost:3306/dbname</property>
<property name="hibernate.connection.username">root</property>

  <property name="hibernate.connection.password">lax</property>
  <property name="hibernate.connection.pool_size">100</property>
  <property name="show_sql">true</property>
  <property name="dialect">org.hibernate.dialect.MySQLDialect</property>
  <property name="hibernate.hbm2ddl.auto">update</property>
  <property name=""></property>

  <property name="hibernate.connection.release_mode">on_close</property>

</session-factory>
</hibernate-configuration>

Answer 1

This is because you are using a connection pool which got created as soon as you build SessionFactory, but the connections are acquired only when you open a Session. Now, you are closing the session, due to which connections are released, but are not closed and are held up by the pool. Now, you are again creating a SessionFactory, hence creating a new pool, then getting a session, hence creating a new connection and so on.. which will eventually reach the maximum number of connections allowed.

What you have to do is using one Connection Pool (using one SessionFactory) and getting and releasing the connections from the same pool.

public class DBConnection {

      private static SessionFactory factory;
      static {
            factory = new Configuration().configure().buildSessionFactory();
      }

      public Session getSession() {
            return factory.openSession();
      }

      public void doWork() {
           Session session = getSession();
           // do work.
           session.close();
      }

     // Call this during shutdown
     public static void close() {
          factory.close();
     }
}