How do I utilise the equivalent of SQLite's DEFAULT <a_value> in Android Room

user_4744514 0
Viewed 114

Note this is intentionally a Q and A to show/explain the difference, when using Room, between an Objects's default instantiaion (constrcution) values and SQLite's DEFAULT clause and how to utilise either

In Room you can code @ColumInfo(defaultValue = "a_default_value") for example (and subsequent demonstration)

@Entity
data class User(
    @PrimaryKey
    val userId: Long?, /* allow null for auto assigned ID */
    @ColumnInfo(defaultValue = "1000")
    val flag1: Int = 0,
    @ColumnInfo(defaultValue = "10000")
    val flag2: Int = 10,
    @ColumnInfo(defaultValue = "default from column DEFAULT clause")
    val otherData: String? = "default from object's default value"
)

Considering the Dao :-

@Insert
abstract fun insert(user: User): Long

Then using :-

dao.insert(User(null))

results in the Object's default values being assigned as per

User is 1 Flag1 is 0 Flag2 is 10 OtherData is default from object's default value

  • i.e.

    • Flag1 is 0 because of val flag1: Int = 0
    • Flag2 is 10, likewise
    • otherData is default from object's default value, likewise

If using :-

dao.insert(User(100, otherData = "Blah"))

Then the result is similar except that Blah is assigned to otherData :-

User is 100 Flag1 is 0 Flag2 is 10 OtherData is Blah

So what about using defaultValue value(s)? How? (albeit it not that much use/difference)

  • The above Entity has been purposefully written to include the probably unnecessary/useless difference.
android-room
1 Answers

You can utilise the column(s) defined DEFAULT values (aka in Room the defaultValue="a_default_value").

Noting that coding this, on some occasions may be necessary/advantageous, such as when migrating so that existing rows have a suitable value.

To use the default value when insert via Room (assuming that for some reason you cannot utilise the Object's default value) you have to utilise an insert that defines the specific column(s), other than the column(s) that are to utilise the DEFAULT (i.e. the default coded in the table).

An exception is that if every column is to default (possible in the above because the userid column is an alias of the rowid due to it effectively being defined using userid INTEGER PRIMARY KEY)

  • If you use @ColumnInfo(defaultValue = ?) then the column in the table will be defined with the_column_name the_columntype DEFAULT '?' as an example the Entity in the Question results in the table being created using

:-

CREATE TABLE IF NOT EXISTS `User` (
    `userId` INTEGER, 
    `flag1` INTEGER NOT NULL DEFAULT 1000, 
    `flag2` INTEGER NOT NULL DEFAULT 10000, 
    `otherData` TEXT DEFAULT 'default from column DEFAULT clause', 
    PRIMARY KEY(`userId`))
  • The SQL above has been extracted, after compiling, from the generated java, which Room generates based upon the Entity(ies).

For example if the need is to insert a row utilising all the defined defaults (flag1, flag2 and otherData columns) then only the userId column should be specified and a value supplied. The SQL would be INSERT INTO user (userid) VALUES(the_userid_value).

  • As per

    • INSERT INTO table VALUES(...); The first form (with the "VALUES" keyword) creates one or more new rows in an existing table. If the column-name list after table-name is omitted then the number of values inserted into each row must be the same as the number of columns in the table. In this case the result of evaluating the left-most expression from each term of the VALUES list is inserted into the left-most column of each new row, and so forth for each subsequent expression. If a column-name list is specified, then the number of values in each term of the VALUE list must match the number of specified columns. Each of the named columns of the new row is populated with the results of evaluating the corresponding VALUES expression. Table columns that do not appear in the column list are populated with the default column value (specified as part of the CREATE TABLE statement), or with NULL if no default value is specified.

    • https://sqlite.org/lang_insert.html

For such SQL Room indicates that an @Query should be used as opposed to an @Insert as per

  • Room provides convenience annotations for defining methods that perform simple inserts, updates, and deletes without requiring you to write a SQL statement.

  • If you need to define more complex inserts, updates, or deletes, or if you need to query the data in the database, use a query method instead.

For the above Entity this could be :-

@Query("INSERT INTO user (userid) /*<<<<< defined columns all other columns will be defaults */ VALUES(:userid)")
abstract fun insert(userid: Long)

As the exception is that if ALL columns are to default in which case INSERT INTO the_table DEFAULT VALUES would be used e.g. :-

@Query("INSERT INTO user DEFAULT VALUES")
abstract fun insert(): Long

Putting this into practice then :-

    dao.insert(10000) /* @Query insert to demo defaultValue */
    dao.insert() /* special using INSERT INTO table DEFAULT VALUES */

results in :-

User is 10000 Flag1 is 1000 Flag2 is 10000 OtherData is default from column DEFAULT clause
User is 10001 Flag1 is 1000 Flag2 is 10000 OtherData is default from column DEFAULT clause

i.e. Flag1, Flag2 and OtherData use the table defined default values and for the latter the userid also defaults to a generated userid (typically 1 greater than the highest existing but it may not be).

user_47445140
Related