安卓/ SQLite的：插入，更新表列，以保持标识符 [英] Android/SQLite: Insert-Update table columns to keep the identifier

问题描述

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

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

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

在最后的冲突子句导致该行是降至当新的插入完成附带相同的坐标。该 SQLite的文档包含有关冲突条款的进一步信息。

The conflict-clause at the end causes that rows are dropped when new inserts are done that come with the same coordinates. The SQLite documentation contains further information about the conflict-clause.

相反，我想为保存前行，只是更新其列。什么是最有效的方式来做到这一点的一款Android / SQLite的环境？

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

• 正如 CREATE TABLE 语句冲突的条款。
• 作为插入触发。
• 正如的ContentProvider＃插入方法的条件子句。
• ...没有更好的，你能想到

• As a conflict-clause in the CREATE TABLE statement.
• As an INSERT trigger.
• As a conditional clause in the ContentProvider#insert method.
• ... any better you can think off

我会认为这是更好的性能来处理数据库内的这种冲突。另外，我觉得很难重写的ContentProvider＃插入的方法来考虑的插入更新的场景。这里是$ C $的c中的插入方法：

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

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

我有问题搞清楚如何在这里适用的替代调用的ContentProvider＃更新。此外，这不是我的最佳解决方案反正。

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

编辑：

@CommonsWare：我试图执行你的建议使用插入或更换。我想出了这个丑陋的一块code。

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

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

当我检查了SQLite数据库，但是，等于列的仍然被删除并用新的IDS 插入。我不明白为什么出现这种情况，并认为其原因是我的冲突条款。但是，文档状态正好相反。

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 documentation states the opposite.

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

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

这尝试的另一个缺点是，你失去它的回报由INSERT语句id的值。为了弥补这一点，我终于找到了一个选项，以索要 last_insert_rowid 。这是因为在dtmilano和swiz 的帖子解释。我是，但是，不知道这是否是安全的，因为另外一个插件可其间发生。

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.

推荐答案

我可以理解感知的概念，它是最好的表现做这一切的逻辑SQL，但也许最简单的（至少code）解决方案在这种情况下，最好的一个？为什么不尝试更新，然后再使用 insertWithOnConflict（）与 CONFLICT_IGNORE 做插入（如有必要）和获取该行ID，您需要：

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_IGNORE to do the insert (if necessary) and get the row id you need:

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

有一个简单的解决方案是检查的返回值的更新（）键，只做插入如果受影响的数量是零，但随后会有一个情况下，你无法获得现有行的ID，而无需额外的选择。这种形式插入总会回报给你正确的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.

如果你想要做大量的这些一次，你可以看看 bulkInsert（）方法对您的供应商，在那里你可以运行内部的多个插入单成交。在这种情况下，因为你并不需要返回更新记录的 ID ，在简单的解决方案应该只是正常工作：

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 id of the updated record, the "simpler" solution should work just fine:

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

在真理，交易code是什么使事情更快地通过最小化时代的数据库内存写入文件的数量， bulkInsert（）只允许多个 ContentValues​​ 来传递与给提供一个单一的电话。

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 ContentValues to be passed in with a single call to the provider.

这篇关于安卓/ SQLite的：插入，更新表列，以保持标识符的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！