org.apache.commons.dbcp.DelegatingPreparedStatement is closed

Question

org.apache.commons.dbcp.DelegatingPreparedStatement is closed

Could i know in which situations this exception will come.

I closed all result sets and prepared statements.

How can i solve this problem.

Code :

public int UpdateMovementLines(List<MaterialRequestIssuanceVO> mlinelist,String projId,String documentno,String user){

     int count = 1;
     int line = 0;
     String uom = null;
     String projLocatorId = null;
     String projWarehouseId = null;
     String warehouseLocatorId = null;
     String issuanceId = null;
     String movementLineId =null;
     String pinstanceId = null;
     String sqlQry = null;
     String whLocatorId = null;

    PreparedStatement ps = null;
    PreparedStatement ps1 = null;
    PreparedStatement ps2 = null;
    PreparedStatement ps3 = null;
    PreparedStatement ps4 = null;
    PreparedStatement ps5 = null;

    ResultSet rs = null;
    ResultSet rs1 = null;
    ResultSet rs2 = null;
    ResultSet rs3 = null;




    try{
        conn.setAutoCommit(false);

        try{

            sqlQry="INSERT INTO m_movement (m_movement_id, AD_CLIENT_ID, AD_ORG_ID, CREATED, CREATEDBY, UPDATED, UPDATEDBY," +
            "name, movementdate, posted, processing, move_fromto_locator,documentno) VALUES " +
            "(?,?,?,NOW(),?,NOW(),?,to_char(now(),'DD-MM-YYYY'),now(),?,?,?,?)";

            ps = conn.prepareStatement(sqlQry);

            for(MaterialRequestIssuanceVO movementvo:mlinelist){        
                issuanceId = movementvo.getIssuanceid();
                ps.setString(1, issuanceId);
                ps.setString(2,movementvo.getClientid());
                ps.setString(3,movementvo.getOrgid());
                ps.setString(4, movementvo.getCreatedby());
                ps.setString(5,movementvo.getUpdatedby());
                ps.setString(6,"N");
                ps.setString(7,"N");
                ps.setString(8,"N");
                ps.setString(9, documentno);           
                count=ps.executeUpdate();
            }

        } 
          catch (SQLException e) {
            // TODO Auto-generated catch block
            log4j.info("Inside DB Line saveMRIssuanceMovementData Exception"+e);
        }

          finally
          {
                try 
                {
                    ps.close();
                    log4j.info("Inside Line Finally");
                } catch (SQLException e) {
                    e.printStackTrace();
                }
          }



        ps=conn.prepareStatement("select c_uom_id as uom from m_product where m_product_id = ?");       

        for(MaterialRequestIssuanceVO movementvo:mlinelist){    
            line = line +10;
            ps.setString(1, movementvo.getMaterialid());

            rs = ps.executeQuery();
            while(rs.next())
            {
                uom = rs.getString("uom");
                log4j.info("Uom: "+uom);
            }


            try{

                ps2=conn.prepareStatement("select m_locator_id as locatorid from m_locator where m_warehouse_id = ?");
                ps2.setString(1, movementvo.getWarehouseId());
                rs2 = ps2.executeQuery();
                while(rs2.next())
                {
                    warehouseLocatorId = rs2.getString("locatorid");
                    log4j.info("warehouseLocatorId: "+warehouseLocatorId);
                }

            }catch(SQLException e){
                log4j.info("Warehouse Locator Exception: "+e);
            }
            finally{
                rs2.close();
                ps2.close();
            }


            try{

                ps3=conn.prepareStatement("select m_locator_id as locatorid from m_locator where m_warehouse_id=? and value like ?");

                ps3.setString(1, movementvo.getWarehouseId());
                ps3.setString(2, projId);
                rs3 = ps3.executeQuery();

                if(rs3.next())
                {
                    projLocatorId = rs3.getString("locatorid");
                    log4j.info("projLocatorId: "+projLocatorId);
                }

                else
                {
                    sqlQry="INSERT INTO m_locator (m_locator_id, AD_CLIENT_ID, AD_ORG_ID, CREATED, CREATEDBY, UPDATED, UPDATEDBY," +
                    "value, m_warehouse_id, priorityno, x,y, z) VALUES " +
                    "(?,?,?,NOW(),?,NOW(),?,?,?,?,?,?,?)";

                    ps4 = conn.prepareStatement(sqlQry);
                    try
                    {
                        whLocatorId = SequenceIdData.getUUID();
                        log4j.info("issueid: "+whLocatorId);
                        ps4.setString(1, whLocatorId);
                        log4j.info("Client Id: "+movementvo.getClientid());
                        ps4.setString(2,movementvo.getClientid());
                        log4j.info("Orgid: "+movementvo.getOrgid());
                        ps4.setString(3,movementvo.getOrgid());
                        ps4.setString(4, movementvo.getCreatedby());
                        ps4.setString(5,movementvo.getUpdatedby());
                        ps4.setString(6,projId);
                        ps4.setString(7,movementvo.getWarehouseId());
                        ps4.setInt(8,50);
                        ps4.setString(9,"x");
                        ps4.setString(10,"y");
                        ps4.setString(11,"z");

                        count=ps4.executeUpdate();

                        if(count == 1)
                            projLocatorId = whLocatorId;
                    }
                    catch(SQLException e)
                    {
                        log4j.info("M_Locator Exception: "+e);
                    }
                    finally
                    {
                        ps4.close();
                    }
                    log4j.info("whLocatorId projLocatorId: "+projLocatorId);
                }

            }catch(SQLException e){
                log4j.info("Locator Exception: "+e);
            }
            finally{


                rs3.close();
                ps3.close();
            }


            try{
            sqlQry="INSERT INTO m_movementline (m_movementline_id, AD_CLIENT_ID, AD_ORG_ID, CREATED, CREATEDBY, UPDATED, UPDATEDBY," +
            "m_movement_id, m_locator_id, m_locatorto_id, m_product_id,line, movementqty,c_uom_id,m_attributesetinstance_id) VALUES " +
            "(?,?,?,NOW(),?,NOW(),?,?,?,?,?,?,?,?,?)";

            ps1 = conn.prepareStatement(sqlQry);

            movementLineId = SequenceIdData.getUUID();
            ps1.setString(1, movementLineId);
            ps1.setString(2,movementvo.getClientid());
            ps1.setString(3,movementvo.getOrgid());
            ps1.setString(4, movementvo.getCreatedby());
                ps1.setString(5,movementvo.getUpdatedby());
                ps1.setString(6,issuanceId);
                ps1.setString(7,warehouseLocatorId);
                ps1.setString(8,projLocatorId);
                ps1.setString(9,movementvo.getMaterialid());
                ps1.setInt(10,line);
                ps1.setInt(11,Integer.parseInt(movementvo.getIssuedqty()));
                ps1.setString(12,uom);
                ps1.setString(13,"0");

            count=ps1.executeUpdate();

            }
            catch(SQLException e){
                log4j.info("Inside DB MoveLines SQLException"+e.getMessage());
            }
            finally
            {
                try 
                {   
                    ps1.close();
                    log4j.info("Inside movement Line Finally");
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            }

        sqlQry="INSERT INTO ad_pinstance (ad_pinstance_id, AD_CLIENT_ID, AD_ORG_ID, CREATED, CREATEDBY, UPDATED, UPDATEDBY," +
            "ad_process_id, record_id, isprocessing, ad_user_id,result) VALUES " +
            "(?,?,?,NOW(),?,NOW(),?,?,?,?,?,?)";

            ps5 = conn.prepareStatement(sqlQry);

            for(MaterialRequestIssuanceVO movementvo:mlinelist){

                try{    
                pinstanceId = SequenceIdData.getUUID();
                log4j.info("pinstanceId: "+pinstanceId);
                ps5.setString(1, pinstanceId);
                log4j.info("Client Id: "+movementvo.getClientid());
                ps5.setString(2,movementvo.getClientid());
                log4j.info("Orgid: "+movementvo.getOrgid());
                ps5.setString(3,movementvo.getOrgid());
                ps5.setString(4, movementvo.getCreatedby());
                ps5.setString(5,movementvo.getUpdatedby());
                ps5.setString(6,"122");
                ps5.setString(7,issuanceId);
                ps5.setString(8,"N");
                ps5.setString(9,user);
                ps5.setInt(10, Integer.parseInt("1"));

                count=ps5.executeUpdate();

                }
                catch(SQLException e){
                    log4j.info("saveMRIssuanceMovementData Line SQLException"+e.getMessage());
                }
                finally
                {
                    try 
                    {   
                        ps5.close();
                        log4j.info("Inside movement Line Finally");
                    } catch (SQLException e) {
                        e.printStackTrace();
                    }
                }


                try{

                    ps=conn.prepareStatement("select m_movement_post(?)");
                    ps.setString(1, pinstanceId);
                    rs = ps.executeQuery();

                    while(rs.next()){
                        log4j.info("Result Set: "+rs.getString(1));
                    }
                }catch(SQLException e){
                    log4j.info("Movement Post Exception: "+e);
                }
                finally{
                    ps.close();
                }
            }

        conn.commit();
    } catch (Exception e) {
        try {
            conn.rollback();
            count = 0;
        } catch (SQLException e1) {
            // TODO Auto-generated catch block
            e1.printStackTrace();
        }

        //Above Mensined Exception Catching hear
        log4j.info("Inside DB saveMRIssuanceMovementData Line SQLException"+e.getMessage());
    }


      finally
      {
          try 
          {
            if(conn != null)
                conn.close();
          }
          catch(SQLException e)
          {

          }
      }


    return count;

}

Answer 1

PreparedStatement is closed

You can get this exception when you're trying to (re)use a PreparedStatement while it has been closed. Check the line number of the first line in the stacktrace. It should hint which PreparedStatement it is talking about. Then backtrack its use in the code and fix code accordingly.

Judging the flood of code you've posted, I suspect that it's the ps5 which is been created before a for loop and been closed inside the for loop. Here's an extract of relevance from your code:

ps5 = conn.prepareStatement(sqlQry);
for (MaterialRequestIssuanceVO movementvo : mlinelist) {
    try {
        ps5.setString(1, string);
        ps5.executeUpdate();
    } finally {
        ps5.close(); // You're closing inside the loop!
    }
}

The next iteration in the loop won't be able to reuse the same PreparedStatement anymore. The fix is obvious: close it after completion of the for loop.

try {
    ps5 = conn.prepareStatement(sqlQry);
    for (MaterialRequestIssuanceVO movementvo : mlinelist) {
        ps5.setString(1, string);
        ps5.executeUpdate();
    }
} finally {
    ps5.close();
}

---

That said, logging all exceptions as Info or doing only e.printStackTrace() and suppressing them and continuing the code flow isn't always a good idea. Log them as Error and then hard-throw thereafter.

} catch (Exception e) {
    logger.error("Your message", e);
    throw e;
}

Rethrowing isn't needed for exceptions during close, but logging them as Warn is useful.

---

Last but not least, consider refactoring the exceptionally large method block into separate and sensible methods (tasks) ;)