我本以为那不会奏效.首先是因为主键永远不应该为空,其次是因为如果没有自动生成,id将如何行为?
Room实际上使用null
,如果AUTOGENERATE为TRUE,则将0转换为null
,因为SQLite在整数主键的特殊情况下,在指定null
时生成值.
- 因为SQLite要求整数主键是整数,所以它处理NULL,在这种特殊情况下,生成整数值.这在某种程度上是这样解释的
行表(as long as it is not the true primary key or INTEGER PRIMARY KEY)的主键约束实际上与唯一约束是相同的.因为它不是真正的主键,所以允许主键的列为空,这违反了所有的SQL标准.
如果AutoGenerate为真,则生成的代码(如下面的演示所示)包括:
"INSERT OR IGNORE INTO `AutoGenTrueTable` (`id`,`name`) VALUES (nullif(?, 0),?)"
在自动生成为FALSE的情况下,生成的代码使用:-
"INSERT OR IGNORE INTO `AutoGenFalseTable` (`id`,`name`) VALUES (?,?)"
请注意,OR IGNORE
是因为IGNORE
的onConflictStrategy.
如果id字段为0,则后一个示例(即,自动生成为FALSE)将使用值0
通过Android Studio的Android View可以很容易地找到生成的Java.DAO位于与接口/抽象类同名但以_Impl
为后缀的类中.
Room使用的Android SQLite API绑定将NULL转换为关键字null
(Token).
- 这个类的名称与带
@Database
注释的类相同,但后缀为_Impl
,它将有其他有用的代码,例如在createAllTables
方法中可以找到用于创建表的SQL.
他们不是都有相同的ID吗?
NO作为主键是隐式唯一的,因此,如果一个id是主键,则它永远不能与同一个表中的另一个id相同,而不管自动生成的是真还是假.
如果为100 is true,则Room也会将0转换为未提供的值,因此0会生成一个值.
However如果指定值0和if autogenerate is false (explicitly or implicitly by default),则将使用0作为id,不允许多次使用,但可以由插入的onConflictStrategy
处理.
下面的DEMO个说明了上面的(noting that the IGNORE onConflictStrategy is used and hence the errant duplicated 0 id's are just ignored).
A little bit about INTEGER PRIMARY KEY(例如,正确地说是@PrimaryKey val whatever:Int
或更多的Long
)又称rowid列的别名.
- 可以使用BYTE、SHORT等,因为它们是整数类型,但它们的用途有限.
- Long更准确,因为它的值可以和64位有符号整数一样大(
Int
不够大,Long
不够大,在许多情况下这不是问题).
如果列是特定的整数(which Room determines at compile time)并且是主键(在列或表级),则该列是一个特殊的通常隐藏的列rowid的别名,所有表都有该列(没有ROWID表除外,Room通过注释不支持它).
这样的列必须是整数类型值(with the exception of the rowid column or an alias thereof any column type can actually store any type of value, although Room does not support this).此外,如果在插入时没有为这样的列指定值,则该值将由SQLite生成.这通常会比该表的最高rowid大1.
因此,只要没有为该列提供值,就会生成该值(并且很可能比最高值大1).
如果使用Room的autogenerate=true
,那么就会将SQLite AUTOINCREMNET
关键字添加到表定义/模式中.这改变了值的生成方式,因为它是两个值中较大的一个,一个是表中最高的rowid,另一个是记录/曾经使用过的最高rowid值,如果具有最高行ID的行已被删除,则该值可能高于最高行ID.
- 请注意,这假设除了SQLite对表(it can be manipulated 100)的处理之外,不会更改SQLite_Sequence表
简而言之,AUTOINCREMENT
添加了一个约束/规则,规定生成的值必须大于任何使用的值.但是,这要求赋值最高的值必须存储在其他地方.SQLite将这个附加值存储在一个名为sqlite_sequence的表中,每个表有1行.获取和维护这样的值是有开销的,因此SQLite文档声明:
- The AUTOINCREMENT keyword imposes extra CPU, memory, disk space, and disk I/O overhead and should be avoided if not strictly needed. It is usually not needed.见https://www.sqlite.org/autoinc.html
使用NULL或0与autogenerate=true
相同,Room不提供值,因此将生成值.如果提供了任何其他值,则使用该值(如果行已经存在,这将导致唯一冲突,the unique conflict handling with the 101 parameter of the respective annotation ('@Insert' or 102) when inserting or updating)
- 如果使用INSERT或UPDATE查询,则实际的SQLite OR?关于冲突的行动可以被指定,例如"拒绝或忽略……"
如前所述,在不使用AUTOGENERATE=TRUE的情况下,将使用0作为值(参见下面的演示),因此,为了不使用AUTOINCREMENT的间接费用/浪费/低效,则该字段应该为空,并且使用NULL来生成值.
- Java, where primitives (int, long), have a default value of 0 and cannot be null is/was a little different and have some gotchas.个
DEMO个
也许可以考虑下面的演示,其中使用了3个表(实体),其中一个使用了AUTOGENERATE=TRUE,另两个没有指定,因此隐含了AUTOGENERATE=FALSE.另外两个之间的区别是,第一个不允许id为空,默认id为0,第二个允许,id的缺省值为空.
这3 @Entity
个注解课程如下:
@Entity
data class AutoGenTrueTable(
@PrimaryKey(autoGenerate = true)
val id: Long=0,
val name: String
)
@Entity
data class AutoGenFalseTable(
@PrimaryKey
val id: Long=0,
val name: String
)
@Entity
data class AutoGenFalseNullableTable(
@PrimaryKey
val id: Long?=null,
val name: String
)
要演示SQLite_SEQUENCE(从中提取所有数据),然后是POJO:-
data class SQLiteSequence(
val name: String,
val seq: Long
)
一个带有@Dao
个注释的界面(用于插入、专门删除和提取数据):
@Dao
interface AllDAOs {
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(autoGenTrueTable: AutoGenTrueTable): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(autoGenFalseTable: AutoGenFalseTable): Long
@Insert(onConflict = OnConflictStrategy.IGNORE)
fun insert(autoGenFalseNullableTable: AutoGenFalseNullableTable): Long
@Query("DELETE FROM autogentruetable WHERE id >=:highAsOrHigherThanId")
fun deleteFromAGTTByHighIds(highAsOrHigherThanId: Long)
@Query("DELETE FROM autogenfalsetable WHERE id >=:highAsOrHigherThanId")
fun deleteFromAGFTByHighIds(highAsOrHigherThanId: Long)
@Query("DELETE FROM autogenfalsenullabletable WHERE id >=:highAsOrHigherThanId")
fun deleteFromFalseNullableByHighIds(highAsOrHigherThanId: Long)
@Query("SELECT * FROM autogentruetable")
fun getAllFromAutoGenTrue(): List<AutoGenTrueTable>
@Query("SELECT * FROM autogenfalsetable")
fun getAllFromAutoGenFalse(): List<AutoGenFalseTable>
@Query("SELECT * FROM autogenfalsenullabletable")
fun getAllFromAutoGenFalseNullable(): List<AutoGenFalseNullableTable>
@Query("SELECT * FROM sqlite_sequence")
fun getAllFromSQLiteSequence(): List<SQLiteSequence>
}
一个非常直接的@Database
个带注释的抽象类,允许使用主线程来简化演示:
@Database(entities = [AutoGenTrueTable::class,AutoGenFalseTable::class,AutoGenFalseNullableTable::class], exportSchema = false, version = 1)
abstract class TheDatabase: RoomDatabase() {
abstract fun getAllDAOs(): AllDAOs
companion object {
private var instance: TheDatabase? = null
fun getInstance(context: Context): TheDatabase {
if (instance == null) {
instance = Room.databaseBuilder(context, TheDatabase::class.java, "the_database.db")
.allowMainThreadQueries()
.build()
}
return instance as TheDatabase
}
}
}
最后是一些插入/提取和删除数据的活动代码,对于所有3个演示表,在不同阶段将提取的数据(包括SQLITE_MASTER的内容)写入日志(log):
class MainActivity : AppCompatActivity() {
lateinit var db: TheDatabase
lateinit var dao: AllDAOs
override fun onCreate(savedInstanceState: Bundle?) {
super.onCreate(savedInstanceState)
setContentView(R.layout.activity_main)
db = TheDatabase.getInstance(this)
dao = db.getAllDAOs()
var stage = 0
logEverything(stage++)
for (i: Int in 1..3) {
dao.insert(AutoGenTrueTable(0,"AG_TT_ZERO_${i}"))
//dao.insert(AutoGenTrueTable(null,"AG_TT_NULL_${i}")) not nullable cannot be used
dao.insert(AutoGenTrueTable(name = "AG_TT_DEFAULT_${i}"))
dao.insert(AutoGenTrueTable(id = 100,"AG_TT_100_${i}"))
dao.insert(AutoGenFalseTable(0,"AG_FT_ZERO_${i}"))
//dao.insert(AutoGenFalseTable(id = null,name = "AG_FT_NULL_${i}")) not nullable cannot be used
dao.insert(AutoGenFalseTable(name = "AG_FT_DEFAULT_${i}"))
dao.insert(AutoGenFalseTable(id = 100, "AG_FT_100_${i}"))
dao.insert((AutoGenFalseNullableTable(0, "AG_FTNULL_ZERO_${i}") ))
dao.insert((AutoGenFalseNullableTable(null, "AG_FTNULL_NULL_${i}") ))
dao.insert((AutoGenFalseNullableTable( name = "AG_FTNULL_DEFAULT_${i}") ))
dao.insert(AutoGenFalseNullableTable(id = 100, name = "AG_FTNULL_100_${i}"))
}
logEverything(stage++)
dao.deleteFromAGTTByHighIds(100)
dao.deleteFromAGFTByHighIds(100)
dao.deleteFromFalseNullableByHighIds(100)
logEverything(stage++)
for (i: Int in 1..3) {
dao.insert(AutoGenTrueTable(0,"AG_TT_ZERO_${i}"))
//dao.insert(AutoGenTrueTable(null,"AG_TT_NULL_${i}")) not nullable cannot be used
dao.insert(AutoGenTrueTable(name = "AG_TT_DEFAULT_${i}"))
dao.insert(AutoGenTrueTable(id = 100,"AG_TT_100_${i}"))
dao.insert(AutoGenFalseTable(0,"AG_FT_ZERO_${i}"))
//dao.insert(AutoGenFalseTable(id = null,name = "AG_FT_NULL_${i}")) not nullable cannot be used
dao.insert(AutoGenFalseTable(name = "AG_FT_DEFAULT_${i}"))
dao.insert(AutoGenFalseTable(id = 100, "AG_FT_100_${i}"))
dao.insert((AutoGenFalseNullableTable(0, "AG_FTNULL_ZERO_${i}") ))
dao.insert((AutoGenFalseNullableTable(null, "AG_FTNULL_NULL_${i}") ))
dao.insert((AutoGenFalseNullableTable( name = "AG_FTNULL_DEFAULT_${i}") ))
dao.insert(AutoGenFalseNullableTable(id = 100, name = "AG_FTNULL_100_${i}"))
}
logEverything(stage++)
}
fun logEverything(stage: Int) {
Log.d("DBINFO_STARTSTAGE_${stage}","Starting logging of stage ${stage}")
logAllFromAGTT(stage)
logAllFromAGFT(stage)
logAllFromAGFTN(stage)
logAllFromSQLite_Sequence(stage)
}
fun logAllFromAGTT(stage: Int) {
for(a in dao.getAllFromAutoGenTrue()) {
Log.d("DBINFO_AGTT_STG${stage}","ID is ${a.id} NAME is ${a.name}")
}
}
fun logAllFromAGFT(stage: Int) {
for(a in dao.getAllFromAutoGenFalse()) {
Log.d("DBINFO_AGFT_STG${stage}","ID is ${a.id} NAME is ${a.name}")
}
}
fun logAllFromAGFTN(stage: Int) {
for(a in dao.getAllFromAutoGenFalseNullable()) {
Log.d("DBINFO_AGFTN_STG${stage}","ID is ${a.id} NAME is ${a.name}")
}
}
fun logAllFromSQLite_Sequence(stage: Int) {
for(ss in dao.getAllFromSQLiteSequence()) {
Log.d("DBINFO_SSEQ_STG${stage}","TABLE IS ${ss.name} HIGHEST ID STORED FOR THE TABLE IS ${ss.seq}")
}
}
}
第一次运行应用程序时,输出为(阶段之间有2个空行,3个表之间有一个空行):-
2023-04-14 12:01:26.073 D/DBINFO_STARTSTAGE_0: Starting logging of stage 0
2023-04-14 12:01:26.244 D/DBINFO_STARTSTAGE_1: Starting logging of stage 1
2023-04-14 12:01:26.246 D/DBINFO_AGTT_STG1: ID is 1 NAME is AG_TT_ZERO_1
2023-04-14 12:01:26.246 D/DBINFO_AGTT_STG1: ID is 2 NAME is AG_TT_DEFAULT_1
2023-04-14 12:01:26.246 D/DBINFO_AGTT_STG1: ID is 100 NAME is AG_TT_100_1
2023-04-14 12:01:26.246 D/DBINFO_AGTT_STG1: ID is 101 NAME is AG_TT_ZERO_2
2023-04-14 12:01:26.246 D/DBINFO_AGTT_STG1: ID is 102 NAME is AG_TT_DEFAULT_2
2023-04-14 12:01:26.247 D/DBINFO_AGTT_STG1: ID is 103 NAME is AG_TT_ZERO_3
2023-04-14 12:01:26.247 D/DBINFO_AGTT_STG1: ID is 104 NAME is AG_TT_DEFAULT_3
2023-04-14 12:01:26.249 D/DBINFO_AGFT_STG1: ID is 0 NAME is AG_FT_ZERO_1
2023-04-14 12:01:26.249 D/DBINFO_AGFT_STG1: ID is 100 NAME is AG_FT_100_1
2023-04-14 12:01:26.250 D/DBINFO_AGFTN_STG1: ID is 0 NAME is AG_FTNULL_ZERO_1
2023-04-14 12:01:26.250 D/DBINFO_AGFTN_STG1: ID is 1 NAME is AG_FTNULL_NULL_1
2023-04-14 12:01:26.250 D/DBINFO_AGFTN_STG1: ID is 2 NAME is AG_FTNULL_DEFAULT_1
2023-04-14 12:01:26.250 D/DBINFO_AGFTN_STG1: ID is 100 NAME is AG_FTNULL_100_1
2023-04-14 12:01:26.251 D/DBINFO_AGFTN_STG1: ID is 101 NAME is AG_FTNULL_NULL_2
2023-04-14 12:01:26.251 D/DBINFO_AGFTN_STG1: ID is 102 NAME is AG_FTNULL_DEFAULT_2
2023-04-14 12:01:26.251 D/DBINFO_AGFTN_STG1: ID is 103 NAME is AG_FTNULL_NULL_3
2023-04-14 12:01:26.251 D/DBINFO_AGFTN_STG1: ID is 104 NAME is AG_FTNULL_DEFAULT_3
2023-04-14 12:01:26.253 D/DBINFO_SSEQ_STG1: TABLE IS AutoGenTrueTable HIGHEST ID STORED FOR THE TABLE IS 104
2023-04-14 12:01:26.258 D/DBINFO_STARTSTAGE_2: Starting logging of stage 2
2023-04-14 12:01:26.261 D/DBINFO_AGTT_STG2: ID is 1 NAME is AG_TT_ZERO_1
2023-04-14 12:01:26.261 D/DBINFO_AGTT_STG2: ID is 2 NAME is AG_TT_DEFAULT_1
2023-04-14 12:01:26.262 D/DBINFO_AGFT_STG2: ID is 0 NAME is AG_FT_ZERO_1
2023-04-14 12:01:26.263 D/DBINFO_AGFTN_STG2: ID is 0 NAME is AG_FTNULL_ZERO_1
2023-04-14 12:01:26.263 D/DBINFO_AGFTN_STG2: ID is 1 NAME is AG_FTNULL_NULL_1
2023-04-14 12:01:26.263 D/DBINFO_AGFTN_STG2: ID is 2 NAME is AG_FTNULL_DEFAULT_1
2023-04-14 12:01:26.264 D/DBINFO_SSEQ_STG2: TABLE IS AutoGenTrueTable HIGHEST ID STORED FOR THE TABLE IS 104
2023-04-14 12:01:26.333 D/DBINFO_STARTSTAGE_3: Starting logging of stage 3
2023-04-14 12:01:26.336 D/DBINFO_AGTT_STG3: ID is 1 NAME is AG_TT_ZERO_1
2023-04-14 12:01:26.336 D/DBINFO_AGTT_STG3: ID is 2 NAME is AG_TT_DEFAULT_1
2023-04-14 12:01:26.337 D/DBINFO_AGTT_STG3: ID is 100 NAME is AG_TT_100_1
2023-04-14 12:01:26.337 D/DBINFO_AGTT_STG3: ID is 105 NAME is AG_TT_ZERO_1
2023-04-14 12:01:26.337 D/DBINFO_AGTT_STG3: ID is 106 NAME is AG_TT_DEFAULT_1
2023-04-14 12:01:26.337 D/DBINFO_AGTT_STG3: ID is 107 NAME is AG_TT_ZERO_2
2023-04-14 12:01:26.337 D/DBINFO_AGTT_STG3: ID is 108 NAME is AG_TT_DEFAULT_2
2023-04-14 12:01:26.337 D/DBINFO_AGTT_STG3: ID is 109 NAME is AG_TT_ZERO_3
2023-04-14 12:01:26.337 D/DBINFO_AGTT_STG3: ID is 110 NAME is AG_TT_DEFAULT_3
2023-04-14 12:01:26.340 D/DBINFO_AGFT_STG3: ID is 0 NAME is AG_FT_ZERO_1
2023-04-14 12:01:26.340 D/DBINFO_AGFT_STG3: ID is 100 NAME is AG_FT_100_1
2023-04-14 12:01:26.342 D/DBINFO_AGFTN_STG3: ID is 0 NAME is AG_FTNULL_ZERO_1
2023-04-14 12:01:26.342 D/DBINFO_AGFTN_STG3: ID is 1 NAME is AG_FTNULL_NULL_1
2023-04-14 12:01:26.342 D/DBINFO_AGFTN_STG3: ID is 2 NAME is AG_FTNULL_DEFAULT_1
2023-04-14 12:01:26.342 D/DBINFO_AGFTN_STG3: ID is 3 NAME is AG_FTNULL_NULL_1
2023-04-14 12:01:26.343 D/DBINFO_AGFTN_STG3: ID is 4 NAME is AG_FTNULL_DEFAULT_1
2023-04-14 12:01:26.343 D/DBINFO_AGFTN_STG3: ID is 100 NAME is AG_FTNULL_100_1
2023-04-14 12:01:26.343 D/DBINFO_AGFTN_STG3: ID is 101 NAME is AG_FTNULL_NULL_2
2023-04-14 12:01:26.343 D/DBINFO_AGFTN_STG3: ID is 102 NAME is AG_FTNULL_DEFAULT_2
2023-04-14 12:01:26.343 D/DBINFO_AGFTN_STG3: ID is 103 NAME is AG_FTNULL_NULL_3
2023-04-14 12:01:26.343 D/DBINFO_AGFTN_STG3: ID is 104 NAME is AG_FTNULL_DEFAULT_3
2023-04-14 12:01:26.346 D/DBINFO_SSEQ_STG3: TABLE IS AutoGenTrueTable HIGHEST ID STORED FOR THE TABLE IS 110
The Results explained(有点)
可以清楚地看到,id不是重复的(它们不可能在所有情况下都是唯一的,因为主键在所有情况下都是隐式唯一的),而且SQLITE_SEQUENCE只记录TT表使用过的最高id(带有AutoGENERATE=TRUE的那个).
不那么容易看出的是,在AutoGenerate为FALSE的情况下,至少是IMPLICATA ION/DEFAULT,使用超过一次的任何值(包括0)都不会生成id,即Room将该值传递给INSERT.该演示具有忽略onConflictStrategy
,因此这些try 的重复将被忽略,并且不会发生故障.
因此,FT和FTNULL的??_ZERO_nn的id为0,并且只有nn为1的单行.与TT_ZERO不同,在TT_ZERO中插入的所有3行的生成ID都不为0.