Room is applied in one-to-many relationships in JetPack

Section 1: Data formats

This article does three things:

How to design a database and insert data into it.
How to query data.
How to delete data.

The data format used in the example is as follows:

{
  "programmeList": [{"programmeId": 23."adList": [{"adName": "Brightness Test 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": "Fifty - fifty"."runningTime": 20."showEndTime": "19:32:00"
        },
        {
          "adName": "Brightness Test 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"]}Copy the code

Section 2: Design the Bean and Entity classes to use

Json to Kotlin Class generates the following Bean L classes:

ProgrammeListBean.kt

data class ProgrammeListBean(
    val programmeList: List<Programme>,
    val sourceList: List<String>
)

Copy the code

Programme.kt

data class Programme(
    val adList: List<Ad>,
    val programmeId: Int.val programmeName: String,
    val showDate: String
)
Copy the code

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
)
Copy the code

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
)
Copy the code

The Bean class has the List data type, but the data is not stored in this way, only row by row. So remove the List data from the Entity class. This is not to say that there is no way to store List data. For example, you can use @typeconverter to define a conversion class that converts List data into an Array Json string and then reverses the Json string into List data. Kind of like serialization and deserialization. Here is an example of this method:

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)}Copy the code

Then use it in the appDatabase.kt class:

@Database( entities = [AdEntity::class, AdSourceEntity::class, ProgrammeEntity::class], version = 1, exportSchema = false )
@TypeConverters(value = [TypeConverter::class)
abstract class AppDataBase : RoomDatabase() {... }Copy the code

Instead of using this method, this article uses the @relation annotation. So if you look at this how do you use Room for one-to-one, one-to-many, many-to-many relationships?

Next, we design the Entity class, which basically removes the fields associated with the List type

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.// This field will be used in @Relation
    val programmeOwnerId: Int.val aspectRatio: String,
    val runningTime: Int.val showEndTime: String,
    val showStartTime: String
)
Copy the code

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
)
Copy the code

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
)
Copy the code

Section 3: Insert data

Step 1: Define the Dao class

All manipulation of Room data is done in the @DAO annotated classes. Create a new AdDao class to complete the operation on AdEntity.

@Dao
interface AdDao {
    @Query("SELECT * FROM ad_table")
    fun getAd(a): 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>)
}
Copy the code

Here we add some basic operations, query, delete, insert.

AdSourceDao.kt

@Dao
interface AdSourceDao {

    @Query("SELECT * FROM ad_source_table")
    fun getAdSource(a): 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>)
}
Copy the code

ProgrammeDao.kt

/** * The Data Access Object for the Plant class. */
@Dao
interface ProgrammeDao {
    @Query("SELECT * FROM programme ORDER BY programmeName")
    fun getProgramme(a): 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)

}
Copy the code

Step 2: Insert data

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 {
                    // Use transactions
                    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)
                        }
                    }
                }
            }
        }
       
Copy the code

The withTransaction function is used to run a transaction, so that the integrity of the data is guaranteed, and the data is not inconsistent because some parts of the data were successfully inserted into the database and some parts failed.

Section 4: Querying data

In our data format, one Ad corresponds to multiple ADsources.

Step 1: Define a composite class to combine AdEntity with List

, which is the inverse of the operation in Section 2.

AdAndAdSource.kt

data class AdAndAdSource(
    @Embedded
    val ad: AdEntity,

    @Relation(parentColumn = "adId", entityColumn = "adOwnerId")
    val sourceEntities: List<AdSourceEntity>
)

Copy the code

Through observation, it can be found that Relation is associated with two fields, parentColumn = “adId” and entityColumn = “adOwnerId” respectively.

Step 2: Add the appropriate query operations to the AdDao. Modify the AdDao class as follows:

AdDao.kt

@Dao
interface AdDao {
    @Query("SELECT * FROM ad_table")
    fun getAd(a): Flow<List<AdEntity>>

    @Query("SELECT EXISTS(SELECT 1 FROM ad_source_table WHERE adCss = :adId LIMIT 1)")
    fun isAdSourceExists(adId: String): Flow<Boolean>

    // Make sure you add it, otherwise you'll get a warning.
    @Transaction
    @Query("SELECT * FROM ad_table")
    fun getAdAnAdSources(a): Flow<List<AdAndAdSource>>
    
    @Delete
    suspend fun deleteAd(ad: AdEntity)
    
    @Insert(onConflict = OnConflictStrategy.REPLACE)
    suspend fun insertAllAd(ads: List<AdEntity>)
}
Copy the code

If @Transaction is not added, the following WARN will appear

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.

If you do not use @Transaction, you may cause data in the database to be inconsistent with POJO data in memory.

Of course, you can also query part of the data

    @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>
Copy the code

Step 3: Query the data

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

                       // Method 1:
// 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))
/ /}
/ /}

                       // Method 2: There is an error, will only execute once, temporarily do not understand why
// 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))
/ /}
/ /}

                       / / method 3
                       (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))
                           }
                   }
               }
           }
       }
Copy the code

Section 4: Deleting data

Delete To do this, delete a Programme object and all other data associated with it must also be deleted. Here you can use foreignKeys. In fact, I have not studied foreignKeys systematically. A brief introduction:

SQL foreign key constraints are used to enforce "exists" relationships between tables.

Foreign keys are used to enforce constraints 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)
);
Copy the code

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.

The parent table is the table referenced by the FOREIGN KEY constraint, that is, FOREIGN KEY(TrackArtist) REFERENCES ARTIST (Artistid) REFERENCES, in this case the ARTIST table.

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) REFERENCES Artist (Artistid) REFERENCES Artist (Artistid)

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.

The fields in the parent table are the parent key, and the fields in the child table are the child key

When do these constraints apply? For example, if the parent table deletes an item, what does the child table need to do? This can be done using foreign keys.

Let’s look at the use of Room foreign keys:

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
)
Copy the code

Http://dict.vetuenglish.com (http://dict.vetuenglish.com) Http://dict.vetuenglish.com (http://dict.vetuenglish.com) http://dict.vetuenglish.com (http://dict.vetuenglish.com) http://dict.vetuenglish.com (http://dict.vetuenglish.com) So childColumns is undoubtedly the field in the table, namely programmeOwnerId, in which entity corresponds to the entity representing the parent table, onDelete = ForeignKey.CASCADE, which is the most important. It defines the action of the child table when the parent table deletes a row.

"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.Copy the code

Level linkage For the deletion action, when the parent Entity is deleted, all associated child Entities are also deleted. In this case, when a line of Programme is deleted, all Ad associated with it is deleted.

There’s an error here, and if you compile now, you’ll get a warning.

Warning: 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.

The programmeOwnerId field references a foreign key, but is not part of the index. This may cause the child table to perform a full table search when the parent table is modified. So I add index here, modify it as follows:

@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
)
Copy the code

Compile again, warning cleared. Also add a foreign key to the AdSourceEntity table:

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
)
Copy the code

Add delete statement to programmedao.kt:

    @RawQuery
    suspend fun deleteProgrammeById(supportSQLiteQuery: SupportSQLiteQuery): Long
Copy the code

Finally delete:

MainActivity.kt

    fun delete(v: View) {
            val supportSQLiteQuery =
                SimpleSQLiteQuery("DELETE FROM programme WHERE programmeId = ?", arrayOf(23))
            GlobalScope.launch {
                programmeDao.deleteProgrammeById(supportSQLiteQuery)
            }
        }
Copy the code

Because Room is a main-Safe cooutine library, there is no need to switch IO threads. The following is the data before the deletion:

Here is the result of calling the delete function:

You can see that all associated data has been deleted.

Finally, a little tip. Sometimes the compiler gets an error during a Build (when Room Kapt doesn’t generate code smoothly) and gives a very succinct prompt that doesn’t help you find the error. In this case, you can follow the following figure to see the detailed error message:

Click on the root project.

The Demo is attached below

Room is applied in one-to-many relationships in JetPack

Section 1: Data formats

Section 2: Design the Bean and Entity classes to use

Section 3: Insert data

Section 4: Querying data

Section 4: Deleting data

Related Posts

Android development: “When the tuyere is over, the pig will fall down”.

Use Lambda in your project!

Kotlin coroutine theory (1)