java - 为什么我得到列表中每个值的多次迭代

Question

这个问题已经有答案了:

Getting java.sql.SQLException: Operation not allowed after ResultSet closed (2 个回答)

已关闭 8 年前。

我有一个应用程序，我正在执行一些数据库操作。并且根据这些值我也正在执行一些计算。但是我正在对列表中的每个值进行多次迭代

for(int i=0;i<consultantMailList.size();i++){

             String consultantRatio = " select count(distinct CANDYS.candidateId) from "
                     +" (Select C.candidateID,J.joborderID from db_candidatenote C, db_jobordernote J " 
                     +" where C.noteid=J.noteid and C.action like 'Engaged' " 
                     +" and C.dateadded between "+ "\""+ new java.sql.Timestamp(startDate.getTime())+ "\" and "+ "\""+ new java.sql.Timestamp(endDate.getTime())+ "\"  ) N,"
                     +" (Select distinct Note.candidateId, JNote.jobOrderID " 
                     +" from db_candidatenote Note,db_jobordernote JNote, corporateuser User where " 
                     +" Note.noteid=JNote.noteid "
                     +" and Note.commentingPersonID = User.userid " 
                     +" and User.name= "+"\""+consultantNameList.get(i)+"\""
                     +" and Note.action='Sourcing') CANDYS "
                     +" where N.candidateID= CANDYS.candidateId "
                     +" and N.joborderid=CANDYS.jobOrderID "                

                     +" union"

                     +" select count(distinct CANDYS.candidateId) from "
                     +" (Select C.candidateID,J.joborderID from db_candidatenote C, db_jobordernote J " 
                     +" where C.noteid=J.noteid and C.action like 'Qualification' " 
                     +" and C.dateadded between "+ "\""+ new java.sql.Timestamp(startDate.getTime())+ "\" and "+ "\""+ new java.sql.Timestamp(endDate.getTime())+ "\"  ) N,"
                     +" (Select distinct Note.candidateId, JNote.jobOrderID " 
                     +" from db_candidatenote Note,db_jobordernote JNote, corporateuser User where " 
                     +" Note.noteid=JNote.noteid "
                     +" and Note.commentingPersonID = User.userid " 
                     +" and User.name= "+"\""+consultantNameList.get(i)+"\""
                     +" and Note.action='Sourcing') CANDYS "
                     +" where N.candidateID= CANDYS.candidateId "
                     +" and N.joborderid=CANDYS.jobOrderID "

                     +" union "

                     +" select count(distinct CANDYS.candidateId) from "
                     +" (Select C.candidateID,J.joborderID from db_candidatenote C, db_jobordernote J " 
                     +" where C.noteid=J.noteid and C.action like 'Internal Submission' "
                     +" and C.dateadded between "+ "\""+ new java.sql.Timestamp(startDate.getTime())+ "\" and "+ "\""+ new java.sql.Timestamp(endDate.getTime())+ "\"  ) N,"
                     +" (Select distinct Note.candidateId, JNote.jobOrderID " 
                     +" from db_candidatenote Note,db_jobordernote JNote, corporateuser User where " 
                     +" Note.noteid=JNote.noteid "
                     +" and Note.commentingPersonID = User.userid " 
                     +" and User.name= "+"\""+consultantNameList.get(i)+"\""
                     +" and Note.action='Sourcing') CANDYS "
                     +" where N.candidateID= CANDYS.candidateId "
                     +" and N.joborderid=CANDYS.jobOrderID "

                     +" union "

                    +" select count(distinct CANDYS.candidateId) from sendout S,"
                    +" (Select distinct Note.candidateId, JNote.jobOrderID " 
                    +" from db_candidatenote Note,db_jobordernote JNote, corporateuser User where " 
                    +" Note.noteid=JNote.noteid "
                    +" and Note.commentingPersonID = User.userid " 
                    +" and User.name= "+"\""+consultantNameList.get(i)+"\""
                    +" and Note.action='Sourcing' ) CANDYS "
                    +" where S.candidateID= CANDYS.candidateId "
                    +" and S.joborderid=CANDYS.jobOrderID "
                    +" and S.dateadded between "+ "\""+ new java.sql.Timestamp(startDate.getTime())+ "\" and "+ "\""+ new java.sql.Timestamp(endDate.getTime())+ "\" "

                    +" union"

                    +"  select count(distinct CANDYS.candidateId) from "
                    +"  (Select C.candidateID,J.joborderID from db_candidatenote C, db_jobordernote J " 
                    +"  where C.noteid=J.noteid and C.action like '%Interview%' " 
                    +"  and C.dateadded between "+ "\""+ new java.sql.Timestamp(startDate.getTime())+ "\" and "+ "\""+ new java.sql.Timestamp(endDate.getTime())+ "\"  ) N,"
                    +"  (Select distinct Note.candidateId, JNote.jobOrderID " 
                    +"  from db_candidatenote Note,db_jobordernote JNote, corporateuser User where " 
                    +"  Note.noteid=JNote.noteid "
                    +"  and Note.commentingPersonID = User.userid " 
                    +"  and User.name= "+"\""+consultantNameList.get(i)+"\""
                    +"  and Note.action='Sourcing') CANDYS "
                    +"  where N.candidateID= CANDYS.candidateId "
                    +"  and N.joborderid=CANDYS.jobOrderID "

                    +"  union "

                    +"  select count(distinct CANDYS.candidateId ) from placement P,(Select distinct Note.candidateId, JNote.jobOrderID " 
                    +"  from db_candidatenote Note,db_jobordernote JNote, corporateuser User where " 
                    +"  Note.noteid=JNote.noteid "
                    +"  and Note.commentingPersonID = User.userid " 
                    +"  and User.name= "+"\""+consultantNameList.get(i)+"\""
                    +"  and Note.action='Sourcing') CANDYS "
                    +"  where P.candidateID= CANDYS.candidateId "
                    +"  and P.joborderid=CANDYS.jobOrderID "
                    +"  and P.dateadded between "+ "\""+ new java.sql.Timestamp(startDate.getTime())+ "\" and "+ "\""+ new java.sql.Timestamp(endDate.getTime())+ "\" " ;


                     String countAnalystPlacement ="SELECT COUNT(*) FROM placement where analyst like "+"\""+consultantNameList.get(i)+"\" and dateAdded between "+ "\""+ new java.sql.Timestamp(startDate.getTime())+ "\" and "+ "\""+ new java.sql.Timestamp(endDate.getTime())+ "\"" ;

                     rsServeResource7 = st.executeQuery(countAnalystPlacement);
                      while(rsServeResource7.next()){
                          placementActual = rsServeResource7.getInt(1);
                      }

                      System.out.println("Placement Actual for :"+consultantNameList.get(i)+"--"+placementActual);

                    //System.out.println(consultantRatio);
                    rsServeResource2 = st.executeQuery(consultantRatio);            
                    while (rsServeResource2.next()) {                           

                    // these variables will be divided by actual milestone numbers like source to engage / engage etc within that time range ..

                    if(rsServeResource2.getString(1)!=null){
                    sourceToEngage = Integer.parseInt(rsServeResource2.getString(1));
                    }else if(rsServeResource2.getString(2)!=null){
                    sourceToQualification = Integer.parseInt(rsServeResource2.getString(2));
                    }else if(rsServeResource2.getString(3)!=null){
                    sourceToIs = Integer.parseInt(rsServeResource2.getString(3));
                    }else  if(rsServeResource2.getString(4)!=null){
                    sourceToPresent = Integer.parseInt(rsServeResource2.getString(4));
                    }else if(rsServeResource2.getString(5)!=null){
                    sourceToInterview = Integer.parseInt(rsServeResource2.getString(5));
                    }else if(rsServeResource2.getString(6)!=null){
                    sourceToPlacament = Integer.parseInt(rsServeResource2.getString(6));
                }


                System.out.println("Source to engage for :"+consultantNameList.get(i)+"-->"+sourceToEngage);
                System.out.println("Source to qualification for :"+consultantNameList.get(i)+"-->"+sourceToQualification);  
                System.out.println("Source to IS for :"+consultantNameList.get(i)+"-->"+sourceToIs);    
                System.out.println("Source to Present for :"+consultantNameList.get(i)+"-->"+sourceToPresent);  
                System.out.println("Source to interview for :"+consultantNameList.get(i)+"-->"+sourceToInterview);  
                System.out.println("Source to placement for :"+consultantNameList.get(i)+"-->"+sourceToPlacament);  



                String sourcingSql = "select count(distinct candidateId) from db_candidatenote Note,corporateuser User where  Note.commentingPersonID =User.userid and User.name like "+"\""+consultantNameList.get(i)+"\" and  action IN ('Sourcing','Sourcing-Green') and Note.dateAdded between "+ "\""+ new java.sql.Timestamp(startDate.getTime())+ "\" and "+ "\""+ new java.sql.Timestamp(endDate.getTime())+ "\"" ;
                rsServeResource6 = st.executeQuery(sourcingSql);
                    while(rsServeResource6.next()){

                        if(rsServeResource6.getString(1)!=null){                                
                            sourcingActual = Integer.parseInt(rsServeResource6.getString(1));                           
                        } 
                    }
                System.out.println("Actual number of Sourcing :"+sourcingActual);

         /******************    value modification done here *********************************/
                    if(sourceToEngage!=0){
                        sourceToEngage = sourcingActual/sourceToEngage;
                    }if(sourceToQualification !=0){
                        sourceToQualification =sourcingActual/qualificationActual;
                    }if(isSubmissionActual !=0){
                        sourceToIs = sourcingActual / isSubmissionActual;
                    }if(presentActual !=0){
                        sourceToPresent = sourcingActual /presentActual;
                    }if(interviewedActual !=0){
                        sourceToInterview = sourcingActual/interviewedActual;
                    }if(placementActual !=0){
                        sourceToPlacament =  sourcingActual/placementActual;
                    }
            /*********************************** Ends Here ************************************/        
                    System.out.println("sourceToEngageRatio" +sourceToEngage);
                    System.out.println("sourceToQualificationRatio" +sourceToQualification);
                    System.out.println("sourceToIsRatio" +sourceToIs);
                    System.out.println("sourceToPresentRatio" +sourceToPresent);
                    System.out.println("sourceToInterviewRatio" +sourceToInterview);
                    System.out.println("sourceToPlacementRatio" +sourceToPlacament);

                }       

                    if(rsServeResource2.getRow() % 2==0){
                        analystRatioTable = analystRatioTable + "<tr style=\"border-bottom: 2px solid #b6b6b4;\"><td style=\"color: #1391d7;\">"+consultantNameList.get(i)+"</td><td>"+sourceToEngage+":"+"1"+"</td><td>"+sourceToQualification+":"+"1"+"</td><td>"+"</td><td>"+sourceToIs+":"+"1"+"</td><td>"+sourceToPresent+":"+"1"+"</td><td>"+sourceToInterview+":"+"1"+"</td><td>"+sourceToPlacament+":"+"1"+"</td></tr>";
                    } else {
                        analystRatioTable = analystRatioTable + "<tr style=\"border-bottom: 2px solid #b6b6b4;\"><td style=\"color: #1391d7;\">"+consultantNameList.get(i)+"</td><td>"+sourceToEngage+":"+"1"+"</td><td>"+sourceToQualification+":"+"1"+"</td><td>"+"</td><td>"+sourceToIs+":"+"1"+"</td><td>"+sourceToPresent+":"+"1"+"</td><td>"+sourceToInterview+":"+"1"+"</td><td>"+sourceToPlacament+":"+"1"+"</td></tr>";
                    }

                    System.out.println("Table data created :"+analystRatioTable);

                 }           

我多次获得每个值，为什么 .LIst 大小为 5

这是我的代码，我在该行中遇到了异常

但我不明白我错在哪里。请有人帮忙

Answer 1

您声称获得异常的代码确实出现在上面较大的代码中。

但是，我在其他地方看到了问题:

if(rsServeResource2.getRow() % 2==0)

在执行不同的查询并获得新结果集 - rsServeResource6 后，您可以访问 rsServeResource2 结果集 - 这意味着 rsServeResource2 是已关闭且无法访问。

编辑:

这不是唯一的问题。错误的缩进使得很难注意到 rsServeResource6 = st.executeQuery(commerceSql); 位于迭代前一个结果集的循环内。这会导致 rsServeResource2 关闭。如果必须同时迭代两个结果集，请使用不同的 Statement 对象来执行它们。