How to update multiple row with SQLite "IN" operator?

Question

In my android application, I save some events that I must sent to my server. All events that are sent, I must mark as "sent". The server, after I sent all events not marked (sent = 0), reply with an array of all ids that it has saved. Then, in android application, I execute an update query with all ids, but it don't work.

EventDao.java

@Dao
public interface EventDao {
    @Query("UPDATE Event SET sent = 1 WHERE id IN (:ids)")
    void updateSent(String ids)
}

MarkEvent.java

...
    DatabaseClient.getInstance(context).getDb().eventDao().updateSent(response.join(","));
    Log.d("MarkEvent", response.join(","));
...

"response" is a JSONArray with only integer. When the Server reply, I can read this in my Logcat:

D/MarkEvent: 2,3,4,5,6,7,8

this number are the correct ids of record

Does this https://stackoverflow.com/questions/9203261/android-sqlite-in-clause-using-values-from-array help? — Raghunandan, Aug 24 '19 at 16:23
Unfortunately no: my String "ids" it's correctly formatted and the result should be the same. Thank you. — mr.krash, Aug 24 '19 at 17:18

score 1 · Accepted Answer · answered Aug 24 '19 at 17:11

1

You have to replace String (ids) with List. So Your method will look like:

void updateSent(List<Integer> ids)

Someone already got similar problem: https://stackoverflow.com/a/53632880/5612090

answered Aug 24 '19 at 17:11

antygravity

1,307
10
12

Thank You, you have led me in the right direction!! I adopted a similar solution that I publish here as a further answer – mr.krash Aug 24 '19 at 17:22

score 0 · Answer 2 · edited Jun 20 '20 at 09:12

I read the "@Query" notation documentation better: Room Query Annotation From documentation:

Room supports binding a list of parameters to the query. At runtime, Room will build the correct query to have matching number of bind arguments depending on the number of items in the method parameter.

So I have changed my Dao like that

@Dao
public interface EventDao {
    @Query("UPDATE Event SET sent = 1 WHERE id IN (:ids)")
    void updateSent(int[] ids)
}

and in my class MarkEvent.java I have added a static function (from this How to cast a JSONArray to int Array) and I have modified the call:

public static int[] JSonArray2IntArray(JSONArray jsonArray){
    int[] intArray = new int[jsonArray.length()];
    for (int i = 0; i < intArray.length; ++i) {
        intArray[i] = jsonArray.optInt(i);
    }
    return intArray;
}

...

DatabaseClient.getInstance(context).getDb().eventDao().updateSent(JSonArray2IntArray(response));

...

Now Work! Thank You.

How to update multiple row with SQLite "IN" operator?

2 Answers2