Delete multiple rows using IDs?

Question

How can I delete multiple rows by a list of IDs in Android SQLite database?

I have defined a general delete method in this manner:

protected void deleteWhere(String whereClause, String[] whereArgs) {
    try {
        databaseHelper.getWritableDatabase().delete(
                getTableName(), whereClause, whereArgs);
    } finally {
        databaseHelper.close();
    }
}

And now I'm trying to call it with a list of IDs:

public void deleteAll(Iterable<T> entities) {
    Iterable<Long> ids = Iterables.transform(entities, getId);

    String whereClause = getIdColumn() + " IN (?)";
    String[] whereArgs = { TextUtils.join(",", ids) };
    deleteWhere(whereClause, whereArgs);
}

If the ID list contains for example the values [1, 2, 42], then I assume the resulting SQL should be:

DELETE FROM tableName WHERE _id IN (1,2,42);

But this doesn't seem to work correctly. If the list contains only 1 ID, then it is correctly deleted. However, if I provide multiple values, than zero rows are affected. What am I doing wrong?

Answer 1

When you give a single string as whereArgs, a single string ends up in the SQL command, as if you had written this:

... WHERE _id IN ('1,2,42')

This would compare each _id value against the value '1,2,42', which of course does not work.

If you use three parameter markers and give three strings in the whereArgs array, you would end up with three strings, like this:

... WHERE _id in ('1','2','42')

This works only when the _id column has integer affinity, which is true for a column declared as INTEGER PRIMARY KEY, but not in the general case.

The Android database API does not allow query parameters to have any type but string. When using integers, you should just insert them directly (as in ianhanniballake's answer):

String whereClause = getIdColumn() + " IN (" + TextUtils.join(",", ids) + ")";