CODEWITHSUNDEEP
javamysql
hellzone
hellzone

Reputation: 5246

How to create an update statement with multiple set clauses?

I want to create an update statement with multiple set clauses. Problem is some of these parameters can be null(not all of them). I need to put some comma before these statements but I don't know how to handle this efficiently. Is there any way to do this?

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Here is my function;

public static void update(value1, value2, value3){
    String sql = "UPDATE table SET ";

    //I need to check if these values are not null then I need to add them to sql string.

    sql += " WHERE condition";
}

My solution;

int count = 0;
if(value1 != null){
    if(count == 0) {
        sql += "column1 = value1";
    } else {
        sql += ", column1 = value1";
    }
    count++;
}

if(value2 != null){
    if(count == 0) {
        sql += "column2 = value2";
    } else {
        sql += ", column2 = value2";
    }
    count++;
}

if(value3 != null){
    if(count == 0) {
        sql += "column3 = value3";
    } else {
        sql += ", column3 = value3";
    }
    count++;
}

Upvotes: 0

Views: 347

Answers (2)

AxelH
AxelH

Reputation: 14572

First, create a quick Bean to store the key an the value

class Token{
    private String key, value;

    public Token(String key, String value) {
        this.key = key;
        this.value = value;
    }
}

Then use a Stream to :

  • filter the null value
  • concatenate the key/value (this could be done in a method of Token
  • join each value with a comma

Quick test :

List<Token> list = new ArrayList<>();
list.add(new Token("A", "Foo"));
list.add(new Token("B", null));
list.add(new Token("C", "Bar"));

String query = 
    String.join(",", 
        list.stream()
           .filter(t -> t.value != null)
           .map(t -> t.key + "=" +t.value)
           .toArray(String[]::new)
    );
System.out.print(query);

A=Foo,C=Bar

Other solution to use with a PreparedStatement:

//remove every null values
list.removeIf(t -> t.value == null);

//Build the query with parameters ?
query = 
        String.join(",", 
            list.stream()
               .filter(t -> t.value != null)
               .map(t -> t.key + "=?")
               .toArray(String[]::new)
        );
System.out.println(query);

A=?,C=?

And the list have the corresponding parameters.

Upvotes: 1

QuakeCore
QuakeCore

Reputation: 1926

Try utilizing a map, where the key would be the column name and the value is the column value, something like this:

public static String addSetValue(Map<String, Object> fieldMap) {
    StringBuilder  setQueryPart = new StringBuilder();
    for (Map.Entry<String, ? extends Object> entry: fieldMap.entrySet()) {
        if (entry.getValue() != null){
            setQueryPart.append(entry.getKey() + " = " +entry.getValue() + ", ");
        }
    }
    //Remove last comma, since we don't need it.
    setQueryPart.deleteCharAt(setQueryPart.lastIndexOf(","));
    return setQueryPart.toString();
}

Then you simply pass a map that you previously filled, and append the return string to your query string.

Upvotes: 1

Related Questions