Room DB: What does UPSERT returns?

Question

While @Insert returns Long or collection of it for rowId and @Delete or @Update returns Int for number of rows affected, then what does @Upsert can return?

Reference: https://developer.android.com/training/data-storage/room/accessing-data

Answer 1

The function/method invoked with an @Upsert annotation can return

1. nothing/void, or
2. a Long/long, or
3. an array of Longs/longs (if multiple upserts are possible).

• the returned value should be the rowid of the inserted or replaced row.
  • the rowid is a normally hidden column that all typical Room tables have.
  • if the PRIMARY KEY is a single field/member and that field/member is an integer type (Long, Int, int, long even Boolean and so on) then the PRIMARY KEY will be an alias of the rowid and thus the PRIMARY KEY of the inserted row
  • if the PRIMARY KEY consists of multiple columns or is not a single integer type column then the rowid still exists and can be used
• it should be noted that Room does not use the SQLite UPSERT (at least currently) but uses an an SQLite INSERT OR REPLACE (and hence returns the same values that an @Insert does).

It is easy enough to ascertain by coding and compiling

e.g. using

@Upsert
fun upsert(parent: PartCargo): Int

Fails top compile with Not sure how to handle upsert method's return type.

Whilst

@Upsert
fun upsert(parent: PartCargo): Long

@Upsert
fun upsert(parent: PartCargo)

and

compile successfully.

However

@Upsert
fun upsert(parent: PartCargo): LongArray

Fails with Upsert method accepts a single parameter but the return type is a collection of elements.

Whilst

@Upsert
fun upsert(parents: List<PartCargo>): LongArray

Compiles successfully.

• all the above are Kotlin, but Java is similar (hence void and long in the initial part of the answer).

Additional

can you add a reference about Room's implementation of Upsert that behind the scene only do insert and replace before I accept this answer?

A reference, if there is one, is not necessary. What is actually done "behind the scenes" can easily be established.

The "behind the scenes", is the annotation processer generating java code when compiling. This java generated java code forms part of the package and is what is actually used at runtime.

The 2 core/primary annotations are the @Database and @Dao annotations. These determine the resultant classes that contain the respective generated java. The classes will be the name of the class/interface being processed suffixed with _Impl.

The generated java is placed with a subfolder of the folder, that via Android View in Android Studio appears as Java(Generated). There will typically be two sub-folders with the same name. One will be seen to contain the respective classes that have been generated.

e.g. the code extracts above were taken from the following:-

The extracts were from the @Dao annotated interface interface DetailsDAO .... and hence the DetailsDAO_Impl class that has been generated.

A search on Upsert locates:-

• heed the warning about NOT editing the files
  • actually can be useful at times to ignore e.g. to add a breakpoint for debugging (if the project is not then compiled as compiling will overwrite the generated java)

It is then just a matter of ascertaining the respective code utilised and the SQL invoked. For the above the eventual respective code is:-

this.__insertionAdapterOfPartCargo = new EntityInsertionAdapter<PartCargo>(__db) {
      @Override
      @NonNull
      protected String createQuery() {
        return "INSERT OR REPLACE INTO `parts` (`tId`,`detailsTId`,`id`,`name`) VALUES (nullif(?, 0),?,?,?)";
      }