How to update table with multiple ids at once

Question

I have an array of ids as a String[], and I need to update a table that corresponds to these ids. I implemented a solution using a for loop, but I am not entirely satisfied with it. Are there any alternative approaches to solve this problem ?

public int settle_updt_reserve(CommonDAO commonDAO, CommonMap value) throws Exception {
    StringBuffer sbQuery = new StringBuffer();

    sbQuery.append("UPDATE t_reserve a"                 + this.NEWLINE);
    sbQuery.append("SET    a.settled = [settled]" + this.NEWLINE);
    sbQuery.append(",  a.settlement_no = [calc_no]" + this.NEWLINE); //settlement number
    sbQuery.append("WHERE  1=1"                         + this.NEWLINE);
    sbQuery.append("AND    a.idkey = [idkey]"  + this.NEWLINE);
    sbQuery.append("AND    a.rs_seq IN ("  + this.NEWLINE);

    String[] arr_rs_seq = value.getString("rs_seq_array", "").split(",");

    for (int i = 0; i < arr_rs_seq.length; i++) {
        sbQuery.append("'" + arr_rs_seq[i] + "'");
        if (i + 1 != arr_rs_seq.length) {
            sbQuery.append(",");
        }
    }
    sbQuery.append(")"  + this.NEWLINE);
    Query qry = new Query(sbQuery);

    try{
        qry.setValue("idkey",           value.getString("idkey", ""));
        qry.setValue("settled",     value.getString("settled", ""));
        qry.setValue("calc_no", value.getString("calc_no", ""));

        return commonDAO.update(qry);
    }catch(Exception e){
        System.out.println("ReportDAO : settle_updt_reserve(CommonDAO, CommonMap) : " + CommonUtil.getSysDate() + "/Error MSG:" + e.getMessage());
        throw e;
    }
}

I need alternative/ better approach for this solution

    String[] arr_rs_seq = value.getString("rs_seq_array", "").split(",");

    for (int i = 0; i < arr_rs_seq.length; i++) {
        sbQuery.append("'" + arr_rs_seq[i] + "'");
        if (i + 1 != arr_rs_seq.length) {
            sbQuery.append(",");
        }
    }
 

Answer 1

Are you using any kind of ORM like Hibernate? Does that 'Query' class belong to some ORM framework?

Usually, the ORM itself will provide a way to replace a single parameter with a list of values. E.g. (Using EBean ORM as an example)

SqlUpdate upd = server.createSqlUpdate("UPDATE t SET col = :val WHERE id IN (:ids));
upd.setParameter("val", val);
upd.setParameter("ids", listOfIds);
upd.execute();

Here, listOfIds is a list with all the values. And the framework will internally create a PreparedStatement with all the necessary parameter value injections, and then execute.

If anything like that is NOT available, I.e. you are not using any ORM or your ORM is not supporting it (highly unlikely), then

    String[] arr_rs_seq = value.getString("rs_seq_array", "").split(",");

    for (int i = 0; i < arr_rs_seq.length; i++) {
        sbQuery.append("'" + arr_rs_seq[i] + "'");
        if (i + 1 != arr_rs_seq.length) {
            sbQuery.append(",");
        }
    }

Can be changed to something like this...

    String[] arr_rs_seq = value.getString("rs_seq_array", "").split(",");

    for (int i = 0; i < arr_rs_seq.length; i++) {
        if (i > 0) {
            sbQuery.append(",");
        }
        sbQuery.append("[key" + i + "]");
    }

This will generate a query with placeholders [key0], [key1], ..., just like your existing placeholders [settled], [calc_no], and [idkey].

And then, after

qry.setValue("calc_no", value.getString("calc_no", ""));

you can add

    for (int i = 0; i < arr_rs_seq.length; i++) {
        qry.setValue("key"+i, arr_rs_seq[i]);
    }

This should be a better solution to eliminate the SQL Injection. Appending '<VALUE>' in the query directly may lead to SQL injection.

So, you should use the parameterized query the same way you are injecting values of parameters idkey, settled, and calc_no.