SQLite:批量更新一个名为 ‘boolean’ 的列

huangapple go评论75阅读模式
英文:

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

以下是否是存储启用状态(即01)的正确方法?

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&lt;Long&gt; idsEnabled = new ArrayList&lt;&gt;();
List&lt;Long&gt; idsDisabled = new ArrayList&lt;&gt;();

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(&quot;UPDATE types set enabled=1 where id in (&quot; 
    + TextUtils.join(&quot;,&quot;, idsEnabled) + &quot;)&quot;, null);
db.rawQuery(&quot;UPDATE types set enabled=0 where id in (&quot; 
    + TextUtils.join(&quot;,&quot;, idsDisabled) + &quot;)&quot;, 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() &gt; 0 || idsDisabled.size() &gt; 0) {
    String ids1 = TextUtils.join(&quot;,&quot;, idsEnabled);
    String ids0 = TextUtils.join(&quot;,&quot;, idsDisabled);
    String sql =
            &quot;UPDATE types &quot; +
            &quot;SET enabled = CASE &quot; +
            (idsEnabled.size() &gt; 0 ? &quot;WHEN id IN (&quot; + ids1 + &quot;) THEN 1 &quot; : &quot;&quot;) +
            (idsDisabled.size() &gt; 0 ? &quot;WHEN id IN (&quot; + ids0 + &quot;) THEN 0 &quot; : &quot;&quot;) +
            &quot;ELSE enabled &quot; +
            &quot;END&quot;;
    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() &gt; 0 || idsDisabled.size() &gt; 0) {
    String ids1 = TextUtils.join(&quot;,&quot;, idsEnabled);
    String ids0 = TextUtils.join(&quot;,&quot;, idsDisabled);

    String idsAll = &quot;&quot;;
    if (idsEnabled.size() == 0) idsAll = ids0;
    else if (idsDisabled.size() == 0) idsAll = ids1;
    else idsAll = ids1 + &quot;,&quot; + ids0;

    String sql =
            &quot;UPDATE types &quot; +
            &quot;SET enabled = CASE &quot; +
            (idsEnabled.size() &gt; 0 ? &quot;WHEN id IN (&quot; + ids1 + &quot;) THEN 1 &quot; : &quot;&quot;) +
            (idsDisabled.size() &gt; 0 ? &quot;WHEN id IN (&quot; + ids0 + &quot;) THEN 0 &quot; : &quot; &quot;) +
            &quot;END &quot; +
            &quot;WHERE id IN (&quot; + idsAll + &quot;)&quot;;
    db.execSQL(sql);
}

huangapple
  • 本文由 发表于 2020年8月9日 23:05:08
  • 转载请务必保留本文链接:https://go.coder-hub.com/63327874.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定