我如何利用 SQLite 的 DEFAULT <a_value> 的等效项?在 Android 房间 [英] How do I utilise the equivalent of SQLite's DEFAULT <a_value> in Android Room

问题描述

请注意，这是故意使用 Q 和 A 来显示/解释使用 Room 时之间的差异Objects 的默认实例化(构造)值和 SQLite 的 DEFAULT 子句以及如何利用它们

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

在 Room 中，您可以编码 @ColumInfo(defaultValue = "a_default_value") 例如(和后续演示)

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"
)

考虑道:-

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

然后使用:-

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 是 0 因为 val flag1: Int = 0
    • Flag2 是 10，同样
    • otherData 是对象默认值的默认值，同样
    • Flag1 is 0 because of val flag1: Int = 0
    • Flag2 is 10, likewise
    • otherData is default from object's default value, likewise

    如果使用:-

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

    然后结果是相似的，除了 Blah 被分配给 otherData :-

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

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

    那么使用 defaultValue 值怎么样?如何?(虽然没有那么多用途/区别)

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

    • 上述实体是有意编写的，以包含可能不必要/无用的差异.

    推荐答案

    您可以使用列定义的 DEFAULT 值(也就是 Room 中的 defaultValue=a_default_value").

    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.

    要在通过 Room 插入时使用默认值(假设由于某种原因您无法使用对象的默认值)您必须使用定义特定列的插入，而不是列) 使用 DEFAULT(即表中的默认编码).

    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).

    一个例外是，如果每一列都是默认的(在上面可能是因为 userid 列是 rowid 的别名，因为它是使用 userid INTEGER PRIMARY KEY 有效定义的)

    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)

    • 如果你使用 @ColumnInfo(defaultValue = ?) 那么表中的列将被定义为 the_column_name the_columntype DEFAULT '?' 作为示例中的实体问题导致使用
    创建表
    • 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`))
    

    • 上面的 SQL 是在编译后从 生成的 java 中提取的，它是 Room 基于实体生成的.
    • The SQL above has been extracted, after compiling, from the generated java, which Room generates based upon the Entity(ies).

    例如，如果需要使用所有定义的默认值(flag1、flag2 和 otherData 列)插入一行，则应仅指定 userId 列并提供值.SQL 将是 INSERT INTO user (userid) VALUES(the_userid_value).

    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).

    • 根据

    • INSERT INTO table VALUES(...); 第一种形式(使用VALUES"关键字)在现有表中创建一个或多个新行.如果省略 table-name 后面的 column-name 列表，则插入每行的值数必须与表中的列数相同.在这种情况下，从 VALUES 列表的每一项中计算最左边的表达式的结果被插入到每个新行的最左边的列中，对于每个后续表达式，依此类推.如果指定了 column-name 列表，则 VALUE 列表的每一项中的值数必须与指定的列数匹配.新行的每个命名列都填充有计算相应 VALUES 表达式的结果.未出现在列列表中的表列填充有默认列值(作为 CREATE TABLE 语句的一部分指定)，如果未指定默认值，则填充 NULL.

    • 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

    对于这样的 SQL Room 表示应该使用 @Query 而不是 @Insert

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

    • Room 提供了方便的注释，用于定义执行简单插入、更新和删除的方法，而无需您编写 SQL 语句.

    如果您需要定义更复杂的插入、更新或删除，或者如果您需要查询数据库中的数据，请改用查询方法.

    • https://developer.android.com/training/data-存储/房间/访问数据

    对于上述实体，这可能是:-

    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)
    

    例外情况是，如果所有列都是默认值，在这种情况下 INSERT INTO the_table DEFAULT VALUES 将被使用，例如:-

    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
    

    然后付诸实践:-

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

    结果:-

    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
    

    即Flag1、Flag2 和 OtherData 使用表定义的默认值，对于后者，userid 也默认为生成的 userid(通常比现有的最高值大 1，但也可能不是).

    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).

    这篇关于我如何利用 SQLite 的 DEFAULT <a_value> 的等效项?在 Android 房间的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！