How to properly close and open a Hibernate session?

Question

I have the following method that inserts a large batch of records every few seconds. After some time of running I get errors like the following:

ERROR: Communications link failure

The last packet successfully received from the server was 523 milliseconds ago. The last packet sent successfully to the server was 8 milliseconds ago.

May 16, 2013 9:48:30 AM com.mchange.v2.c3p0.stmt.GooGooStatementCache checkinStatement INFO: Problem with checked-in Statement, discarding.

com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after statement closed.

The code I use to open and close connections is the following:

public DataControllerImp() {
    session = HibernateUtil.getSessionFactory().openSession();
}

@Override
public void saveMessage(ArrayList<Message> messages) {
    Transaction tx = session.beginTransaction();

    for (int i = 0; i < mesages.size(); i++) {
        Message message = messages.get(i);

        try {
            session.save(message);
            if (i % 75 == 0) { 
                // flush a batch of inserts and release memory:
                session.flush();
                session.clear();
            }
        } catch (Exception e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } finally {
            session.close();
        }
    }

    tx.commit();
}

I am also using c3p0 connection pooling. My configuration looks like:

<property name="connection.provider_class">org.hibernate.connection.C3P0ConnectionProvider</property>        
<property name="hibernate.c3p0.acquire_increment">1</property>
<property name="hibernate.c3p0.idle_test_period">300</property>
<property name="hibernate.c3p0.min_size">3</property>
<property name="hibernate.c3p0.max_size">20</property>
<property name="hibernate.c3p0.max_statements">50</property>
<property name="hibernate.c3p0.timeout">300</property>
<property name="hibernate.c3p0.acquireRetryAttempts">1</property>
<property name="hibernate.c3p0.acquireRetryDelay">250</property>

Am I opening and closing the connections incorrectly? Please let me know what I can change to stop from receiving this error and halting my program.

As an addition, see the official [Hibernate Reference](http://docs.jboss.org/hibernate/orm/4.3/manual/en-US/html/ch13.html#transactions-demarcation-nonmanaged) for the illustration of the session/transaction handling idiom. — informatik01, Mar 08 '14 at 19:41

score 2 · Accepted Answer · answered May 16 '13 at 17:58

2

    Transaction tx = session.beginTransaction();
    try {
        for (int i = 0; i < mesages.size(); i++) {
            Message message = messages.get(i);
            session.save(message);
            if (i % 75 == 0) { 
                // flush a batch of inserts and release memory:
                session.flush();
                session.clear();
            }
        }
        tx.commit();
    }catch (Exception e) {
        // TODO Auto-generated catch block
        e.printStackTrace();
        tx.rollBack();
    }finally{
        session.close();
    }
}

answered May 16 '13 at 17:58

Ziul

883
1
13
24

This would close the connection every time this method is called? Is that really the right place to put the close statement? – Atma May 16 '13 at 18:06
You were getting the exception cuz you were closing the session before commiting. About were to close session, that depends on the unit of work, the ideal escenario would be to open and close the session once per thread. – Ziul May 16 '13 at 19:19

score 0 · Answer 2 · answered Feb 06 '15 at 15:03

Rather make a generic method for any object type and pass the object. Add logic of any List too.

public void save(Object obj) {
    Session session = null;
    Transaction transaction = null;
    try {
        session = sessionFactory.getCurrentSession();
        transaction = session.beginTransaction();
        session.save(obj);
        session.flush();
        transaction.commit();
    } catch (JDBCException jde) {
        logger.fatal("Error occured in database communication", jde);
        transaction.rollback();
        throw new RuntimeException(jde);
    } finally {
        if (session.isOpen()) {
            session.close();
        }
    }
}

check to make sure `session` is not null in the finally block (in case getCurrentSession throws Exception) — Abdullah Bakhsh, Aug 23 '21 at 20:38

How to properly close and open a Hibernate session?

2 Answers2

Linked