英文:
SQLite: bulk update a 'boolean' column
问题
在SQLite表中更新布尔列(即分类二进制INTEGER
值)的标准方法是什么?
给定一个表格:
CREATE TABLE types (
id INTEGER NOT NULL PRIMARY KEY,
name TEXT,
enabled INTEGER DEFAULT 1)
以及一个可以具有启用/禁用状态的对象列表,它们分别表示为两个列表:
List<Long> idsEnabled = new ArrayList<>();
List<Long> idsDisabled = new ArrayList<>();
以下是否是存储启用状态(即0
或1
)的正确方法?
SQLiteDatabase db = dbHelper.getWritableDatabase();
// 更新:不起作用,因为rawQuery不能用于修改数据,必须使用execSQL
db.rawQuery("UPDATE types set enabled=1 where id in ("
+ TextUtils.join(",", idsEnabled) + ")", null);
db.rawQuery("UPDATE types set enabled=0 where id in ("
+ TextUtils.join(",", idsDisabled) + ")", null);
是否有更好、更有效、更适用的方法来执行此操作?我的假设是逐行更新每行将是最坏的情况。这个问题不一定与Android有关,尽管如果有某种特定于Android的API可以执行此操作,将会很有帮助。
英文:
What is the standard approach to update a boolean column (i.e. a categorical binary INTEGER
value) in a SQL (SQLite) table?
<sup>(here binary is intended to mean an INTEGER that takes two values only: 0
and 1
, hence the quotes in the question title)</sup>
Given a table:
<!-- language: lang-sql -->
CREATE TABLE types (
id INTEGER NOT NULL PRIMARY KEY,
name TEXT,
enabled INTEGER DEFAULT 1)
And a list of objects which can have an enabled/disabled state and which are represented as two lists:
<!-- language: lang-java -->
List<Long> idsEnabled = new ArrayList<>();
List<Long> idsDisabled = new ArrayList<>();
Would this be the correct way to store the enabled state (i.e. 0
or 1
)?
<!-- language: lang-java -->
SQLiteDatabase db = dbHelper.getWritableDatabase();
// upd: NOT WORKING because rawQuery does NOT work for modifying data,
// must use execSQL
db.rawQuery("UPDATE types set enabled=1 where id in ("
+ TextUtils.join(",", idsEnabled) + ")", null);
db.rawQuery("UPDATE types set enabled=0 where id in ("
+ TextUtils.join(",", idsDisabled) + ")", null);
Is there a better, more efficient, OOTB way to do this? My assumption is that updating each row one by one would be the worst case.
The question is not necessarily android-specific, although if there is some android-specific API for doing this it would be helpful.
答案1
得分: 1
方法rawQuery()
不是更新表的方法。它用于以Cursor
的形式返回行。在这种情况下,您必须使用execSQL()
:
if (idsEnabled.size() > 0 || idsDisabled.size() > 0) {
String ids1 = TextUtils.join(",", idsEnabled);
String ids0 = TextUtils.join(",", idsDisabled);
String sql =
"UPDATE types " +
"SET enabled = CASE " +
(idsEnabled.size() > 0 ? "WHEN id IN (" + ids1 + ") THEN 1 " : "") +
(idsDisabled.size() > 0 ? "WHEN id IN (" + ids0 + ") THEN 0 " : "") +
"ELSE enabled " +
"END";
db.execSQL(sql);
}
上述语句使用CASE
表达式来确定将更新列的当前值的值。
如果您只想更新表的一小部分,那么在UPDATE
语句中包括一个WHERE
子句将更加高效,这样您可以省略CASE
表达式的ELSE
部分:
if (idsEnabled.size() > 0 || idsDisabled.size() > 0) {
String ids1 = TextUtils.join(",", idsEnabled);
String ids0 = TextUtils.join(",", idsDisabled);
String idsAll = "";
if (idsEnabled.size() == 0) idsAll = ids0;
else if (idsDisabled.size() == 0) idsAll = ids1;
else idsAll = ids1 + "," + ids0;
String sql =
"UPDATE types " +
"SET enabled = CASE " +
(idsEnabled.size() > 0 ? "WHEN id IN (" + ids1 + ") THEN 1 " : "") +
(idsDisabled.size() > 0 ? "WHEN id IN (" + ids0 + ") THEN 0 " : " ") +
"END " +
"WHERE id IN (" + idsAll + ")";
db.execSQL(sql);
}
英文:
The method rawQuery()
is not the way to update the table.<br/>
It is used to return rows in the form of a Cursor
.<br/>
In this case you must use execSQL()
:
if (idsEnabled.size() > 0 || idsDisabled.size() > 0) {
String ids1 = TextUtils.join(",", idsEnabled);
String ids0 = TextUtils.join(",", idsDisabled);
String sql =
"UPDATE types " +
"SET enabled = CASE " +
(idsEnabled.size() > 0 ? "WHEN id IN (" + ids1 + ") THEN 1 " : "") +
(idsDisabled.size() > 0 ? "WHEN id IN (" + ids0 + ") THEN 0 " : "") +
"ELSE enabled " +
"END";
db.execSQL(sql);
}
The above statement uses a CASE
expression to determine the value that will update the current value of the column.<br/>
If you want to update just a small part of the table then it would be more efficient to include a WHERE
clause in the UPDATE
statement, so you can omit the ELSE
part of the CASE
expression:
if (idsEnabled.size() > 0 || idsDisabled.size() > 0) {
String ids1 = TextUtils.join(",", idsEnabled);
String ids0 = TextUtils.join(",", idsDisabled);
String idsAll = "";
if (idsEnabled.size() == 0) idsAll = ids0;
else if (idsDisabled.size() == 0) idsAll = ids1;
else idsAll = ids1 + "," + ids0;
String sql =
"UPDATE types " +
"SET enabled = CASE " +
(idsEnabled.size() > 0 ? "WHEN id IN (" + ids1 + ") THEN 1 " : "") +
(idsDisabled.size() > 0 ? "WHEN id IN (" + ids0 + ") THEN 0 " : " ") +
"END " +
"WHERE id IN (" + idsAll + ")";
db.execSQL(sql);
}
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论