Oracle11g sql can update statement judge some colum is ""

Question

I hava two values picId and tubId and I want to update table A. But first of all, I must check if they are empty i.e "" . I use an update statement twice. I'm wondering if I can use update statement only once to complete this task.

String picId = "";
String tubId = "1";

if(!"".equals(picId)) {
    String update = "update A set columnName=someValue where id=" + picId;
    //this method for update 
    this.executeUpdate(update, new Object[]{});
}
if(!"".equals(tubId)) {
    String update = "update A set columnName=someValue where id=" + tubId;
    this.executeUpdate(update, new Object[]{});
}

Thank you very much!

This code is vulnerable to SQL injection. [Use PreparedStatements instead](https://stackoverflow.com/questions/1812891/java-escape-string-to-prevent-sql-injection). — Michael, Aug 10 '17 at 10:10
Thank you all.WIth your help I have known how to deal with it : — Jon Snow, Aug 10 '17 at 12:42

score 0 · Accepted Answer · answered Aug 10 '17 at 10:07

0

Dear Jon Snow try to use in like this:

  String update = "update A set columnName=someValue where id in (" + tubId +","+picId+")";

answered Aug 10 '17 at 10:07

Abdelhak

8,299
4
22
36

score 0 · Answer 2 · answered Aug 10 '17 at 10:09

0

Here you can use another variable and give condition base on it.

String picId="";
String tubId="1";
String sample="";
if(!(picID.equals("")))
{
sample=picId;
}
else if(!(tubId.equals(""))) 
{
sample=tubId;
}
 String update = "update A set columnName=someValue where id=" + sample;

         this.executeUpdate(update, new Object[]{});

answered Aug 10 '17 at 10:09

ashwanth s

105
1
1
10

But if none of them is "" or both of them are ""? – Jon Snow Aug 10 '17 at 11:38

score 0 · Answer 3 · answered Aug 10 '17 at 10:27

0

I hope you know what using concatenation in SQL queries is the way to SQL-Injection.

But about your question, you can use IN condition for WHERE clause. Just prepare the data:

List<String> listId = new ArrayList<>();
if (!"".equals(picId)) {
    listId.add(picId);
}
if(!"".equals(tubId)) {
     listId.add(tubId);
}
String ids = listId.stream().map(_s -> String.format("'%s'", _s)).collect(Collectors.joining(",", "(", ")"));
String update = "update A set columnName=someValue where id IN " + ids;
this.executeUpdate(update, new Object[]{});

answered Aug 10 '17 at 10:27

Syroezhkin

188
11

I have improved this statement in order to avoid SQL-Injection.And With your help,I know how to use IN keyword in SQL – Jon Snow Aug 11 '17 at 01:32
I'm glad, that it was useful. I would use prepared statement to execute queries. Nobody knows how SQL-Injection can be executed. – Syroezhkin Aug 11 '17 at 08:14

Oracle11g sql can update statement judge some colum is ""

3 Answers3