Batch InsertOrUpate in Room ORM

Question

I am trying hands with android room ORM. Many ORM's have this method insertOrUpdate method. How do I achieve this? I want to insert or update multiple rows in a single transaction,i.e., if row is present then update it else insert a new entry.

Answer 1

You can do an insert with a conflict resolution set to replace:

@Dao
public interface MyDao {
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    public void insertUsers(User... users);
}

Notice that (from Room documentation):

Note: SQLite handles @Insert(onConflict = REPLACE) as a set of REMOVE and REPLACE operations instead of a single UPDATE operation. This method of replacing conflicting values could affect your foreign key constraints. For more details, see the SQLite documentation for the ON_CONFLICT clause.

In Room, this single insert statement is run inside a transaction, so it should be safe most of the time.

Alternatively, you can run your statements inside a transaction:

myDb.runInTransaction(new Runnable() {
     @Override
     public void run() {
         // Your operations
     }
});

Or, from this other StackOverflow answer:

@Dao
public abstract class ProductDao {
    @Insert
    public abstract void insert(Product product);

    @Delete
    public abstract void delete(Product product);

    @Transaction
    public void insertAndDeleteInTransaction(Product newProduct, Product oldProduct) {
        // Anything inside this method runs in a single transaction.
        insert(newProduct);
        delete(oldProduct);
    }
}

Answer 2

For batch transaction insert/update you can do that Transaction Annotation:

Annotating a method with @Transaction makes sure that all database operations you’re executing in that method will be run inside one transaction. The transaction will fail when an exception is thrown in the method body.

@Dao
abstract class UserDao {

    @Transaction
    open fun updateData(users: List<User>) {
        deleteAllUsers()
        insertAll(users)
    }
    @Insert
    abstract fun insertAll(users: List<User>)
    @Query("DELETE FROM Users")
    abstract fun deleteAllUsers()
}

You might want to use the @Transaction annotation for @Query methods that have a select statement, in the following cases:

When the result of the query is fairly big. By querying the database in one transaction, you ensure that if the query result doesn’t fit in a single cursor window, it doesn’t get corrupted due to changes in the database between cursor window swaps. When the result of the query is a POJO with @Relation fields. The fields are queries separately so running them in a single transaction will guarantee consistent results between queries. @Delete, @Update and @Insert methods that have multiple parameters are automatically run inside a transaction.

Answer 3

You can also do something like this

@Insert
suspend fun insertAll(vararg users: User)

Answer 4

First did an update and then insert with onConflict strategy for insertion as IGNORE