ROOM database usage

The Room database has three important parts

  • @database: Define a Database, which tables must be included using the entities property. The version attribute indicates the version number of the database, which is used for database upgrade. Instantiation of the Dao is also defined in the class annotated by @database
  • @DAO: Define various apis for manipulating database tables (e.g., adding, deleting, modifying, and querying tables)
  • @Entity: Define a table, and each property of the Entity class represents each field of the table, unless you annotate it with @ignore

The @database annotation defines a Database

  • Define an abstract class that inherits RoomDatabase
  • Annotate the abstract class with @Database and specify the version number using the entities property configuration table
  • Defines a set of abstract methods for the Dao layer

Then, after building, the AppDataBase_Impl class that inherits AppDataBase is automatically generated and all the abstract methods are automatically implemented

@Database(entities = [User::class, Course::class, Teacher::class, UserJoinCourse::class, IDCard::class], version = 1)
abstract class AppDataBase : RoomDatabase() {
    abstract fun userDao(): UserDao
    abstract fun teacherDao(): TeacherDao
    abstract fun courseDao(): CourseDao
    abstract fun userJoinCourseDao(): UserJoinCourseDao
    abstract fun idCardDao(): IDCardDao
}
Copy the code

The @DAO annotation defines a database

  • Define an interface or abstract class and annotate the class with @DAO
  • Define abstract methods for manipulating various tables and use the corresponding abstract methods with annotations such as @Query

After you build it, the UserDao_Impl class that inherits the UserDao is automatically generated and all the abstract methods are automatically implemented

@Dao
abstract class UserDao {

    @Query("select * from tab_user")
    abstract fun getAll(): List<User>

    @Query("select * from tab_user where uid = :uid")
    abstract fun getById(uid: Long): User?

    @Insert(onConflict = OnConflictStrategy.REPLACE)
    abstract fun insert(vararg users: User): LongArray

    @Update
    abstract fun update(user: User)

    @Delete
    abstract fun delete(vararg users: User): Int
}
Copy the code

The @entity annotation defines the table

@entity (tableName = "tab_user") data class User(@columnInfo (name = "uid") // Define the column name @primaryKey (autoGenerate = true) // Identify the primary key and increment var uid: Long? , @columninfo // If no column name is specified, use the field name var username: String? Var age: Int = 0) // @columninfo is optional.Copy the code

Define the federated primary key

If you need to use multiple fields together as primaryKeys, you need to define the joint primary key using the primaryKeys attribute in the @entity annotation

@Entity(primaryKeys = arrayOf("firstName", "lastName")) data class User( val firstName: String? , val lastName: String? )Copy the code

Ignore a field

You can use the @ignore annotation

@Entity(primaryKeys = arrayOf("firstName", "lastName")) data class User( val firstName: String? , val lastName: String? , @Ignore val picture: Bitmap? )Copy the code

You can also use the ignoredColumns attribute in the @entity annotation

@Entity( primaryKeys = ["firstName", "lastName"], ignoredColumns = ["picture"] ) data class User( val firstName: String? , val lastName: String? , val picture: Bitmap? )Copy the code

Adding indexes

Add indexes using the indices property in the @Entity annotation

@Entity(indices = [Index(value = ["lastName", "address"])]) data class User( @PrimaryKey val id: Int, val firstName: String? , val address: String? , val lastName: String? )Copy the code

Define a foreign key association

You can define a foreign key association between two tables using the foreignKeys attribute in the @Entity annotation

@Entity(tableName = "tab_course") data class Course( @ColumnInfo(name = "cid") @PrimaryKey(autoGenerate = true) var cid:  Long? = null, @ColumnInfo var name: String ) @Entity(tableName = "tab_teacher", foreignKeys = [ForeignKey( entity = Course::class, childColumns = ["cid"], ParentColumns = ["cid"] // tab_course column name)], indices = [Index("cid")] ) data class Teacher( @ColumnInfo(name = "tid") @PrimaryKey(autoGenerate = true) var tid: Long? = null, var name: String, var cid: Long? = null )Copy the code

One-to-one relationship

Use foreign key associations + unique constraints to represent one-to-one associations

For example, a user can only have one ID card, and one ID card can only be owned by one user

@entity (tableName = "tab_user") data class user (@columnInfo (name = "uid") @primaryKey (autoGenerate = true) var uid: Long? , @ColumnInfo var username: String? , var age: Int = 0) @entity (tableName = "tab_ID_card ", foreignKeys = [ForeignKey(Entity = user ::class, parentColumns = ["uid"], childColumns = ["uid"] )], indices = [ Index("_uuid", unique = true), Index("uid", @primarykey (autoGenerate = true) var id: Long? , @ColumnInfo(name = "_uuid") var uuid: String, var startTime: String, var expireTime: String, @ColumnInfo(name = "uid") var userId: Long? )Copy the code

It is recommended that you specify an idCard field in the user table annotated with @ignore, for example:

@Entity(tableName = "tab_user") data class User( @ColumnInfo(name = "uid") @PrimaryKey(autoGenerate = true) var uid: Long? , @ColumnInfo var username: String? , var age: Int = 0) {@ignore var idCard: idCard? = null }Copy the code

The UserDao is then implemented as follows:

@Dao abstract class UserDao { @Query("select * from tab_user where uid = :uid") abstract fun getById(uid: Long): User? Fun getByIdWithIdCard(uid: Long): user? { val user = getById(uid) user? .let { it.idCard = AppDataBase.getInstance().idCardDao().getByForeignKey(it.uid!!) } return user } } @Dao abstract class IDCardDao { @Query("select * from tab_id_card where uid in (:uid)") abstract fun getByForeignKey(uid: Long): IDCard? . }Copy the code

One-to-many relationship

Use a foreign key association to represent a one-to-many association

For example, one teacher can only teach one subject, and one subject can be taught by more than one teacher

Table @entity (tableName = "tab_course") Data class Course(@columnInfo (name = "CID ") @primaryKey (autoGenerate = true) var cid: Long? @columninfo var name: String) {// Ignore var teachers: List<Teacher>? = null} // define table, @entity (tableName = "tab_teacher", foreignKeys = [ForeignKey(Entity = Course::class, childColumns = ["cid"], parentColumns = ["cid"] )], indices = [Index("cid")] ) data class Teacher( @ColumnInfo(name = "tid") @PrimaryKey(autoGenerate = true) var tid: Long? = null, var name: String, var cid: Long? = null) {@ignore var course: course? = null }Copy the code

Then, for ease of operation, the CourseDao and TeacherDao implementations are as follows:

@Dao abstract class CourseDao { @Query("select * from tab_course where cid = :cid") abstract fun getById(cid: Long): Course? fun getByIdWithTeacher(cid: Long): Course? { return getById(cid)? .apply { this.teachers = AppDataBase.getInstance().teacherDao().getByForeignKey(this.cid!!) this.teachers? .forEach { it.course = this } } } ... } @Dao abstract class TeacherDao { @Query("select * from tab_teacher where tid = :tid") abstract fun getById(tid: Long): Teacher? @Query("select * from tab_teacher where cid = :cid") abstract fun getByForeignKey(cid: Long): List<Teacher> ... }Copy the code

Many-to-many relationships

Many-to-many relationships require an intermediate table to represent them

For example, a user can learn multiple courses, and a course can be learned by multiple users

The intermediate table structure is as follows:

@Entity( tableName = "tab_user_join_course", indices = [Index(value = ["uid", "cid"], unique = true)], ForeignKey(entity = User::class, childColumns = ["uid"], parentColumns = ["uid"], OnDelete = foreignKey. CASCADE), // ForeignKey(entity = Course::class, childColumns = [" CID "], parentColumns = ["cid"]) ] ) data class UserJoinCourse( @PrimaryKey(autoGenerate = true) var id: Long? = null, @ColumnInfo(name = "uid") var uid: Long, @ColumnInfo(name = "cid") var cid: Long )Copy the code

The implementation of UserJoinCourseDao is as follows:

@Dao
interface UserJoinCourseDao {

    @Query("""
        select * from tab_user
        inner join tab_user_join_course on tab_user.uid = tab_user_join_course.uid
        where tab_user_join_course.cid = :cid
    """)
    fun getUsersByCourseId(cid: Long): List<User>

    @Query("""
        select * from tab_course
        inner join tab_user_join_course on tab_course.cid = tab_user_join_course.cid
        where tab_user_join_course.uid = :uid
    """)
    fun getCoursesByUserId(uid: Long): List<Course>

    @Insert
    fun insert(vararg userJoinCourses: UserJoinCourse)
}
Copy the code

@ function annotation

The @relation annotation is used to query table data to automatically query other associated data

The @relation annotation cannot be used in the Entity class of the @Entity annotation

The @relation annotation can only be used with one-to-many (the return value must be a set)

For example, define a CourseWithTeacher class

class CourseWithTeacher ( @Embedded var course: Course, @Relation(// entity label associated query table (optional), Default matching table entity = Teacher::class, // parentColumn indicates a field in the Course table (any field in the Course table) // entityColumn indicates a field in the Teacher table (any field in the Teacher table) // (example: SELECT 'tid', 'name', 'cid' FROM 'tab_teacher' WHERE :entityColumn in [:parentColumn]) parentColumn = "cid", entityColumn = "cid") var teachers: List<Teacher> )Copy the code

Then modify the CourseDao implementation

@Dao
abstract class CourseDao {
    @Query("select * from tab_course")
    abstract fun getAll(): List<CourseWithTeacher>
}
Copy the code

And associative deletion policy

When two tables are associated with foreign keys, for example, tab_user and TAB_ID_card tables implement one-to-one association with ForeignKey. When deleting a data in tab_USER table, if the data is associated with tab_ID_card, the deletion fails. Will quote android. Database. Sqlite. SQLiteConstraintException: FOREIGN KEY constraint failed error; At this time, you can add the onDelete attribute configuration and associative deletion policy to ForeignKey

  • Foreignkey. NO_ACTION: default policy. No processing is performed. If deleted data is associated, an error message is reported

  • Foreignkey.restrict: Has the same effect as NO_ACTION, but it checks the constraint first

  • Foreignkey. SET_NULL: When a data is deleted from the tab_user table, the UID value of the tab_ID_card table is set to NULL

  • Foreignkey. SET_DEFAULT: When a data is deleted from tab_user table, the uid value of the corresponding tab_ID_card table is set to the default value. However, because room cannot set the default value for column, it is still set to NULL

  • Foreignkey. CASCADE: When a data is deleted from tab_USER, the data corresponding to tab_ID_card is also deleted

    @Entity( tableName = "tab_id_card", foreignKeys = [ForeignKey( entity = User::class, parentColumns = ["uid"], childColumns = ["uid"], onDelete = ForeignKey.CASCADE )], indices = [ Index("_uuid", unique = true), Index("uid", @primarykey (autoGenerate = true) var id: Long? , @ColumnInfo(name = "_uuid") var uuid: String, var startTime: String, var expireTime: String, @ColumnInfo(name = "uid") var userId: Long? = null )Copy the code

The associated update policy is the same as the delete policy, but is configured through onUpdate

Database upgrade or degradation

The room database upgrade rules are as follows

For example, in the version iteration of app, version(version number) underwent changes of 1, 2, 3 and 4

Using Migration to configure update rules for each version, its constructors must specify startVersion and endVersion

The code implementation is as follows:

private fun createAppDataBase(context: Context): AppDataBase { return Room.databaseBuilder(context, AppDataBase::class.java, "db_example") .addMigrations(object : Migration(1, 2) {override Fun Migrate (database: SupportSQLiteDatabase) { Log.i("AppDataBase", "===Migration 1-2===") // do something } }).addMigrations(object : Migration(2, 3) {override Fun Migrate (database: SupportSQLiteDatabase) { Log.i("AppDataBase", "===Migration 2-3===") // do something } }) .addMigrations(object : Migration(3, 4) {override Fun Migrate (database: SupportSQLiteDatabase) { Log.i("AppDataBase", "===Migration 3-4===") // do something } }) .addMigrations(object : Migration(1, 3) {override Fun Migrate (database: SupportSQLiteDatabase) { Log.i("AppDataBase", "===Migration 1-3===") // do something } }).addMigrations(object : Migration(2, 4) {override Fun Migrate (database: SupportSQLiteDatabase) { Log.i("AppDataBase", "===Migration 2-4===") // do something } }) .build() }Copy the code
The current APP database version The latest app database version The upgrade rules Print the result
1 4 Upgrade from 1 to 3 and then from 3 to 4 ===Migration 1-3===

===Migration 3-4===
2 4 Upgrade directly from 2 to 4 ===Migration 2-4===
3 4 Upgrade from 3 to 4 ===Migration 3-4===
4 4 The same

The summary rule is as follows (for example, the current version == 1 and the latest version == 4) :

  • Start with the currentversionAs astartVersion, matches the largestendVersion(i.e. upgrade from 1 to 3)
  • And then match with the one aboveendVersionOne of the moststartVersionAnd match the largestendVersion(i.e., upgrade from 3 to 4)

What if the corresponding upgrade Migration configuration is not matched?

By default, if no upgrade policy is matched, the APP crashes

In order to prevent the crash, you can add fallbackToDestructiveMigration method configuration directly deleted all the tables, recreate the table

private fun createAppDataBase(context: Context): AppDataBase { return Room.databaseBuilder(context, AppDataBase::class.java, "db_example") .addMigrations(object : Migration(1, 2) {override Fun Migrate (database: SupportSQLiteDatabase) { Log.i("AppDataBase", "===Migration 1-2===") // do something } ... / / if there is no match to the Migration, the direct delete all of the tables, recreate the table. The fallbackToDestructiveMigration (). The build ()}Copy the code

Delete table rebuild with specified version number

The current version is 1 or 2, for example, to upgrade to the 4 is very troublesome, workload is too big, it is better to directly delete library reconstruction, this time can call fallbackToDestructiveMigrationFrom method specifies how the current version is the delete table reconstruction

private fun createAppDataBase(context: Context): AppDataBase { return Room.databaseBuilder(context, AppDataBase::class.java, "db_example") .addMigrations(object : Migration(3, 4) {override Fun Migrate (database: SupportSQLiteDatabase) {log. I ("AppDataBase", "===Migration 3-4===") // do something}}) Recreate the table. FallbackToDestructiveMigration () / / need to cooperate with fallbackToDestructiveMigration method is used, specify the current ` version ` is 1 or 2, I have deleted all the tables directly, Recreate the table. FallbackToDestructiveMigrationFrom (1, 2). The build ()}Copy the code

The room database degradation rules are as follows

For example, in the iteration process of app version, version(version number) has undergone changes of 1, 2, 3 and 4. Currently, it is 4 and needs to be downgraded to 1

private fun createAppDataBase(context: Context): AppDataBase { return Room.databaseBuilder(context, AppDataBase::class.java, "db_example") .addMigrations(object : Migration(4, 3) {override Fun Migrate (database: SupportSQLiteDatabase) { Log.i("AppDataBase", "===Migration 4-3===") // do something } }).addMigrations(object : Migration(3, 2) {override Fun Migrate (database: SupportSQLiteDatabase) { Log.i("AppDataBase", "===Migration 3-2===") // do something } }) .addMigrations(object : Migration(2, 1) {override Fun Migrate (database: SupportSQLiteDatabase) { Log.i("AppDataBase", "===Migration 2-1===") // do something } }) .addMigrations(object : Migration(4, 2) {override Fun Migrate (database: SupportSQLiteDatabase) { Log.i("AppDataBase", "===Migration 4-2===") // do something } }).addMigrations(object : Migration(3, 1) {override Fun Migrate (database: SupportSQLiteDatabase) { Log.i("AppDataBase", "===Migration 3-1===") // do something } }) .build() }Copy the code
The current APP database version Demote to APP database version The upgrade rules Print the result
4 1 You go from 4 to 2, and then from 2 to 1 ===Migration 4-2===

===Migration 2-1===
4 2 You go straight from 4 to 2 ===Migration 4-2===
4 3 We went from 4 to 3 ===Migration 4-3===
4 4 The same

The summary rules are as follows (for example, if version == 4 and version == 1 is degraded) :

  • Start with the currentversionAs astartVersion, matches the smallestendVersion(i.e. demoted from 4 to 2)
  • And then match with the one aboveendVersionOne of the moststartVersionAnd matches the smallestendVersion(i.e. demoted from 2 to 1 again)

Similarly, if no downgrade rule is matched, crash will also occur by default. Can delete table reconstruction through fallbackToDestructiveMigrationOnDowngrade method configuration, but can’t specify version delete table reconstruction

private fun createAppDataBase(context: Context): AppDataBase { return Room.databaseBuilder(context, AppDataBase::class.java, "db_example") .addMigrations(object : Migration(4, 3) {override Fun Migrate (database: SupportSQLiteDatabase) {log. I ("AppDataBase", "===Migration 4-3===") // do something}}) // If no demoted Migration is matched, The delete table reconstruction. FallbackToDestructiveMigrationOnDowngrade (). The build ()}Copy the code

Common methods for database upgrade

Upgrade from 1 to 2 and add a table tab_test

private fun createAppDataBase(context: Context): AppDataBase { return Room.databaseBuilder(context, AppDataBase::class.java, "db_example") .addMigrations(object : Migration(1, 2) {override Fun Migrate (database: SupportSQLiteDatabase) { Log.i("AppDataBase", "===Migration 1-2===") database.execSQL(""" CREATE TABLE IF NOT EXISTS `tab_test` ( `uid` INTEGER PRIMARY KEY AUTOINCREMENT, `username` TEXT, `age` INTEGER NOT NULL ) """.trimIndent()) } }) .fallbackToDestructiveMigration() .fallbackToDestructiveMigrationOnDowngrade() .build() }Copy the code

Upgrade from 2 to 3; add desc to tab_test

private fun createAppDataBase(context: Context): AppDataBase { return Room.databaseBuilder(context, AppDataBase::class.java, "db_example") .addMigrations(object : Migration(1, 2) {override Fun Migrate (database: SupportSQLiteDatabase) { Log.i("AppDataBase", "===Migration 1-2===") database.execSQL(""" CREATE TABLE IF NOT EXISTS `tab_test` ( `uid` INTEGER PRIMARY KEY AUTOINCREMENT, `username` TEXT, `age` INTEGER NOT NULL ) """.trimIndent()) } }) .addMigrations(object : Migration(2, 3) { override fun migrate(database: SupportSQLiteDatabase) { Log.i("AppDataBase", "===Migration 2-3===") database.execSQL("ALTER TABLE `tab_test` ADD COLUMN `desc` TEXT") } }) .fallbackToDestructiveMigration() .fallbackToDestructiveMigrationOnDowngrade() .build() }Copy the code

Upgrade from 3 to 4; rename tab_test desc to desc2

private fun createAppDataBase(context: Context): AppDataBase { return Room.databaseBuilder(context, AppDataBase::class.java, "db_example") .addMigrations(object : Migration(1, 2) {override Fun Migrate (database: SupportSQLiteDatabase) { Log.i("AppDataBase", "===Migration 1-2===") database.execSQL(""" CREATE TABLE IF NOT EXISTS `tab_test` ( `uid` INTEGER PRIMARY KEY AUTOINCREMENT, `username` TEXT, `age` INTEGER NOT NULL ) """.trimIndent()) } }) .addMigrations(object : Migration(2, 3) { override fun migrate(database: SupportSQLiteDatabase) { Log.i("AppDataBase", "===Migration 2-3===") database.execSQL("ALTER TABLE `tab_test` ADD COLUMN `desc` TEXT") } }) .addMigrations(object : Migration(3, 4) { override fun migrate(database: SupportSQLiteDatabase) { Log.i("AppDataBase", "===Migration 3-4===") // RENAME tmp_tab_test database. ExecSQL ("ALTER TABLE 'tab_test' RENAME TO 'tmp_tab_test'" ExecSQL (""" CREATE TABLE IF NOT EXISTS 'tab_test' (' uid 'INTEGER PRIMARY KEY AUTOINCREMENT, `username` TEXT, `age` INTEGER NOT NULL, TrimIndent ()) execSQL(" INSERT into 'tab_test' select * FROM 'tab_test' execSQL("insert into 'tab_test' select * from 'tab_test' ` tmp_tab_test ` ") / / delete tmp_tab_test table database. ExecSQL (" drop table ` tmp_tab_test ` ")}}) fallbackToDestructiveMigration ()  .fallbackToDestructiveMigrationOnDowngrade() .build() }Copy the code

Gradle rely on

Implementation "Androidx. room: room-Runtime :2.1.0" kapt "androidx.room:room-compiler:2.1.0" // For Kotlin use kapt instead of annotationProcessorCopy the code

Other Suggestions

Highly recommended usefacebook 的 stetho libraryCooperate with debugging your database

Select * from table_user where table_user = ‘tab_user’;

SELECT sql FROM sqlite_master WHERE name='tab_user';
Copy the code