java - org.apache.commons.dbcp.DelegatingPreparedStatement 关闭

Question

org.apache.commons.dbcp.DelegatingPreparedStatement is closed

我能知道在什么情况下会出现这个异常吗？

我关闭了所有结果集和准备好的语句。

我该如何解决这个问题。

代码:

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

当您尝试(重新)使用已关闭的 PreparedStatement 时，您可能会遇到此异常。检查堆栈跟踪中第一行的行号。它应该提示它正在谈论的PreparedStatement。然后回溯它在代码中的使用并相应地修复代码。

根据您发布的大量代码，我怀疑是 ps5 是在 for 循环之前创建的关闭 inside for 循环。以下是您的代码的相关摘录:

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

循环中的下一次迭代将无法再重用相同的 PreparedStatement。修复很明显:在 for 循环完成后关闭它。

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

---

也就是说，将所有异常记录为 Info 或仅执行 e.printStackTrace() 并抑制它们并继续代码流并不总是一个好主意。将它们记录为 Error，然后硬抛出。

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

关闭期间的异常不需要重新抛出，但将它们记录为 Warn 很有用。

---

最后但同样重要的是，考虑将异常大的方法 block 重构为单独且合理的方法(任务)；)