[FIXED] Android Room Persistence Library: Upsert

Issue

Android's Room persistence library graciously includes the @Insert and @Update annotations that work for objects or collections. I however have a use case (push notifications containing a model) that would require an UPSERT as the data may or may not exist in the database.

Sqlite doesn't have upsert natively, and workarounds are described in this SO question. Given the solutions there, how would one apply them to Room?

To be more specific, how can I implement an insert or update in Room that would not break any foreign key constraints? Using insert with onConflict=REPLACE will cause the onDelete for any foreign key to that row to be called. In my case onDelete causes a cascade, and reinserting a row will cause rows in other tables with the foreign key to be deleted. This is NOT the intended behavior.

Solution

EDIT:

Starting in version 2.5.0-alpha03, Room now has support for an @Upsert annotation. An example of its use can be seen in this pull request in the "Now in Android" sample app.

OLD ANSWER:

I could not find a SQLite query that would insert or update without causing unwanted changes to my foreign key, so instead I opted to insert first, ignoring conflicts if they occurred, and updating immediately afterwards, again ignoring conflicts.

The insert and update methods are protected so external classes see and use the upsert method only. Keep in mind that this isn't a true upsert as if any of the MyEntity POJOS have null fields, they will overwrite what may currently be in the database. This is not a caveat for me, but it may be for your application.

@Insert(onConflict = OnConflictStrategy.IGNORE)
protected abstract void insert(List<MyEntity> entities);

@Update(onConflict = OnConflictStrategy.IGNORE)
protected abstract void update(List<MyEntity> entities);

@Transaction
public void upsert(List<MyEntity> entities) {
    insert(models);
    update(models);
}

Answered By - Tunji_D
Answer Checked By - Terry (JavaFixing Volunteer)