Nested Prepared Statement Execution in Java loop throwing DSRA9110e. Statement is Closed. error

Question

I am using JSF 2, with Oracle 11gR2 and my application is deployed on Websphere 8.5. I have following code that throws subject error at line number mentioned. I used two solutions but both would throw same error.

public void insertEmployee(List<Employee> empList) throws SQLException
{
    Session sess = HibernateUtil.currentSession();
    Connection cn = sess.connection();
    String sqlIdentifier = "insert into EMP_DTLS (ID, EMP_NAME, ORG_NAME) values (?, ?, ?)";
    PreparedStatement statement = cn.prepareStatement(sqlIdentifier);
    cn.setAutoCommit(false);
    Employer em =(Employer) sess.get(Employer.class, empList.get(0).getEmployerId());

       for (Employee currEmployee: empList)
       {           
       statement.setLong(1,getEmpNewId(cn,sess));   // ERROR ON THIS LINE
       statement.setString(2,currEmployee.getEmpName()); 
        statement.setString(3, em.getName());
        statement.addBatch();
        }
        try 
        {    
           statement.executeBatch();
        }
       catch (Exception ex)
    {
        ex.printStackTrace();
        cn.rollback();
    }
    finally
    {
            cn.commit();
            statement.close();
            statement = null;
    }   
    } 
    public Long getEmpNewId(Connection cn, Session sess) throws SQLException
    {
    long empId = 1;
    String sqlIdentifier = "select SEQ_EMP_DTLS_ID.NEXTVAL from dual";
    PreparedStatement statement = null;
    statement = cn.prepareStatement(sqlIdentifier);
    synchronized( this ) 
    {
       ResultSet rs;
    try {
        rs = statement.executeQuery();
        if(rs.next())  empId = rs.getLong(1);
        statement.close();
        statement = null;
        sqlIdentifier = null;
    } catch (SQLException e) {
        e.printStackTrace();
    }

    }
     return empId;
}   

I used following solution too but same error:-

public void insertEmployee(List<Employee> empList) throws SQLException
{
    Session sess = HibernateUtil.currentSession();
    Connection cn = sess.connection();

    String sqlIdentifier = "insert into EMP_DTLS "
            + "(ID, EMP_NAME, ORG_NAME)"
            + "values (SEQ_EMP_DTLS_ID.NEXTVAL, ?, ?)";
    PreparedStatement statement = cn.prepareStatement(sqlIdentifier);
    cn.setAutoCommit(false);
      Employer em =(Employer) sess.get(Employer.class, empList.get(0).getEmployerId());  

       for (Employee currEmployee: empList)
       {           
          statement.setString(1,currEmployee.getEmpName()); // Error on this line 
          statement.setString(2, em.getName());
          statement.addBatch();
        }
      ....

This is the error :-

com.ibm.websphere.ce.cm.objectclosedexception dsra9110e statement is closed

What am i doing wrong.

Answer 1

Probably the connection is closed (or returned to the pool) at the end of the hibernate call, and thus also the statement is closed. My advice is to use a nativeQuery in hibernate, instead of using jdbc. It is also bad practice to change the connection hibernate gave you (setAutocommit)