CODEWITHSUNDEEP
javamysqlsqlweb-servicesnested-loops
rosa
rosa

Reputation: 55

mySQL nested loop wont execute

I'm working on a SOAP based webservice where in a part of it i have to perform some queries on the database using nested loop, the problem is that the inner loop just gets executed for ONE time only, before giving up.This is the code:

          for(int i=0; i<selec.length; i++){
                for(int j=0; j<sintom.length;j++){
                    var[(i*sintom.length)+j] = "INSERT INTO malattia (nome, eta,  descrizione, sesso, etnia, sintomi) "
                + "VALUES ('" + malattia + "','" + eta + "','" + descrizione + "','" +  sexarra[0] + "','" + selec[i] + "','" + sintom[j] + "')";
        }

      }

This is where the queries are supposed to get executed: 

        if (errore.equals("")) {
              try {
                    Statement st = conn.createStatement();
      for(int i=0; i<selec.length; i++){
        for(int j=0;j<sintom.length;j++){

                     st.executeUpdate(var[(i*sintom.length)+j]);}}

What happens is that no matter the size of select it will work fine as long as the length of sintom is 1,bigger than 1 and it wont work.

Thanks for your expert advices, always appreciated!

Upvotes: 0

Views: 468

Answers (2)

umbr
umbr

Reputation: 440

Try to use PreparedStatement and its Batch capability instead of plain query to simplify code and prevent SQL-injection.

Upvotes: 1

JB Nizet
JB Nizet

Reputation: 691755

Your use-case is a perfect example of a case where a prepared statement should be used. Read more about them in the JDBC tutorial.

Using a prepared statement would allow

  • avoiding SQL injection attacks. You should never use string concatenation to build your SQL query. A malicious user could enter some special value which would completely change the meaning of your query. A non-malicious user could enter special characters (quotes, for example) which would make the query fail because it's not syntaxically correct.
  • letting the database prepare the execution plan only once, for all the insert queries you're executing. Indeed, the query is always the same. Only the parameters change.

So, the code should look like this:

PreparedStatement ps = conn.prepareStatement("INSERT INTO malattia (nome, eta, ...) values (?, ?, ...)");
for (int i= 0; ...) {
    for (int j = 0; ...) {
        ps.setString(1, malattia);
        ps.setString(2, eta);
        ...
        ps.executeUpdate();
    }
}

Upvotes: 1

Related Questions