CODEWITHSUNDEEP
javajdbcprepared-statementderby
Xenos29
Xenos29

Reputation: 17

PreparedStatement not executing with correct query

Does placing a prepared statement inside a for loop with varying query strings somehow breaks it?

I have this: 

if(btn.equals("Continue")){
                String[] vals1 = request.getParameterValues("Vals");
                String count = "";
                queryStr = AniModel.deleteQuery(vals1);
                PreparedStatement ps = conn.prepareStatement(queryStr);
                int update = ps.executeUpdate();
                count = "" + update;

                    request.setAttribute("query",queryStr);
                    request.setAttribute("count",count);
                    RequestDispatcher dispatch = request.getRequestDispatcher("success4.jsp");
                    dispatch.forward(request, response);
            }

It takes several array elements in which it uses on the call of the DELETE query that i created: 

public static String deleteQuery(String[] title){
    String qry="DELETE FROM AnimeDatabase WHERE ";
    for(int i=0;i < title.length;i++){
        if (i>0) qry += " OR "; 
        qry += "ANI_TITLE='" + title[i] + "'";
    }
    return qry;
}

It gets the array values from this:

<%



                String[] values = request.getParameterValues("cbg");
                int countme=0;
                int numb=1;
                for(int i=0;i < values.length;i++){
                    countme++;
                    numb=countme;

                %>
                <tr><td><input type="hidden" name="Vals" value="<%out.println(values[i]);%>"id="vs_<%=(numb)%>"><%=values[i]%></td></tr>
                <% 
                    }
            %>

It has the correct query input in the end but its just not executing the update on the database.

EDIT: I wish to achieve that when i do execute the command, it will do the looping and execute the statements depending on how many values are there in the array until it ends.

Set it to conn.setAutoCommit(false); and place conn.commit(); inside the Continue code block. Still doesn't work.

Upvotes: 0

Views: 1390

Answers (1)

George
George

Reputation: 1263

A couple of points not knowing what you actually wish to achieve:

  1. If you want to generate a query to DELETE records can you not create a single query encompassing the criteria to execute it once?
  2. Are you by any chance having an issue with transaction commit? Are you starting a transaction?
  3. If your query is taking parameters I would prepare the SQL string and statement outside the loop and in the loop set the parameters before executing.
  4. Also make sure you don't have any other queries or issues in environment around the code shown.

There is however nothing wrong with your logic and although I find pattern (3) a bit more fast from my experience I cannot see a direct issue with the code right now.

(however there are some other issues there like count being a string for example but it may be a naming issue. though the update would return a number so you are converting again to a string but not actually doing anything with the count var which in essence will contain only your last number of rows effected in a string format)

For option 1 (and your edit). There is room for optimisation:

String[] vals1 = request.getParameterValues("Vals");
int count = 0;
String sql = "DELETE FROM AnimeDatabase WHERE ";

for(int i=0; i < vals1.length; i++) {

    if (i>0) sql += " OR "; 
    sql += "ANI_TITLE='" + vals1 + "'";

}

PreparedStatement ps = conn.prepareStatement(sql);
int update = ps.executeUpdate();
count = update;

Upvotes: 1

Related Questions