CODEWITHSUNDEEP
databaseinsertandroid-room
Flatpick13
Flatpick13

Reputation: 81

Android room database insert/update sequence

I have an application with a Room database, including DAO and Repository. The sequence of events is as follows:

  1. Insert a new row.
  2. Send the row to the server
  3. Update the row to indicate the row has been sent.

The problem is that the ID of the new row is not always available before I send to the server, or when I do the update. Therefore the DAO update method doesn't work since it doesn't have a proper ID.
Is there a trick to making the Insert return the ID before I go on? I know that is circumventing the benefit of an async process, but may be necessary in this case. I was hoping someone else may have solved this problem.

Upvotes: 3

Views: 2346

Answers (1)

MikeT
MikeT

Reputation: 56948

The problem is that the ID of the new row is not always available before I send to the server

For tables in Room there is always a value that uniquely identifies the inserted row available if the row is inserted via the convenience @Insert (different matter if using @Query("INSERT INTO ....."))

  • note if the row was not inserted due to a trapped/handled conflict then the value returned will be -1.

Is there a trick to making the Insert return the ID before I go on?

Not really a trick, at least if IDs are an integer type, ,more what is expected/anticipated usage. Just have the insert Dao return Long (or long if java)

e.g. (Java)

@Insert
long insert(TheEntity theEntity);

or (Kotlin)

@Insert
fun insert(theEntity: TheEntity): Long

The Long/long returned will be the value of the rowid column or an alias thereof.

rowid is a column that ALL tables have (except those defined with WITHOUT ROWID, which Room doesn't allow), although it is a hidden column. If you code an integer type annotated with @PrimaryKey then the column will be an alias of the rowid.

  • note by integer type you could have

    • Long, long,
    • Int, int Integer,
    • Short, short,
    • Byte, byte,
    • even, albeit it pretty useless, Boolean, boolean.

  • However, Long,long is the most suitable value for an id column as SQLite can store a 64bit signed integer which equates to Long/long. Hence why inserts can only return Long/long.

    • The is little if any advantage to not using Long/long SQLite will store the value in as little space as possible from 1 byte - 8 bytes. So a value of 1 be it byte/short/int/long will take up a single byte when stored in the database.

    • e.g. for Kotlin code fun insert(toDoData: ToDoData): Int and you get error: Not sure how to handle insert method's return type. public abstract int insert(@org.jetbrains.annotations.NotNull()

Non-Integer ID types/Composite Primary Keys

Other column types String, Byte[], Double, Float etc, or composite primary keys, will not be aliases of the rowid column. To get the primary key from these other types you could use the rowid returned, as it is unique to the row, to then get the primary key e.g. (for @PrimaryKey val id: String)

@Query("SELECT id FROM todo_table WHERE rowid=:rowid" )
fun getPrimaryKeyByRowid(rowid: Long): String`

Of course you could bypass getting the PrimayKey value(s) and pass and then use the rowid value to determine the row to update, but not using the @Update convenience method/function but an @Query update with UPDATE .... WHERE rowid=:passRowid.

Upvotes: 3

Related Questions