sql update statement does not execute properly

Question

I'm having problem with an update query. following is the function I'm calling:

    public static void update(ArrayList<String> arr, int id)
    {   
        Connection conn = null;
        System.out.println(lineArr.size());
        try
        {

        Class.forName ("com.mysql.jdbc.Driver").newInstance();
        conn = DriverManager.getConnection (url,userName,password);

        for(int i=0;i<arr.size();i++)
        {
        String currentLine = arr.get(i);
        //process the string
        int lastSlash=currentLine.lastIndexOf("\\"); 
        String location=currentLine.substring(0, lastSlash+1);
            location=location.replace("\\", "\\\\");
        String name=currentLine.substring(lastSlash+1);         

        String query=" UPDATE tbl1 " +
                 " set tbl1.id= ?"+
                 " where tbl1.sid =(select tbl2.sid from tbl2 " +
                       "where tbl2.cl=? and tbl2.cl2=?)";
            PreparedStatement psmt = conn.prepareStatement(query);
                    psmt.setString(1, id+"");
            psmt.setString(2, location);
            psmt.setString(3, name);
                    System.out.println(psmt1.toString());
            psmt.executeUpdate();
            System.out.println("-----------------------------------------------");
        }//for
        //conn.close();
    }
    catch (Exception e)
    {
        System.out.println(e.getMessage());
        e.printStackTrace();
    }
}

Following are the print outs:

2

    com.mysql.jdbc.JDBC4PreparedStatement@ffcd87:  UPDATE tb1  set tbl1.id= '391' WHERE tbl1.sid =(select tbl2.id from tbl2 where tbl2.location='src\\ps\\' and tbl2 .name='test.cxx')
    -----------------------------------------------
    **com.mysql.jdbc.JDBC4PreparedStatement@e7e7b:   UPDATE tb1  set tbl1.id= '391' WHERE tbl1.sid =(select tbl2.id from tbl2 where tbl2.location='\nsrc\\ps\\' and tbl2 .name='test.hxx')**

Notice that how the prepared statement added \n at the beginning of the location

Please use a prepared statement instead of inserting data into your SQL string - currently you're wide open for SQL injection attacks... — Jon Skeet
– Jon Skeet, Commented Oct 20, 2011 at 13:55
If you concatinate SQL query string (i.e. if you do not use query params instead) you can prevent DB (Oracle for example) to use its own statement caching mechanizm. It is potentially a performance leak. — svaor
– svaor, Commented Oct 20, 2011 at 14:06
I changed my code and using prepared statement but still having the same problem. — sap
– sap, Commented Oct 20, 2011 at 14:08

zlajo · Accepted Answer · 2011-10-20 14:12:49Z

1

Basically it is not a good idea to build sql statements by concatenation strings. You should consider using a PreparedStatement to query the database. When using a Prepared Statement you should create it outside the loop and only update the parameters.

PreparedStatement statement = con.prepareStatement("UPDATE table SET attr1 = ? WHERE attr2 = ?");
statement.setString(1, "something new");
statement.setString(2, someNumber);
statement.executeUpdate();

statement.setString(1, "something else");
statement.setString(2, anotherNumber);
statement.executeUpdate();

The usage of the PreparedStatement has two main advantages:

Typically the performance is better if the same query is executed very often
It is much more safe (type safety) and secure (no sql injections)

Further more (as mentioned in a different answer) PreparedStatement offers the possibility to execute jobs as batches. Basically this means that you collect all your operations and execute them all at once. This can also significantly improve the performance of your application.

edited Oct 20, 2011 at 14:12

answered Oct 20, 2011 at 13:58

zlajo

2,1931 gold badge19 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

sethu Over a year ago

Thats right.. one major problem in concatenating would be if you dont escape characters.. In this case it looks like a filename.. but what if location or name had a quote in it? like "sethu's". Different databases have different escape chars. Mostly a double single quote would work.. But PreparedStatement takes care of all of this for you..

Basanth Roy · Accepted Answer · 2011-10-20 14:13:30Z

1

You should use PreparedStatement. Otherwise, you need to close your statement and reopen in each loop.

If you are using PreparedStatement, you can move the query definition outside the loop. Inside the loop you can use a batch insert, i.e. stmt.addBatch(); And then finally outside the loop, you can do an stmt.executeBatch();

conn = DriverManager.getConnection (url,userName,password);


String query=" UPDATE tbl1 " +
         " set id= ? "+
         " where sid =(select sid from tbl2 " +
               "where tbl2.cl= ? and tbl2.cl2= ?)";
PreparedStatement stmt=conn.createStatement(query);


for(int i=0;i<lineArr.size();i++)
{
  String currentLine = arr.get(i);
  int lastSlash=currentLine.lastIndexOf("\\"); 
  String location=currentLine.substring(0, lastSlash+1);
      location=location.replace("\\", "\\\\");
  String name=currentLine.substring(lastSlash+1);         

  stmt.setString(1,id);
  stmt.setString(2, location);
  stmt.setString(3, name);

  stmt.addBatch();

}//for

int count=stmt.executeBatch();

edited Oct 20, 2011 at 14:13

answered Oct 20, 2011 at 13:56

Basanth Roy

6,5305 gold badges27 silver badges25 bronze badges

4 Comments

sap Over a year ago

I'm using prepared statement now but when I printed out the prepared statement, noticed that "\n" is automatically being added to one of the variables:UPDATE tb1 set tbl1.id= '391' WHERE tb1.sid =(select tbl2.sid from tbl2 where tbl2.;ocation='\nsrc\\core\\ps\\' and tbl2.name='test.hxx')

sap Over a year ago

there are no errors. It just does not execute the second time. If you look at the output I posted, you will notice that second time, the query adds "\n" before the location.

sap Over a year ago

the \n is being added by the preparedstatement. If I remove it manually and run it in phpmyadmin, then yes the data gets updated. I'm not understanding why the \n is being added the second time the query is being executed.

Basanth Roy Over a year ago

I doubt PreparedStatement is adding it. Most likely it came from String location=currentLine.substring(0, lastSlash+1); location=location.replace("\\", "\\\\");

jqa · Accepted Answer · 2011-10-20 13:58:44Z

0

My guess is your data has backslashes in it that you are attempting to remove. Perhaps your replace logic is not working with your data and some backslashes are making it into your SQL statement and breaking it. Use prepared statements instead.

Or do you have a unique index on id?

answered Oct 20, 2011 at 13:58

jqa

1,3706 silver badges17 bronze badges

2 Comments

jqa Over a year ago

@sarah - is it a unique index? are you trying to set the id column in more one row to the same value?

jqa Over a year ago

@sarah The value of id is coming in from the function call and doesn't change during the loop, right? So the first iteration of the loop will set one row to that value. The subsequent iterations will also try to set other rows to that same value but the unique index will cause the update to fail. Check your table and see if you can drop the uniqueness constraint.

user561938 · Accepted Answer · 2011-10-20 14:01:30Z

0

Shouldn't lineArr.size() be arr.size()? As you are doing arr.get(i) in the loop.

answered Oct 20, 2011 at 14:01

user561938

Collectives™ on Stack Overflow

sql update statement does not execute properly

4 Answers 4

1 Comment

4 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

1 Comment

4 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related