3 level nested loop query wont run outer loop

Question

I have 3 level nested loop which is supposed to execute a query, part of a bigger webservice but the issue is that the outermost loop is executed only half the code is as follow:

       PreparedStatement ps = conn.prepareStatement("INSERT INTO malattia (nome, eta, descrizione, sesso, etnia, sintomi) values (?, ?, ?, ?, ?, ?)");


              if(sexarra.length == 2){

                  for(int k=0; k<sexarra.length; k++)  {
                    for(int i=0; i<selec.length; i++){
                      for(int j=0;j<sintom.length;j++){

                        ps.setString(1, malattia);
                        ps.setInt(2, eta);
                        ps.setString(3, descrizione);
                        ps.setString(4, sexarra[k] );
                        ps.setString(5, selec[i]);
                        ps.setString(6, sintom[j]);
                        ps.executeUpdate();
                      }
                    }
                  }
             }
              else {

                  for(int i=0; i<selec.length; i++){
                        for(int j=0;j<sintom.length;j++){

                        ps.setString(1, malattia);
                        ps.setInt(2, eta);
                        ps.setString(3, descrizione);
                        ps.setString(4, sexarra[0] );
                        ps.setString(5, selec[i]);
                        ps.setString(6, sintom[j]);
                        ps.executeUpdate();

                       }

                  }                   
                 }

                ps.close();
                 //ds.close();
                conn.close();
                ris = "si";
              } catch (SQLException e) {
                    System.out.println("Errore: " + e.getMessage());

                    return ris;
              }
        }
        return ris;
}

The problem lays in the first part where sexarra.lengh=2, precisely in the most outer loop which is supposed to iterate for two times, but what program throws an exception as soon the loops related to k=0 are done, I mean it doesn't execute the loops related to K=1. I have been having problems with nested loops and preparedstatement for days now and this is the latest one, where I don't know what am I doing wrong. Thanks for your time and effort people.

Can you format your code correctly so we could read it better? Also, are you saying that there is an exception thrown? In that case, please also put the exception as part of the question. — momo, Aug 25 '11 at 02:33
@momo i only know that an exception is thrown because the catch(SQLException e) is activated but i don't exactly see the exception since this code is running on the server side of the webservice, only "ris" is returned to the client side. — tutak, Aug 25 '11 at 02:55
@Nikita, I got some issues with registration and usernames, Almost all the questions are old when i first started using the site, didn't know how it works BUT i did gave feedback on how i ultimately solved the problem, now I'm once again using the old username, I still don't have the rights to select my own answer as a solution. — tutak, Aug 25 '11 at 03:02
running time of `O(n^3)`. Its terrible I believe. but seems like u need it :( — Kowser, Aug 25 '11 at 03:03
well sexarra has a fixed size of <=2 so it is O(n^2) or am i wrong? :) — tutak, Aug 25 '11 at 03:08
@Student - look in the server's log files for the exception. — Stephen C, Aug 25 '11 at 04:17
@Stepehenc this is what i get in catalina.out: Errore: Duplicate entry 'cancer-Caucasian-Schizofrenia' for key 'PRIMARY' — tutak, Aug 25 '11 at 06:01
@Stephen you solved it!!! Actually since the table had only three primary keys malattia,sintomi and selec, the query was refused since having different values for sexarra didn't make a difference, to have the records stored atleast one of the primary was supposed to have different values.Thank you so much!! How can mark this as answered now? — tutak, Aug 25 '11 at 06:16
@Student - create an Answer yourself that says "I found the solution by looking in the server logs", and then accept it. — Stephen C, Aug 25 '11 at 06:29

score 1 · Answer 1 · edited May 23 '17 at 11:47

1

I am yet to try the logic of your program, but I would suggest you look at Reusing a PreparedStatement multiple times

I have never used the case of resetting prepared statement parameters without grabbing the instance again (poolpreparedstatement can be used to cache the statement). That might be the issue here

edited May 23 '17 at 11:47

Community

1
1

answered Aug 25 '11 at 02:37

Fazal

2,991
7
29
37

I need the preparedstatement to run (sintom.lengh*selec.length)*sexarra.length times where sexarra.length==2. but the outter loop would run only half that times(sintom.length*selec.length)*1 times. – tutak Aug 25 '11 at 02:52
My suggestion was to close and load the prepared statement after every ps.update. – Fazal Aug 25 '11 at 14:40
Thank you, but i resolved the problem. I can't mark it as answered till tomorrow, coz it won't allow me to do so. – tutak Aug 25 '11 at 21:30

score 0 · Accepted Answer · answered Aug 25 '11 at 21:28

I found the answer by looking into server logs, advised by @ Stephen C in the comments, the error was caused due to the refusal of execution of query, the outter loop was passing different value while the other values remained the same, but value passed by the outter did not consist Primary Key of the table therefore, the query was effectively duplicate.

3 level nested loop query wont run outer loop

2 Answers2