CODEWITHSUNDEEP
javasql
sap
sap

Reputation: 1141

sql update statement does not execute properly

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

Upvotes: 0

Views: 2046

Answers (4)

Basanth Roy
Basanth Roy

Reputation: 6500

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();

Upvotes: 1

zlajo
zlajo

Reputation: 2183

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.

Upvotes: 1

user561938
user561938

Reputation:

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

Upvotes: 0

jqa
jqa

Reputation: 1370

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?

Upvotes: 0

Related Questions