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.
Another approach I can think of is to get the entity via DAO by query, and then perform any desired updates. This may be less efficient compared to the other solutions in this thread in terms of runtime because of having to retrieve the full entity, but allows much more flexibility in terms of operations allowed such as on what fields/variable to update.
For example :
If the table has more than one column, you can use
@Insert(onConflict = OnConflictStrategy.REPLACE)
to replace a row.
Reference - Go to tips Android Room Codelab
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.
Should be possible with this sort of statement:
For more elegant way to do that I would suggest two options:
Checking for return value from
insert
operation withIGNORE
as aOnConflictStrategy
(if it equals to -1 then it means row wasn't inserted):Handling exception from
insert
operation withFAIL
as aOnConflictStrategy
:Perhaps you can make your BaseDao like this.
secure the upsert operation with @Transaction, and try to update only if insertion is failed.