Android Room持久化库：Upsert

这里介绍一种在Room库中使用真正的UPSERT子句的方法。

这种方法的主要优点是您可以更新不知道其ID的行。

1. 在项目中设置Android SQLite支持库，以在所有设备上使用现代SQLite功能：
2. 从BasicDao继承您的daos。
3. 可能，您想在BasicEntity中添加：abstract fun toMap(): Map<String, Any?>

在Dao中使用UPSERT：

@Transaction
private suspend fun upsert(entity: SomeEntity): Map<String, Any?> {
    return upsert(
        SomeEntity.TABLE_NAME,
        entity.toMap(),
        setOf(SomeEntity.SOME_UNIQUE_KEY),
        setOf(SomeEntity.ID),
    )
}

// An entity has been created. You will get ID.
val rawEntity = someDao.upsert(SomeEntity(0, "name", "key-1"))

// An entity has been updated. You will get ID too, despite you didn't know it before, just by unique constraint!
val rawEntity = someDao.upsert(SomeEntity(0, "new name", "key-1"))

BasicDao：

import android.database.Cursor
import androidx.room.*
import androidx.sqlite.db.SimpleSQLiteQuery
import androidx.sqlite.db.SupportSQLiteQuery

abstract class BasicDao(open val database: RoomDatabase) {
    /**
     * Upsert all fields of the entity except those specified in [onConflict] and [excludedColumns].
     *
     * Usually, you don't want to update PK, you can exclude it in [excludedColumns].
     *
     * [UPSERT](https://www.sqlite.org/lang_UPSERT.html) syntax supported since version 3.24.0 (2018-06-04).
     * [RETURNING](https://www.sqlite.org/lang_returning.html) syntax supported since version 3.35.0 (2021-03-12).
     */
    protected suspend fun upsert(
        table: String,
        entity: Map<String, Any?>,
        onConflict: Set<String>,
        excludedColumns: Set<String> = setOf(),
        returning: Set<String> = setOf("*")
    ): Map<String, Any?> {
        val updatableColumns = entity.keys
            .filter { it !in onConflict && it !in excludedColumns }
            .map { "`${it}`=excluded.`${it}`" }

        // build sql
        val comma = ", "
        val placeholders = entity.map { "?" }.joinToString(comma)
        val returnings = returning.joinToString(comma) { if (it == "*") it else "`${it}`" }
        val sql = "INSERT INTO `${table}` VALUES (${placeholders})" +
                " ON CONFLICT(${onConflict.joinToString(comma)}) DO UPDATE SET" +
                " ${updatableColumns.joinToString(comma)}" +
                " RETURNING $returnings"

        val query: SupportSQLiteQuery = SimpleSQLiteQuery(sql, entity.values.toTypedArray())
        val cursor: Cursor = database.openHelper.writableDatabase.query(query)

        return getCursorResult(cursor).first()
    }

    protected fun getCursorResult(cursor: Cursor, isClose: Boolean = true): List<Map<String, Any?>> {
        val result = mutableListOf<Map<String, Any?>>()
        while (cursor.moveToNext()) {
            result.add(cursor.columnNames.mapIndexed { index, columnName ->
                val columnValue = if (cursor.isNull(index)) null else cursor.getString(index)
                columnName to columnValue
            }.toMap())
        }

        if (isClose) {
            cursor.close()
        }
        return result
    }
}

实体示例：

import androidx.room.ColumnInfo
import androidx.room.Entity
import androidx.room.Index
import androidx.room.PrimaryKey

@Entity(
    tableName = SomeEntity.TABLE_NAME,
    indices = [Index(value = [SomeEntity.SOME_UNIQUE_KEY], unique = true)]
)
data class SomeEntity(
    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = ID)
    val id: Long,

    @ColumnInfo(name = NAME)
    val name: String,

    @ColumnInfo(name = SOME_UNIQUE_KEY)
    val someUniqueKey: String,
) {
    companion object {
        const val TABLE_NAME = "some_table"
        const val ID = "id"
        const val NAME = "name"
        const val SOME_UNIQUE_KEY = "some_unique_key"
    }

    fun toMap(): Map<String, Any?> {
        return mapOf(
            ID to if (id == 0L) null else id,
            NAME to name,
            SOME_UNIQUE_KEY to someUniqueKey
        )
    }
}

另一种想到的方法是通过查询DAO获取实体，然后执行任何所需的更新。
与本线程中其他解决方案相比，由于需要检索完整实体，因此在运行时可能不那么高效，但允许更多操作的灵活性，例如可以更新哪些字段/变量。
例如：

private void upsert(EntityA entityA) {
   EntityA existingEntityA = getEntityA("query1","query2");
   if (existingEntityA == null) {
      insert(entityA);
   } else {
      entityA.setParam(existingEntityA.getParam());
      update(entityA);
   }
}

使用这种语句应该是可行的：

INSERT INTO table_name (a, b) VALUES (1, 2) ON CONFLICT UPDATE SET a = 1, b = 2

如果您有遗留代码：一些Java实体和BaseDao作为接口（您无法添加函数体），或者您太懒了，不想用extends替换所有Java子类中的implements。

注意：它仅适用于Kotlin代码。我相信您会用Kotlin编写新代码，我是对的吧？ :)

最后一个懒惰的解决方案是添加两个Kotlin扩展函数：

fun <T> BaseDao<T>.upsert(entityItem: T) {
    if (insert(entityItem) == -1L) {
        update(entityItem)
    }
}

fun <T> BaseDao<T>.upsert(entityItems: List<T>) {
    val insertResults = insert(entityItems)
    val itemsToUpdate = arrayListOf<T>()
    insertResults.forEachIndexed { index, result ->
        if (result == -1L) {
            itemsToUpdate.add(entityItems[index])
        }
    }
    if (itemsToUpdate.isNotEmpty()) {
        update(itemsToUpdate)
    }
}