Here is a way to use a real UPSERT clause in Room library.
The main advantage of this method is that you can update rows for which you don't know their ID.
- Setup Android SQLite support library in your project to use modern SQLite features on all devices:
- Inherit your daos from BasicDao.
- Probably, you want to add in your BasicEntity:
abstract fun toMap(): Map<String, Any?>
Use UPSERT in your Dao:
@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
}
}
Entity example:
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
)
}
}
