使用ID删除多行? [英] Delete multiple rows using IDs?

问题描述

如何通过Android SQLite数据库中的ID列表删除多行?

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();
    }
}

现在我正尝试使用ID列表来调用它:

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);
}

例如，如果ID列表包含值[1、2、42]，那么我认为生成的SQL应该是:

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);

但这似乎无法正常工作.如果该列表仅包含1个ID，则将其正确删除.但是，如果我提供多个值，则零行会受到影响.我在做什么错了?

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?

推荐答案

当您将单个字符串指定为 whereArgs 时，单个字符串最终会出现在SQL命令中，就像您编写此代码一样:

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')

这会将每个 _id 值与'1,2,42'值进行比较，这当然是行不通的.

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

如果使用三个参数标记并在 whereArgs 数组中提供三个字符串，则最终将得到三个字符串，如下所示:

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')

仅当 _id 列具有整数 affinity 时，此方法才有效a>，对于声明为INTEGER PRIMARY KEY的列，这是正确的，但在一般情况下不是这样.

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.

Android数据库API不允许查询参数具有字符串以外的任何类型.使用整数时，您应该直接插入它们(如 ianhanniballake的答案):

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) + ")";

这篇关于使用ID删除多行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！