Sqlite UPDATE with parameterised IN (…) clause [du

问题:

This question already has an answer here:

• IN clause and placeholders 10 answers

In my app I'm working with sqlite database - and I hit a strange issue.

I have a table that looks like this:

_id   field1   field2   status
---   ------   ------   ------
 1    value    value    ...
 2    value    value    ...
...

At one place, I need to update some rows to set them to another status, so I'm trying

SQLiteDatabase db = getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put("status", STATUS_SENT);
Set<Integer> ids = datasource.getCompletedIds();
String idparam = TextUtils.join(",", ids);
int cnt = db.update(TABLE_ORDERS, cv, "_id in (?)", new String[] { idparam });
Log.d("db", "updated " + cnt + " rows");
db.close();

However nothing gets updated - and db.update returns 0. What am I missing?

回答1:

I'm not happy recommending not using parameters, but it this case, it's actually easier than formatting all those ? markers (and with plain integers, just as safe):

db.update(TABLE_ORDERS, cv, "_id IN (" + idparam + ")", null);

回答2:

Unfortunately you must list one insertion character ? for each id in your Set...
A popular fix is to write a quick utility to add the appropriate number of ?s based on the Set's size.