这里介绍一种在Room
库中使用真正的UPSERT
子句的方法。
这种方法的主要优点是您可以更新不知道其ID的行。
- 在项目中设置Android SQLite支持库,以在所有设备上使用现代SQLite功能:
- 从BasicDao继承您的daos。
- 可能,您想在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),
)
}
val rawEntity = someDao.upsert(SomeEntity(0, "name", "key-1"))
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) {
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}`" }
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
)
}
}
ON CONFLICT UPDATE SET a = 1, b = 2
在Room
的@Query
注释中不受支持。 - isabsent