hibernatetransactionsdatabase-connectionc3p0hibernate-session

How to properly close and open a Hibernate session?


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.


Solution

  •     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();
        }
    }