第一节：数据格式
这篇文章做到三个事：
- 如何设计数据库，把数据插入到数据库中。
- 如何查询数据。
- 如何删除数据。
示例中使用到的数据格式如下：
{
"programmeList": [
{
"programmeId": 23,
"adList": [
{
"adName": "亮亮测试2",
"adId": 80,
"adPageType": 2,
"adSourceList": [
{
"adSourceType": 1,
"switchingEffects": 1,
"adCss": "{\"loop\":true,\"direction\":\"horizontal\",\"autoplay\":{\"delay\":5000}}",
"adSourceSort": 0,
"sourceRunningTime": 5,
"pageNum": 1,
"adSourceInfo": "http://qiniu.signcc.com/2YNRqUAxxTxxxxxxi36m.jpg;http://qiniu.signcc.com/3OvTJxxxxtx3G1xjlC7ucGKF.jfif;http://qiniu.signcc.com/4dFHaME3zixxxiOOV69K.jpg",
"adVideoType": 2
},
{
"adSourceType": 2,
"switchingEffects": 1,
"adCss": "{}",
"adSourceSort": 0,
"sourceRunningTime": null,
"pageNum": 2,
"adSourceInfo": "http://qiniu.signcc.com/4o4uUUS7sfpxxxxcoI2Mk.MP4",
"adVideoType": 2
}
],
"showStartTime": "18:32:00",
"aspectRatio": "50-50",
"runningTime": 20,
"showEndTime": "19:32:00"
},
{
"adName": "亮亮测试1",
"adId": 79,
"adPageType": 1,
"adSourceList": [
{
"adSourceType": 2,
"switchingEffects": 1,
"adCss": "{}",
"adSourceSort": 0,
"sourceRunningTime": null,
"pageNum": 1,
"adSourceInfo": "http://qiniu.signcc.com/53TZu5xxxxoQpvQ7R3VY.mp4",
"adVideoType": 2
}
],
"showStartTime": "18:32:00",
"aspectRatio": "100",
"runningTime": 20,
"showEndTime": "19:32:00"
}
],
"programmeName": "liangliangTest",
"showDate": "2021-04-02"
}
],
"sourceList": [
"http://qiniu.signcc.com/53TZu5olUNrQoQpvQ7R3VY.mp4",
"http://qiniu.signcc.com/2YNRqxxxxx55Aii36m.jpg;http://qiniu.signcc.com/3OvTJCxxxxlC7ucGKF.jfif;http://qiniu.signcc.com/4dFHaMxxxxBthoiOOV69K.jpg",
"http://qiniu.signcc.com/4o4uUUxxxxScoI2Mk.MP4"
]
}
第二节：设计要使用的 Bean 类和 Entity 类
这里使用插件 Json to Kotlin Class 来完成 Bean 的生成
会生成下面几个 Bean l类：
ProgrammeListBean.kt
data class ProgrammeListBean(
val programmeList: List<Programme>,
val sourceList: List<String>
)
Programme.kt
data class Programme(
val adList: List<Ad>,
val programmeId: Int,
val programmeName: String,
val showDate: String
)
AdSource.kt
data class AdSource(
val adCss: String,
val adSourceInfo: String,
val adSourceSort: Int,
val adSourceType: Int,
val adVideoType: Int,
val pageNum: Int,
val sourceRunningTime: Int,
val switchingEffects: Int
)
Ad.kt
data class Ad(
val adId: Int,
val adName: String,
val adPageType: Int,
val adSourceList: List<AdSource>,
val aspectRatio: String,
val runningTime: Int,
val showEndTime: String,
val showStartTime: String
)
Bean 类中有 List 数据类型，但是在数据中，是没有以这种方式来存储，只能一行行来存储数据。所以 Entity 类中把这些 List 类型的数据给去掉。
当然也不是说完全没有办法来存储 List 类型的数据，比如可以通过 @TypeConverter 自定义一个转化类，把 List 类型数据转成成一个 Array Json 字符串，然后再把 Json 字符串再逆转换成 List 类型数据。有点像序列化和反序列化。下面是该方法的一个示例：
open class TypeConverter {
@TypeConverter
fun json2StatsEntity(src: String): List<ProgrammeEntity>? =
GsonBuilder().create().fromJson(src)
@TypeConverter
fun statsEntity2Json(data: List<ProgrammeEntity): String =
GsonBuilder().create().typedToJson(data)
}
然后在 AppDataBase.kt 类中使用：
@Database(
entities = [AdEntity::class, AdSourceEntity::class, ProgrammeEntity::class],
version = 1, exportSchema = false
)
@TypeConverters(value = [TypeConverter::class)
abstract class AppDataBase : RoomDatabase() {
...
}
本文不使用该方法，而是利用 @Relation 注释来完成。
这里可以参考下这个 如何用 Room 处理一对一，一对多，多对多关系？
接下来设计 Entity 类，基本上就是把 List 类型相关的字段去掉
AdEntity.kt
fun covertAdToEntity(ad: Ad, programmeId: Int) = ad.let {
AdEntity(
it.adId,
it.adName,
it.adPageType,
programmeId,
it.aspectRatio,
it.runningTime,
it.showEndTime,
it.showStartTime
)
}
@Entity(tableName = "ad_table")
data class AdEntity(
@PrimaryKey val adId: Int,
val adName: String,
val adPageType: Int,
// 这个字段在 @Relation 会被使用到
val programmeOwnerId: Int,
val aspectRatio: String,
val runningTime: Int,
val showEndTime: String,
val showStartTime: String
)
AdSourceEntity.kt
fun covertAdSourceToEntity(adSource: AdSource, adId: Int) = adSource.let {
AdSourceEntity(
adId,
it.adCss,
it.adSourceInfo,
it.adSourceSort,
it.adSourceType,
it.adVideoType,
it.pageNum,
it.sourceRunningTime,
it.switchingEffects
)
}
@Entity(
tableName = "ad_source_table", primaryKeys = ["adSourceInfo", "adOwnerId"]
)
data class AdSourceEntity(
val adOwnerId: Int,
val adCss: String,
val adSourceInfo: String,
val adSourceSort: Int,
val adSourceType: Int,
val adVideoType: Int,
val pageNum: Int,
val sourceRunningTime: Int,
val switchingEffects: Int
)
ProgrammeEntity.kt
fun covertProgrammeToEntity(programme: Programme): ProgrammeEntity =
programme.let {
ProgrammeEntity(
it.programmeId,
it.programmeName,
it.showDate
)
}
@Entity(tableName = "programme")
data class ProgrammeEntity(
@PrimaryKey val programmeId: Int,
val programmeName: String,
val showDate: String
)
第三节：插入数据
第一步：定义 Dao 类
Room 中数据的操作都是在 @Dao 注释的类中完成的。这里新建一个 AdDao 类来完成对 AdEntity 的操作。
@Dao
interface AdDao {
@Query("SELECT * FROM ad_table")
fun getAd(): Flow<List<AdEntity>>
@Query("SELECT EXISTS(SELECT 1 FROM ad_source_table WHERE adCss = :adId LIMIT 1)")
fun isAdSourceExists(adId: String): Flow<Boolean>
@Delete
suspend fun deleteAd(ad: AdEntity)
@Insert(onConflict = OnConflictStrategy.REPLACE)
suspend fun insertAllAd(ads: List<AdEntity>)
}
这里先添加了一些基本的操作，查询，删除，插入动作。
AdSourceDao.kt
@Dao
interface AdSourceDao {
@Query("SELECT * FROM ad_source_table")
fun getAdSource(): Flow<List<AdSourceEntity>>
@Query("SELECT * FROM ad_source_table WHERE adOwnerId = :adId")
fun getAdSourceById(adId: Int): Flow<List<AdSourceEntity>>
@Insert(onConflict = OnConflictStrategy.REPLACE)
fun insertAllAdSource(adSourceEntities:List<AdSourceEntity>)
}
ProgrammeDao.kt
/**
* The Data Access Object for the Plant class.
*/
@Dao
interface ProgrammeDao {
@Query("SELECT * FROM programme ORDER BY programmeName")
fun getProgramme(): Flow<List<ProgrammeEntity>>
@Query("SELECT * FROM programme WHERE programmeId = :programmeId ORDER BY programmeName")
fun getProgrammeById(programmeId: Int): Flow<List<ProgrammeEntity>>
@Insert(onConflict = OnConflictStrategy.REPLACE)
suspend fun insertAll(plants: List<ProgrammeEntity>)
@Insert(onConflict = OnConflictStrategy.REPLACE)
suspend fun insertProgramme(programmeEntity: ProgrammeEntity)
}
第二步：插入数据
MainActivity.kt
fun insert(v: View) {
val programmeListBean = gson.fromJson(
getJson("ProgrammeList.json", application),
ProgrammeListBean::class.java
)
val programmeList = programmeListBean.programmeList
for (programme in programmeList) {
GlobalScope.launch {
// 使用事务
appDatabase.withTransaction {
val adList = programme.adList
val programmeId = programme.programmeId
programmeDao.insertProgramme(covertProgrammeToEntity(programme))
val adEntities = adList.map {
covertAdToEntity(it, programmeId)
}.toList()
adDao.insertAllAd(adEntities)
for (ad in adList) {
val adId = ad.adId
val adSourceEntities = ad.adSourceList.map {
covertAdSourceToEntity(it, adId)
}.toList()
adSourceDao.insertAllAdSource(adSourceEntities)
}
}
}
}
}
这里使用 withTransaction 函数来跑一个事务，这样就可以保证数据插入的完整性，不会因为某部分数据插入数据库成功，而某部分又失败而造成数据不一致。
第四节：查询数据
在我们数据格式中，一个 Ad 是对应多个 AdSource 的关系。
第一步：定义一个组合类，用于把 AdEntity 和 List<AdSource> 组合起来，相当于是第二节里面的逆操作。
AdAndAdSource.kt
data class AdAndAdSource(
@Embedded
val ad: AdEntity,
@Relation(parentColumn = "adId", entityColumn = "adOwnerId")
val sourceEntities: List<AdSourceEntity>
)
通过观察可以发现 Relation 是通过各自的两个字段 parentColumn = "adId", entityColumn = "adOwnerId" 来进行关联的。
第二步：在 AdDao 添加相应的查询操作。修改 AdDao 类，修改后如下：
AdDao.kt
@Dao
interface AdDao {
@Query("SELECT * FROM ad_table")
fun getAd(): Flow<List<AdEntity>>
@Query("SELECT EXISTS(SELECT 1 FROM ad_source_table WHERE adCss = :adId LIMIT 1)")
fun isAdSourceExists(adId: String): Flow<Boolean>
// 一定要加上，不然会报警告。
@Transaction
@Query("SELECT * FROM ad_table")
fun getAdAnAdSources(): Flow<List<AdAndAdSource>>
@Delete
suspend fun deleteAd(ad: AdEntity)
@Insert(onConflict = OnConflictStrategy.REPLACE)
suspend fun insertAllAd(ads: List<AdEntity>)
}
如果没有加 @Transaction 就会出现下面的报警（WARN)
The return value includes a POJO with a @Relation. It is usually desired to annotate this method with @Transaction to avoid possibility of inconsistent results between the POJO and its relations.
直译过来就是：如果不使用 @Transaction 就可能会造成数据库中的数据与内存中的 POJO 数据不一致。
当然也可以查询部分数据
@Transaction
@Query("SELECT * FROM ad_table WHERE adId IN (:adIds)")
fun getAdAnAdSourcesByIds(adIds: List<Int>): Flow<List<AdAndAdSource>>
@Transaction
@Query("SELECT * FROM ad_table WHERE adId =:adId")
fun getAdAnAdSourcesById(adId: Int): Flow<AdAndAdSource>
第三步：查询数据
MainActivity.kt
fun query(v: View) {
GlobalScope.launch {
// adDao.getAdAnAdSources().collectLatest {
// Log.i("liang", gson.toJson(it))
// }
programmeDao.getProgrammeAndAd().collectLatest {
for (programmeAndAd in it) {
Log.i("liang", gson.toJson(programmeAndAd))
Log.i("liang", "adEntities size: ${programmeAndAd.adEntities.size}")
// 方法一：
// val adIds = programmeAndAd.adEntities.map {ad->
// Log.i("liang", "ad id: ${ad.adId}")
// ad.adId
// }.toList()
// adDao.getAdAnAdSourcesById(adIds).collect { v->
// for(adAndAdSource in v) {
// Log.i("liang", gson.toJson(adAndAdSource))
// }
// }
// 方法二：有错误，只会执行一次，暂时不明白为什么
// for (ad in programmeAndAd.adEntities) {
// Log.i("liang", "ad id: ${ad.adId}")
// adDao.getAdAnAdSourcesById(ad.adId).apply {
// Log.i("liang", "Flow obj: ${this.toString()}")
// }.collect { v ->
// Log.i("liang", gson.toJson(v))
// }
// }
// 方法三
(programmeAndAd.adEntities.indices).asFlow()
.transform { index ->
val adId = programmeAndAd.adEntities[index].adId
emit(adDao.getAdAnAdSourcesById(adId).first())
}
.collect { v ->
Log.i("liang", gson.toJson(v))
}
}
}
}
}
第四节：删除数据
删除要做到，删除某一个 Programme 对象，其关联的其他数据也要全部被删除。这里就可以使用到 foreignKeys，其实我一直对外键没有个系统的学习。
简单介绍下：
SQL foreign key constraints are used to enforce "exists" relationships between tables.
外键就是用来加强在表之间的约束。
CREATE TABLE artist(
artistid INTEGER PRIMARY KEY,
artistname TEXT
);
CREATE TABLE track(
trackid INTEGER,
trackname TEXT,
trackartist INTEGER
FOREIGN KEY(trackartist) REFERENCES artist(artistid)
);
SQLite uses the following terminology（术语）:
The parent table is the table that a foreign key constraint refers to. The parent table in the example in this section is the artist table. Some books and articles refer to this as the referenced table, which is arguably more correct, but tends to lead to confusion.
父表就是外键约束引用的表，也就是 FOREIGN KEY(trackartist) REFERENCES artist(artistid)
REFERENCES 后面跟的表，在这里就是 artist 表。
The child table is the table that a foreign key constraint is applied to and the table that contains the REFERENCES clause. The example in this section uses the track table as the child table. Other books and articles refer to this as the referencing table.
子表就是外键约束定义所在的表，也就是 FOREIGN KEY(trackartist) REFERENCES artist(artistid) 这句在那个表加上的，这个表就是子表，这里指的是 track 表。
The parent key is the column or set of columns in the parent table that the foreign key constraint refers to. This is normally, but not always, the primary key of the parent table. The parent key must be a named column or columns in the parent table, not the rowid.
The child key is the column or set of columns in the child table that are constrained by the foreign key constraint and which hold the REFERENCES clause.
这些 key 就简单了，在父表的字段就是 parent key，在子表里面的字段就是 child key
这些约束在什么时候用的上呢？比如现在想要做得删除动作，当父表删除一项后，子表需要做什么处理呢？这些就可以使用外键来实现。
再来看 Room 中外键的使用：
AdEntity.kt
@Entity(tableName = "ad_table", foreignKeys = [
ForeignKey(
entity = ProgrammeEntity::class,
parentColumns = ["programmeId"],
childColumns = ["programmeOwnerId"],
onDelete = ForeignKey.CASCADE
)
])
data class AdEntity(
@PrimaryKey val adId: Int,
val adName: String,
val adPageType: Int,
val programmeOwnerId: Int,
val aspectRatio: String,
val runningTime: Int,
val showEndTime: String,
val showStartTime: String
)
套用上面的术语（terminology），可以看到 foreignKeys 实在 AdEntity 类里面定义的，那么 AdEntity 对应的表 ad_table 就是子表喽。那么 childColumns 毫无疑问的就是这个表里面的字段了，也就是 programmeOwnerId，其中 entity 对应的代表父表的 Entity，onDelete = ForeignKey.CASCADE，这个最重要了，它定义了父表删除一行后，子表的所对应的动作。
"CASCADE" action propagates the delete or update operation on the parent key to each
dependent child key. For onDelete() action, this means that each row in the child
entity that was associated with the deleted parent row is also deleted. For an
onUpdate() action, it means that the values stored in each dependent child key are
modified to match the new parent key values.
级联动作对于删除动作，在父 Entity 被删除的时候，所有相关联的子 Entity 也被删除。在这里，就是删除一行 Programme 的时候，把相关联的 Ad 全部删除掉。
这里留了一个错误，如果现在编译，就会得到一个警告。
警告: programmeOwnerId column references a foreign key but it is not part of an index. This may trigger full table scans whenever parent table is modified so you are highly advised to create an index that covers this column.
意思就是 programmeOwnerId 这个字段引用了一个外键，但是却不是 index 的一部分，这可能会造成，当父表被修改的时候，子表会进行全表搜索，也就是效率不好，如果数据不多问题也不大。
那么我这里就加上 index，修改后如下：
@Entity(
tableName = "ad_table", foreignKeys = [
ForeignKey(
entity = ProgrammeEntity::class,
parentColumns = ["programmeId"],
childColumns = ["programmeOwnerId"],
onDelete = ForeignKey.CASCADE
)
], indices = [Index("programmeOwnerId")]
)
data class AdEntity(
@PrimaryKey val adId: Int,
val adName: String,
val adPageType: Int,
val programmeOwnerId: Int,
val aspectRatio: String,
val runningTime: Int,
val showEndTime: String,
val showStartTime: String
)
再次编译，警告消除。 同样的对于 AdSourceEntity 表也加上外键：
AdSourceEntity.kt
@Entity(
tableName = "ad_source_table", primaryKeys = ["adSourceInfo", "adOwnerId"],
foreignKeys = [
ForeignKey(
entity = AdEntity::class,
parentColumns = ["adId"],
childColumns = ["adOwnerId"],
onDelete = ForeignKey.CASCADE
)
],
indices = [Index("adOwnerId"), Index("adSourceInfo")]
)
data class AdSourceEntity(
val adOwnerId: Int,
val adCss: String,
val adSourceInfo: String,
val adSourceSort: Int,
val adSourceType: Int,
val adVideoType: Int,
val pageNum: Int,
val sourceRunningTime: Int,
val switchingEffects: Int
)
在 ProgrammeDao.kt 加上删除语句：
@RawQuery
suspend fun deleteProgrammeById(supportSQLiteQuery: SupportSQLiteQuery): Long
最后执行删除操作：
MainActivity.kt
fun delete(v: View) {
val supportSQLiteQuery =
SimpleSQLiteQuery("DELETE FROM programme WHERE programmeId = ?", arrayOf(23))
GlobalScope.launch {
programmeDao.deleteProgrammeById(supportSQLiteQuery)
}
}
因为 Room 是 Main-Safe 协程库，所以不需要有切换 IO 线程的动作。 下面是执行操作删除前的数据：
下面是调用 delete 函数后的结果：
可以看到相关联的数据全部删除了。
最后来个小技巧，有时候编译器在 Build 的时候出现错误（Room kapt 无法顺利生成代码时候），给出的是很简洁的提示，无法帮助查找错误。这时候可以按照下面的图来操作，就可以看到详细的错误提示：
也就是点击根项目就可以了。
下面附上 Demo
