How to get the next auto-increment id in Android room?-CodePudding

Here is my room entity object:

@Entity(tableName = "user_account", indices = [Index(value = ["user_name", "user_type"], unique = true)])
data class DataUserAccountEntity(
    @PrimaryKey(autoGenerate = true) @ColumnInfo(name = "auto_id")    val autoId:    Int,
    @NonNull                         @ColumnInfo(name = "user_name")  val userName:  String,
    @NonNull                         @ColumnInfo(name = "user_photo") val userPhoto: String,
    @NonNull                         @ColumnInfo(name = "user_type")  val userType:  Int,
)

Here is my Dao entity object:

@Dao
interface DataUserAccountDao {
    @Query("SELECT * FROM user_account WHERE auto_id = :autoId LIMIT 1")
    fun getUserAccount(autoId: Int): DataUserAccountEntity

    @Query("SELECT * FROM user_account ORDER BY auto_id ASC")
    fun getAllUserAccounts(): List<DataUserAccountEntity>
}

Since auto_id is set to @PrimaryKey(autoGenerate = true), how would I query room for the next value?

(i.e. I am looking for the auto_id that would be generated if I insert a new row into the local database right now)

CodePudding user response：

Although I appreciate the response, this does not solve my problem. I need the number BEFORE insertion.

If autoGenerate=true is coded then you can use:-

@Query("SELECT seq 1 FROM sqlite_sequence WHERE name=:tableName")
fun getNextRowidFromTable(tableName: String): Long

HOWEVER, there is no guarantee that the next allocated value will be 1 greater than the last and thus the value obtained from the query. As per:-

The behavior implemented by the AUTOINCREMENT keyword is subtly different from the default behavior. With AUTOINCREMENT, rows with automatically selected ROWIDs are guaranteed to have ROWIDs that have never been used before by the same table in the same database. And the automatically generated ROWIDs are guaranteed to be monotonically increasing.

and

Note that "monotonically increasing" does not imply that the ROWID always increases by exactly one. One is the usual increment. However, if an insert fails due to (for example) a uniqueness constraint, the ROWID of the failed insertion attempt might not be reused on subsequent inserts, resulting in gaps in the ROWID sequence. AUTOINCREMENT guarantees that automatically chosen ROWIDs will be increasing but not that they will be sequential.

What coding autoGereate=true does is include the AUTOINCREMENT keyword. This doesn't actually cause auto generation rather that for every table (using Room at least) a value is generated an placed into a hidden column rowid. If a column is specified with a type of INTEGER and the column is the PRIMARY KEY (not part of a composite primary key) then that column is an alias of the rowid. If such a column has a value specified for the column when inserting the row then that value (as long as it is unique) is assigned to the column (and therefore rowid).

AUTOINCREMENT is a constraint (rule) that enforces the use of a value higher than any that have been assigned (even if such rows are deleted).

AUTOINCREMENT handles this subtle difference by using the sqlite_sequence table to store the assigned rowid or alias thereof obviously updating the value to always be the highest. The sqlite_sequence table will not exist if AUTOINCREMENT aka autoGenerate=true is not coded in any @Entity annotated classes (which are passed to the @Database annotated class via the entities parameter of the annotation)

You may wish to refer to https://www.sqlite.org/autoinc.html

CodePudding user response：

You can get the id of last saved record in room database.

@Query("SELECT auto_id FROM user_account ORDER BY auto_id DESC LIMIT 1")
fun getLastUserAccount(autoId: Int): Long

This will return you last row id. Suppose you have 5 records, it will return 4.

Now, you increment the returned_id, to get new one.

And verify after inserting,

@Insert(onConflict = OnConflictStrategy.REPLACE)
suspend fun insertCountry(dataUserAccountEntity: DataUserAccountEntity): Long

Long is the return type of this new record. if it's -1, it means the operation got failed else will return the auto-generated ID