android - 为什么 insertWithOnConflict(..., CONFLICT_IGNORE) 返回 -1(错误)？

Question

我有一个 SQLite 表:

CREATE TABLE regions (_id INTEGER PRIMARY KEY, name TEXT, UNIQUE(name));

还有一些安卓代码:

Validate.notBlank(region);
ContentValues cv = new ContentValues();
cv.put(Columns.REGION_NAME, region);
long regionId = 
    db.insertWithOnConflict("regions", null, cv, SQLiteDatabase.CONFLICT_IGNORE);
Validate.isTrue(regionId > -1,
    "INSERT ON CONFLICT IGNORE returned -1 for region name '%s'", region);

在重复行上，insertWithOnConflict() 返回 -1，表示错误，Validate然后抛出:

INSERT ON CONFLICT IGNORE returned -1 for region name 'Overseas'

SQLite ON CONFLICT documentation (强调我的)状态:

When an applicable constraint violation occurs, the IGNORE resolution algorithm skips the one row that contains the constraint violation and continues processing subsequent rows of the SQL statement as if nothing went wrong. Other rows before and after the row that contained the constraint violation are inserted or updated normally. No error is returned when the IGNORE conflict resolution algorithm is used.

Android insertWithOnConflict() documentation状态:

Returns the row ID of the newly inserted row OR the primary key of the existing row if the input param 'conflictAlgorithm' = CONFLICT_IGNORE OR -1 if any error

CONFLICT_REPLACE 不是一个选项，因为替换行将更改它们的主键，而不仅仅是返回现有键:

sqlite> INSERT INTO regions (name) VALUES ("Southern");
sqlite> INSERT INTO regions (name) VALUES ("Overseas");
sqlite> SELECT * FROM regions;
1|Southern
2|Overseas
sqlite> INSERT OR REPLACE INTO regions (name) VALUES ("Overseas");
sqlite> SELECT * FROM regions;
1|Southern
3|Overseas
sqlite> INSERT OR REPLACE INTO regions (name) VALUES ("Overseas");
sqlite> SELECT * FROM regions;
1|Southern
4|Overseas

我认为 insertWithOnConflict() 应该在重复行上向我返回重复行的主键(_id 列)——所以我应该永远收到此插入的错误。为什么 insertWithOnConflict() 会抛出错误？我需要调用什么函数才能始终获得有效的行 ID？

Answer 1

不幸的是，您的问题的答案是文档完全错误并且没有这样的功能。

有an open bug from 2010正好解决了这个问题，尽管已经有 80 多人为其加注星标，但 Android 团队没有官方回应。

问题是 also discussed on SO here .

如果您的用例冲突严重(即大多数时候您希望找到现有记录并希望返回该 ID)，您建议的解决方法似乎是可行的方法。另一方面，如果您的用例大多数情况下您希望没有现有记录，那么以下解决方法可能更合适:

try {
  insertOrThrow(...)
} catch(SQLException e) {
  // Select the required record and get primary key from it
} 

这是此解决方法的独立实现:

public static long insertIgnoringConflict(SQLiteDatabase db,
                                          String table,
                                          String idColumn,
                                          ContentValues values) {
    try {
        return db.insertOrThrow(table, null, values);
    } catch (SQLException e) {
        StringBuilder sql = new StringBuilder();
        sql.append("SELECT ");
        sql.append(idColumn);
        sql.append(" FROM ");
        sql.append(table);
        sql.append(" WHERE ");

        Object[] bindArgs = new Object[values.size()];
        int i = 0;
        for (Map.Entry<String, Object> entry: values.valueSet()) {
            sql.append((i > 0) ? " AND " : "");
            sql.append(entry.getKey());
            sql.append(" = ?");
            bindArgs[i++] = entry.getValue();
        }

        SQLiteStatement stmt = db.compileStatement(sql.toString());
        for (i = 0; i < bindArgs.length; i++) {
            DatabaseUtils.bindObjectToProgram(stmt, i + 1, bindArgs[i]);
        }

        try {
            return stmt.simpleQueryForLong();
        } finally {
            stmt.close();
        }
    }
}