Hibernate problem: "Too many connections"

Question

I am getting the following error:

.
.
.
.
6844 [main] WARN org.hibernate.util.JDBCExceptionReporter - SQL Error: 1040, SQLState: 08004
6844 [main] ERROR org.hibernate.util.JDBCExceptionReporter - Data source rejected establishment of connection,  message from server: "Too many connections"
Exception in thread "main" org.hibernate.exception.JDBCConnectionException: Cannot open connection
    at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:99)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
    at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:52)
    at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:449)
    at org.hibernate.jdbc.ConnectionManager.getConnection(ConnectionManager.java:167)
    at org.hibernate.jdbc.JDBCContext.connection(JDBCContext.java:160)
    at org.hibernate.transaction.JDBCTransaction.begin(JDBCTransaction.java:81)
    at org.hibernate.impl.SessionImpl.beginTransaction(SessionImpl.java:1473)
    at sun.reflect.GeneratedMethodAccessor121.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.hibernate.context.ThreadLocalSessionContext$TransactionProtectionWrapper.invoke(ThreadLocalSessionContext.java:345)
    at $Proxy0.beginTransaction(Unknown Source)
    at com.mycomp.myproj.matcher.Matcher.findMatch(Matcher.java:228)
    at com.mycomp.myproj.Confidence.Confidence.main(Confidence.java:160)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Data source rejected establishment of connection,  message from server: "Too many connections"
    at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
    at sun.reflect.NativeConstructorAccessorImpl.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    at com.mysql.jdbc.Util.getInstance(Util.java:384)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1015)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
    at com.mysql.jdbc.MysqlIO.doHandshake(MysqlIO.java:1105)
    at com.mysql.jdbc.ConnectionImpl.createNewIO(ConnectionImpl.java:2186)
    at com.mysql.jdbc.ConnectionImpl.<init>(ConnectionImpl.java:787)
    at com.mysql.jdbc.JDBC4Connection.<init>(JDBC4Connection.java:49)
    at sun.reflect.GeneratedConstructorAccessor16.newInstance(Unknown Source)
    at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(Unknown Source)
    at java.lang.reflect.Constructor.newInstance(Unknown Source)
    at com.mysql.jdbc.Util.handleNewInstance(Util.java:409)
    at com.mysql.jdbc.ConnectionImpl.getInstance(ConnectionImpl.java:357)
    at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at java.sql.DriverManager.getConnection(Unknown Source)
    at org.hibernate.connection.DriverManagerConnectionProvider.getConnection(DriverManagerConnectionProvider.java:133)
    at org.hibernate.jdbc.ConnectionManager.openConnection(ConnectionManager.java:446)
    ... 11 more

The hibernate bits of my program are like this:

for loop_1        // this will execute say 2000 times 
{
    for loop_2       // this will execute say 1000 times
    {
        // Opening 2 sessions for 2 different databases

        Configuration config_1 = new Configuration().configure("Hibernate_1.cfg.xml");
        SessionFactory sessionFactory_1 = config_1.buildSessionFactory();
        Session session_1 = sessionFactory_1.getCurrentSession();
        session_1.beginTransaction();

        Configuration config_2 = new Configuration().configure("Hibernate_2.cfg.xml");
        SessionFactory sessionFactory_2 = config_2.buildSessionFactory();
        Session session_2 = sessionFactory_2.getCurrentSession();
        session_2.beginTransaction();

        doInsertDb_1(some_object_1, session_1);

        doUpdateDb_2(some_object_2, session_2);
    }
}

    public int doInsertDb_1(Object obj, Session session) {

        try {

            session.save(obj);
            session.flush();
            session.getTransaction().commit();

            return 1;

        } catch (Exception ex) {
            ex.printStackTrace();
            return 0;
        }
    }

    public int doUpdate_2(Object obj, Session session) {

        try {

            Query query = session.createQuery("" <Creating some query> );
            query.executeUpdate();
            session.getTransaction().commit();

            return 1;

        } catch (Exception ex) {
            ex.printStackTrace();
            return 0;
        }
    }

It is obvious that somewhere I am not closing the connections properly. I am not able to figure out where? Can someone please guide me with this?

Thanks a lot.

Answer 1

Are you sure you need to create both sessions each time through the loop? Creating them once, prior to both loops, would likely solve your problem and speed-up your code. If you insist on opening them in the inner loop, though, make sure to close() them afterwards, since that should at least free up the database connections you're opening (but never closing.)

EDIT: You should hold off on committing your changes until you've made them all, so that you won't prematurely close the connections.

EDIT 2: Since you need the results of each update for processing further updates, you can at least move the creation of the SessionFactories outside the loops.

Answer 2

Try to create sessions outside the loop