Android/SQLite：插入更新表列以保留标识符

Android/SQLite: Insert-Update table columns to keep the identifier

提问by JJD

Currently, I am using the following statement to create a table in an SQLite database on an Android device.

目前，我正在使用以下语句在 Android 设备上的 SQLite 数据库中创建表。

CREATE TABLE IF NOT EXISTS 'locations' (
  '_id' INTEGER PRIMARY KEY AUTOINCREMENT, 'name' TEXT, 
  'latitude' REAL, 'longitude' REAL, 
  UNIQUE ( 'latitude',  'longitude' ) 
ON CONFLICT REPLACE );

The conflict-clause at the end causes that rows are droppedwhen new inserts are done that come with the same coordinates. The SQLite documentationcontains further information about the conflict-clause.

最后的冲突子句导致在完成具有相同坐标的新插入时删除行。在SQLite的文档包含有关冲突条款的进一步信息。

Instead, I would like to keepthe former rows and just updatetheir columns. What is the most efficient way to do this in a Android/SQLite environment?

相反，我想保留以前的行并只更新它们的列。在 Android/SQLite 环境中执行此操作的最有效方法是什么？

• As a conflict-clause in the CREATE TABLEstatement.
• As an INSERTtrigger.
• As a conditional clause in the ContentProvider#insertmethod.
• ... any better you can think off

• 作为CREATE TABLE声明中的冲突条款。
• 作为INSERT触发器。
• 作为方法中的条件子句ContentProvider#insert。
• ......你能想到的更好的

I would think it is more performant to handle such conflicts within the database. Also, I find it hard to rewrite the ContentProvider#insertmethod to consider the insert-updatescenario. Here is code of the insertmethod:

我认为在数据库中处理此类冲突会更高效。此外，我发现很难重写该ContentProvider#insert方法来考虑插入-更新场景。下面是该insert方法的代码：

public Uri insert(Uri uri, ContentValues values) {
    final SQLiteDatabase db = mOpenHelper.getWritableDatabase();
    long id = db.insert(DatabaseProperties.TABLE_NAME, null, values);
    return ContentUris.withAppendedId(uri, id);
}

When data arrives from the backend all I do is inserting the data as follows.

当数据从后端到达时，我所做的就是按如下方式插入数据。

getContentResolver.insert(CustomContract.Locations.CONTENT_URI, contentValues);

I have problems figuring out how to apply an alternative call to ContentProvider#updatehere. Additionally, this is not my favored solution anyways.

我在弄清楚如何将替代调用应用于ContentProvider#update此处时遇到问题。此外，这无论如何都不是我喜欢的解决方案。

---

---

Edit:

编辑：

@CommonsWare: I tried to implement your suggestion to use INSERT OR REPLACE. I came up with this ugly piece of code.

@CommonsWare：我尝试实施您的建议以使用INSERT OR REPLACE. 我想出了这段丑陋的代码。

private static long insertOrReplace(SQLiteDatabase db, ContentValues values, String tableName) {
    final String COMMA_SPACE = ", ";
    StringBuilder columnsBuilder = new StringBuilder();
    StringBuilder placeholdersBuilder = new StringBuilder();
    List<Object> pureValues = new ArrayList<Object>(values.size());
    Iterator<Entry<String, Object>> iterator = values.valueSet().iterator();
    while (iterator.hasNext()) {
        Entry<String, Object> pair = iterator.next();
        String column = pair.getKey();
        columnsBuilder.append(column).append(COMMA_SPACE);
        placeholdersBuilder.append("?").append(COMMA_SPACE);
        Object value = pair.getValue();
        pureValues.add(value);
    }
    final String columns = columnsBuilder.substring(0, columnsBuilder.length() - COMMA_SPACE.length());
    final String placeholders = placeholderBuilder.substring(0, placeholdersBuilder.length() - COMMA_SPACE.length());
    db.execSQL("INSERT OR REPLACE INTO " + tableName + "(" + columns + ") VALUES (" + placeholders + ")", pureValues.toArray());

    // The last insert id retrieved here is not safe. Some other inserts can happen inbetween.
    Cursor cursor = db.rawQuery("SELECT * from SQLITE_SEQUENCE;", null);
    long lastId = INVALID_LAST_ID;
    if (cursor != null && cursor.getCount() > 0 && cursor.moveToFirst()) {
        lastId = cursor.getLong(cursor.getColumnIndex("seq"));
    }
    cursor.close();
    return lastId;
}

When I check the SQLite database, however, equal columns are still removed and inserted with new ids. I do not understand why this happens and thought the reason is my conflict-clause. But the documentationstates the opposite.

但是，当我检查 SQLite 数据库时，仍会删除相等的列并插入新的 ids。我不明白为什么会发生这种情况，并认为原因是我的冲突条款。但是文档说明了相反的情况。

The algorithm specified in the OR clause of an INSERT or UPDATE overridesany algorithm specified in a CREATE TABLE. If no algorithm is specified anywhere, the ABORT algorithm is used.

INSERT 或 UPDATE 的 OR 子句中指定的算法 会覆盖CREATE TABLE 中指定的任何算法。如果没有在任何地方指定算法，则使用 ABORT 算法。

Another disadvantage of this attempt is that you loose the value of the id which is return by an insert statement. To compensate this, I finally found an option to ask for the last_insert_rowid. It is as explained in the posts of dtmilano and swiz. I am, however, not sure if this is safe since another insert can happen inbetween.

这种尝试的另一个缺点是您丢失了插入语句返回的 id 值。为了弥补这一点，我终于找到了一个选项来要求last_insert_rowid. 正如dtmilano 和 swiz 的帖子所解释的那样。但是，我不确定这是否安全，因为中间可能会发生另一个插入。

采纳答案by Devunwired

I can understand the perceived notion that it is best for performance to do all this logic in SQL, but perhaps the simplest (least code) solution is the best one in this case? Why not attempt the update first, and then use insertWithOnConflict()with CONFLICT_IGNOREto do the insert (if necessary) and get the row id you need:

我可以理解在 SQL 中执行所有这些逻辑对性能最好的感知概念，但在这种情况下，最简单（最少代码）的解决方案可能是最好的解决方案？为什么不先尝试更新，然后使用insertWithOnConflict()withCONFLICT_IGNORE进行插入（如有必要）并获取您需要的行 ID：

public Uri insert(Uri uri, ContentValues values) {
    final SQLiteDatabase db = mOpenHelper.getWritableDatabase();
    String selection = "latitude=? AND longitude=?"; 
    String[] selectionArgs = new String[] {values.getAsString("latitude"),
                values.getAsString("longitude")};

    //Do an update if the constraints match
    db.update(DatabaseProperties.TABLE_NAME, values, selection, null);

    //This will return the id of the newly inserted row if no conflict
    //It will also return the offending row without modifying it if in conflict
    long id = db.insertWithOnConflict(DatabaseProperties.TABLE_NAME, null, values, CONFLICT_IGNORE);        

    return ContentUris.withAppendedId(uri, id);
}

A simpler solution would be to check the return value of update()and only do the insert if the affected count was zero, but then there would be a case where you could not obtain the id of the existing row without an additional select. This form of insert will always return to you the correct id to pass back in the Uri, and won't modify the database more than necessary.

一个更简单的解决方案是检查 的返回值，update()并且仅在受影响的计数为零时才执行插入，但是在这种情况下，如果没有额外的选择，您将无法获得现有行的 id。这种插入形式将始终向您返回正确的 id 以在 中传回Uri，并且不会对数据库进行不必要的修改。

If you want to do a large number of these at once, you might look at the bulkInsert()method on your provider, where you can run multiple inserts inside a single transaction. In this case, since you don't need to return the idof the updated record, the "simpler" solution should work just fine:

如果您想一次执行大量这些操作，您可以查看bulkInsert()提供程序上的方法，您可以在其中在单个事务中运行多个插入。在这种情况下，由于您不需要返回id更新记录的 ，“更简单”的解决方案应该可以正常工作：

public int bulkInsert(Uri uri, ContentValues[] values) {
    final SQLiteDatabase db = mOpenHelper.getWritableDatabase();
    String selection = "latitude=? AND longitude=?";
    String[] selectionArgs = null;

    int rowsAdded = 0;
    long rowId;
    db.beginTransaction();
    try {
        for (ContentValues cv : values) {
            selectionArgs = new String[] {cv.getAsString("latitude"),
                cv.getAsString("longitude")};

            int affected = db.update(DatabaseProperties.TABLE_NAME, 
                cv, selection, selectionArgs);
            if (affected == 0) {
                rowId = db.insert(DatabaseProperties.TABLE_NAME, null, cv);
                if (rowId > 0) rowsAdded++;
            }
        }
        db.setTransactionSuccessful();
    } catch (SQLException ex) {
        Log.w(TAG, ex);
    } finally {
        db.endTransaction();
    }

    return rowsAdded;
}

In truth, the transaction code is what makes things faster by minimizing the number of times the database memory is written to the file, bulkInsert()just allows multiple ContentValuesto be passed in with a single call to the provider.

事实上，事务代码是通过最小化数据库内存写入文件的次数来使事情更快的原因，bulkInsert()只允许ContentValues通过对提供程序的单个调用传入多个。

回答by blazeroni

One solution is to create a view for the locationstable with a INSTEAD OF trigger on the view, then insert into the view. Here's what that would look like:

一种解决方案是使用视图locations上的 INSTEAD OF 触发器为表创建视图，然后插入到视图中。这就是它的样子：

View:

看法：

CREATE VIEW locations_view AS SELECT * FROM locations;

Trigger:

扳机：

CREATE TRIGGER update_location INSTEAD OF INSERT ON locations_view FOR EACH ROW 
  BEGIN 
    INSERT OR REPLACE INTO locations (_id, name, latitude, longitude) VALUES ( 
       COALESCE(NEW._id, 
         (SELECT _id FROM locations WHERE latitude = NEW.latitude AND longitude = NEW.longitude)),
       NEW.name, 
       NEW.latitude, 
       NEW.longitude
    );
  END;

Instead of inserting into the locationstable, you insert into the locations_viewview. The trigger will take care of providing the correct _idvalue by using the sub-select. If, for some reason, the insert already contains an _idthe COALESCEwill keep it and override an existing one in the table.

不是插入到locations表中，而是插入到locations_view视图中。触发器将_id通过使用子选择来提供正确的值。如果由于某种原因，插件已经包含_id了COALESCE将保持并覆盖在表中的现有一个。

You'll probably want to check how much the sub-select affects performance and compare that to other possible changes you could make, but it does allow you keep this logic out of your code.

您可能想要检查子选择对性能的影响程度，并将其与您可以进行的其他可能更改进行比较，但它确实允许您将此逻辑排除在代码之外。

I tried some other solutions involving triggers on the table itself based on INSERT OR IGNORE, but it seems that BEFORE and AFTER triggers only trigger if it will actually insert into the table.

我尝试了一些其他解决方案，涉及基于 INSERT OR IGNORE 的表本身的触发器，但似乎 BEFORE 和 AFTER 触发器仅在实际插入表时才触发。

You might find this answerhelpful, which is the basis for the trigger.

您可能会发现此答案很有帮助，这是触发器的基础。

Edit: Due to BEFORE and AFTER triggers not firing when an insert is ignored (which could then have been updated instead), we need to rewrite the insert with an INSTEAD OF trigger. Unfortunately, those don't work with tables - we have to create a view to use it.

编辑：由于在插入被忽略时 BEFORE 和 AFTER 触发器不会触发（然后可以被更新），我们需要用 INSTEAD OF 触发器重写插入。不幸的是，这些不适用于表 - 我们必须创建一个视图才能使用它。

回答by biegleux

INSERT OR REPLACEworks just like ON CONFLICT REPLACE. It will delete the row if the row with the unique column already exists and than it does an insert. It never does update.

INSERT OR REPLACE就像ON CONFLICT REPLACE. 如果具有唯一列的行已经存在，它将删除该行，然后插入。它永远不会更新。

I would recommend you stick with your current solution, you create table with ON CONFLICTclausule, but every time you insert a row and the constraint violation occurs, your new row will have new _idas origin row will be deleted.

我建议您坚持使用当前的解决方案，使用ON CONFLICTclausule创建表，但是每次插入一行并且发生约束违规时，您的新行都会有新的，_id因为原始行将被删除。

Or you can create table without ON CONFLICTclausule and use INSERT OR REPLACE, you can use insertWithOnConflict()method for that, but it is available since API level 8, requires more coding and leads to the same solution as table with ON CONFLICTclausule.

或者您可以创建没有ON CONFLICTclausule 的表并使用INSERT OR REPLACE，您可以为此使用insertWithOnConflict()方法，但它自 API 级别 8 起可用，需要更多的编码并导致与带有ON CONFLICTclausule 的表相同的解决方案。

If you still want to keep your origin row, it means you want to keep the same _idyou will have to make two queries, first one for inserting a row, second to update a row if insertion failed (or vice versa). To preserve consistency, you have to execute queries in a transaction.

如果您仍然想保留原始行，则意味着您要保持不变，_id您将必须进行两次查询，第一个用于插入行，第二个用于在插入失败时更新行（反之亦然）。为了保持一致性，您必须在事务中执行查询。

    db.beginTransaction();
    try {
        long rowId = db.insert(table, null, values);
        if (rowId == -1) {
            // insertion failed
            String whereClause = "latitude=? AND longitude=?"; 
            String[] whereArgs = new String[] {values.getAsString("latitude"),
                    values.getAsString("longitude")};
            db.update(table, values, whereClause, whereArgs);
            // now you have to get rowId so you can return correct Uri from insert()
            // method of your content provider, so another db.query() is required
        }
        db.setTransactionSuccessful();
    } finally {
        db.endTransaction();
    }

回答by Muzikant

Use insertWithOnConflictand set the last parameter (conflictAlgorithm) to CONFLICT_REPLACE.

使用insertWithOnConflict并将最后一个参数 ( conflictAlgorithm) 设置为CONFLICT_REPLACE。

Read more at the following links:

在以下链接阅读更多信息：

insertWithOnConflict documentation

insertWithOnConflict 文档

CONFLICT_REPLACE flag

CONFLICT_REPLACE 标志

回答by Amir Hossein Ghasemi

for me, none of the approaches are work if I don't have "_id"

对我来说，如果我没有“_id”，这些方法都不起作用

you should first call update, if the affected rows are zero, then insert it with ignore:

您应该首先调用更新，如果受影响的行为零，则将其插入忽略：

 String selection = MessageDetailTable.SMS_ID+" =?";
 String[] selectionArgs =  new String[] { String.valueOf(md.getSmsId())};

 int affectedRows = db.update(MessageDetailTable.TABLE_NAME, values, selection,selectionArgs);

 if(affectedRows<=0) {
     long id = db.insertWithOnConflict(MessageDetailTable.TABLE_NAME, null, values, SQLiteDatabase.CONFLICT_IGNORE);
 }

回答by user1154664

Use INSERT OR REPLACE.

使用插入或替换。

This is the correct way to do it.

这是正确的做法。