Let us play in Room with Kotlin

Let us play in Room with Kotlin

Applications use different data sources, the most common one is a database. During the last Google I/O, a new solution for handling those was presented, called Room. It removes the usual boilerplate code and greatly simplifies working with SQLite on Android. We will discuss how to use Room efficiently, migrate existing code to the library, and test it. Additionally, we will check how to work with it using Kotlin.

2b0404a5db1a74f01bf3bf94d142e28c?s=128

Alex Zhukovich

March 03, 2018
Tweet

Transcript

  1. Let us play in Room with Kotlin @Alex_Zhukovich

  2. None
  3. None
  4. None
  5. val db = dbHelper.readableDatabase // Define a projection that specifies

    which columns from the database // you will actually use after this query. val projection = arrayOf(BaseColumns._ID, FeedEntry.COLUMN_NAME_TITLE, FeedEntry.COLUMN_NAME_SUBTITLE) // Filter results WHERE "title" = 'My Title' val selection = "${FeedEntry.COLUMN_NAME_TITLE} = ?" val selectionArgs = arrayOf("My Title") // How you want the results sorted in the resulting Cursor val sortOrder = "${FeedEntry.COLUMN_NAME_SUBTITLE} DESC" val cursor = db.query( FeedEntry.TABLE_NAME, // The table to query projection, // The array of columns to return (pass null to get all) selection, // The columns for the WHERE clause selectionArgs, // The values for the WHERE clause null, // don't group the rows null, // don't filter by row groups sortOrder // The sort order ) https://developer.android.com/training/data-storage/sqlite.html#kotlin
  6. val db = dbHelper.readableDatabase // Define a projection that specifies

    which columns from the database // you will actually use after this query. val projection = arrayOf(BaseColumns._ID, FeedEntry.COLUMN_NAME_TITLE, FeedEntry.COLUMN_NAME_SUBTITLE) // Filter results WHERE "title" = 'My Title' val selection = "${FeedEntry.COLUMN_NAME_TITLE} = ?" val selectionArgs = arrayOf("My Title") // How you want the results sorted in the resulting Cursor val sortOrder = "${FeedEntry.COLUMN_NAME_SUBTITLE} DESC" val cursor = db.query( FeedEntry.TABLE_NAME, // The table to query projection, // The array of columns to return (pass null to get all) selection, // The columns for the WHERE clause selectionArgs, // The values for the WHERE clause null, // don't group the rows null, // don't filter by row groups sortOrder // The sort order ) SELECT id, title, subTitle FROM Feed WHERE title = ? ORDER BY subTitle DESC https://developer.android.com/training/data-storage/sqlite.html#kotlin
  7. override fun onUpgrade(db: SQLiteDatabase, oldVersion: Int, newVersion: Int) { if

    (oldVersion == DATABASE_VERSION_1) { ... } if (oldVersion == DATABASE_VERSION_2) { ... } if (oldVersion == DATABASE_VERSION_3) { ... } if (oldVersion == DATABASE_VERSION_4) { ... } ... }
  8. DBFlow SQLBrite GreenDao Realm Room etc.

  9. val db = dbHelper.readableDatabase // Define a projection that specifies

    which columns from the database // you will actually use after this query. val projection = arrayOf(BaseColumns._ID, EmployeeEntry.COLUMN_NAME, EmployeeEntry.COLUMN_PHONE) // Filter results WHERE "name" = 'Alex' val selection = "${EmployeeEntry.COLUMN_NAME} = ?" val selectionArgs = arrayOf("Alex") // How you want the results sorted in the resulting Cursor val sortOrder = "${EmployeeEntry.COLUMN_NAME} DESC" val cursor = db.query( EmployeeEntry.TABLE_NAME, // The table to query projection, // The array of columns to return (pass null to get all) selection, // The columns for the WHERE clause selectionArgs, // The values for the WHERE clause null, // don't group the rows null, // don't filter by row groups sortOrder // The sort order ) SELECT id, name, phone FROM Employee WHERE name = ? ORDER BY name DESC
  10. "SELECT * FROM employees WHERE team_id = :teamId"

  11. @Query("SELECT * FROM employees WHERE team_id = :teamId")

  12. @Query("SELECT * FROM employees WHERE team_id = :teamId") fun getEmployeesByTeamId():

    List<Employee>
  13. @Query("SELECT * FROM employees WHERE team_id = :teamId") fun getEmployeesByTeamId(teamId:

    Long): List<Employee>
  14. @Dao interface EmployeeDao { @Query("SELECT * FROM employees WHERE team_id

    = :teamId") fun getEmployeesByTeamId(teamId: Long): List<Employee> }
  15. @Dao interface EmployeeDao { @Query("SELECT * FROM employees WHERE team_id

    = :teamId") fun getEmployeesByTeamId(teamId: Long): List<Employee> } @Entity data class Employee( @PrimaryKey val teamId: Long, val name: String, val phone: String)
  16. @Database(entities = [Employee::class], version = 1) abstract class ContactsDatabase: RoomDatabase()

    { abstract fun employeesDao(): EmployeesDAO } @Dao interface EmployeeDao { @Query("SELECT * FROM employees WHERE team_id = :teamId") fun getEmployeesByTeamId(): List<Employee> }
  17. Room.databaseBuilder(context.applicationContext, ContactsDatabase::class.java, DATABASE_NAME).build() @Database(entities = [Employee::class], version = 1) abstract

    class ContactsDatabase: RoomDatabase() { abstract fun employeesDao(): EmployeesDAO }
  18. @Insert fun insert(employee: Employee): Long @Insert fun insertListOfEmployees(employees: List<Employee>) @Insert

    fun insertEmployees(vararg employee: Employee): List<Long>
  19. @Update fun updateEmployee(employee: Employee): Int @Update fun updateEmployees(employees: List<Employee>): Int

    @Update fun updateEmployee(employee: Employee)
  20. @Delete fun deleteEmployee(employee: Employee): Int @Delete fun deleteEmployee(employees: List<Employee>) @Delete

    fun deleteEmployee(vararg employee: Employee): Int
  21. onConflict = ABORT FAIL IGNORE REPLACE ROLLBACK

  22. @Insert(onConflict = OnConflictStrategy.REPLACE) fun insertEmployee(employee: Employee) @Update(onConflict = OnConflictStrategy.ROLLBACK) fun

    updateEmployee(employee: Employee)
  23. Compile time errors

  24. @Query("SELECT id, surname FROM employee") fun getEmployees(): List<Employee> There is

    a problem with the query: [SQLITE_ERROR] SQL error or missing database ( no such column: surname) @Entity data class Employee( @PrimaryKey val id: Long, val name: String)
  25. @Query("SELECT * FROM employee") fun getEmployees(): List<Employee> There is a

    problem with the query: [SQLITE_ERROR] SQL error or missing database ( no such table: employee) @Entity(tableName = "employees") data class Employee( @PrimaryKey val id: Long, val name: String)
  26. @Dao interface EmployeesDAO { @Query("SELECT name FROM employees") fun getEmployees():

    List<Employee> } @Entity(tableName = "employees") data class Employee( @PrimaryKey val id: Long, val name: String) Columns returned by the query: name. Fields in Employee: id, name.
  27. @Dao interface EmployeesDAO { @SuppressWarnings(RoomWarnings.CURSOR_MISMATCH) @Query("SELECT name FROM employees") fun

    getEmployees(): List<Employee> }
  28. @Dao interface EmployeesDAO { @Query("SELECT name FROM employees") fun getEmployees():

    List<String> }
  29. Entity

  30. @Entity data class Employee( val id: Long, val name: String,

    val phone: String) An entity must have at least 1 field annotated with @PrimaryKey
  31. @Entity data class Employee( @PrimaryKey val id: Long, val name:

    String, val phone: String)
  32. @Entity data class Employee( val name: String, val phone: String)

    { @PrimaryKey(autoGenerate = true) var id: Long? = 0 }
  33. @Entity(tableName = "employees") data class Employee( @PrimaryKey val id: Long,

    val name: String, val phone: String)
  34. @Entity( tableName = "employees", primaryKeys = ["id", "name"]) data class

    Employee( val name: String, val phone: String)
  35. @Entity(tableName = "employees") data class Employee( @ColumnInfo(name = "employee_id") val

    id: Long, @ColumnInfo(name = "full_name") var name: String, @ColumnInfo(name = "phone_number") var phone: String)
  36. @Entity(tableName = "employees") data class Employee( @ColumnInfo(name = "employee_id") val

    id: Long, @ColumnInfo(name = "full_name") var name: String, @ColumnInfo(name = "phone_number") var phone: String) { @Ingore var image: Bitmap? = null }
  37. @Entity( tableName = "employees", foreignKeys = [(ForeignKey( entity = Team::class,

    parentColumns = arrayOf("id"), childColumns = arrayOf("team_id"), onDelete = ForeignKey.CASCADE))]) data class Employee( @ColumnInfo(name = "team_id") var teamId: Long, @ColumnInfo(name = "name") var name: String, @ColumnInfo(name = "phone") var phone: String) { @PrimaryKey(autoGenerate = true) @ColumnInfo(name = "employee_id") var id: Long? = 0 }
  38. @Entity(tableName = "employees") data class Employee( @ColumnInfo(name = "name") var

    name: String, @ColumnInfo(name = "phone") var phone: String, @Embedded val homeLocation: Location) { @PrimaryKey(autoGenerate = true) @ColumnInfo(name = "employee_id") var id: Long? = 0 } id name phone latitude longitude data class Location( val latitude: Double, val longitude: Double)
  39. @Entity(tableName = "employees") data class Employee( @ColumnInfo(name = "name") val

    name: String, @ColumnInfo(name = "phone") val phone: String, @Embedded(prefix = "home_") val homeLocation: Location, @Embedded(prefix = "work_") val workLocation: Location){ @PrimaryKey(autoGenerate = true) @ColumnInfo(name = "employee_id") var id: Long? = 0 } id name phone home_latitude home_longitude work_latitude work_longitude data class Location( val latitude: Double, val longitude: Double)
  40. Type Converters

  41. @Entity(tableName = "employees") data class Employee( @ColumnInfo(name = "name") val

    name: String, @ColumnInfo(name = "phone") val phone: String, @ColumnInfo(name = "hire_date") val hireDate: Date) { @PrimaryKey(autoGenerate = true) @ColumnInfo(name = "employee_id") var id: Long? = 0 } Cannot figure out how to save this field into database. You can consider adding a type converter for it. private Date hireDate;
  42. object DateConverter { @TypeConverter @JvmStatic fun toDate(value: Long): Date {

    return Date(value) } @TypeConverter @JvmStatic fun toLong(value: Date): Long { return value.time } }
  43. @Database( entities = [Team::class, Employee::class], version = 1) @TypeConverters(DateConverter::class) abstract

    class ContactsDatabase: RoomDatabase() { abstract fun teamsDao(): TeamsDAO abstract fun employeesDao(): EmployeesDAO }
  44. Testing

  45. None
  46. adb shell Instrumentation am instrument Application Test Application

  47. None
  48. None
  49. @RunWith(RobolectricTestRunner::class) class EmployeeDaoTest { … lateinit var database: ContactsDatabase @Before

    fun setUp() { database = Room.inMemoryDatabaseBuilder( RuntimeEnvironment.application.baseContext, ContactsDatabase::class.java) .allowMainThreadQueries() .build() } @Test fun shouldInsertAndRetrieveEmployee() { database.teamsDao().insertTeam(testTeam) database.employeesDao().insertEmployee(testEmployee) val employees = database.employeesDao().getEmployeeListByTeamId(TEAM_ID) assertEquals(1, employees.size) assertEquals(testEmployee, employees.first()) } @After fun tearDown() { database.close() } }
  50. @RunWith(RobolectricTestRunner::class) class EmployeeDaoTest { … lateinit var database: ContactsDatabase @Before

    fun setUp() { database = Room.inMemoryDatabaseBuilder( RuntimeEnvironment.application.baseContext, ContactsDatabase::class.java) .allowMainThreadQueries() .build() } @Test fun shouldInsertAndRetrieveEmployee() { database.teamsDao().insertTeam(testTeam) database.employeesDao().insertEmployee(testEmployee) val employees = database.employeesDao().getEmployeesByTeamId(TEAM_ID) assertEquals(1, employees.size) assertEquals(testEmployee, employees.first()) } @After fun tearDown() { database.close() } }
  51. @RunWith(RobolectricTestRunner::class) class EmployeeDaoTest { … lateinit var database: ContactsDatabase @Before

    fun setUp() { database = Room.inMemoryDatabaseBuilder( RuntimeEnvironment.application.baseContext, ContactsDatabase::class.java) .allowMainThreadQueries() .build() } @Test fun shouldInsertAndRetrieveEmployee() { database.teamsDao().insertTeam(testTeam) database.employeesDao().insertEmployee(testEmployee) val employees = database.employeesDao().getEmployeeListByTeamId(TEAM_ID) assertEquals(1, employees.size) assertEquals(testEmployee, employees.first()) } @After fun tearDown() { database.close() } }
  52. Migration

  53. val MIGRATION_1_2: Migration = object : Migration(1, 2) { override

    fun migrate(database: SupportSQLiteDatabase) { ... } } val database = Room.databaseBuilder( context.applicationContext, ContactsDatabase::class.java, DATABASE_NAME) .addMigrations(MIGRATION_1_2) .build()
  54. val database = Room.databaseBuilder( context.applicationContext, ContactsDatabase::class.java, DATABASE_NAME) .fallbackToDestructiveMigration() .build()

  55. val MIGRATION_2_3: Migration = object : Migration(2, 3) { override

    fun migrate(database: SupportSQLiteDatabase) { // Create a new table createTable(database, CREATE_EMPLOYEE_TABLE_V3) // Copy the data from the old table to the new one copyData(database, EMPLOYEE_TABLE, TMP_EMPLOYEE_TABLE) // Remove the old table removeTable(database, EMPLOYEE_TABLE) // Rename the new table to the correct name renameTable(database, TMP_EMPLOYEE_TABLE, EMPLOYEE_TABLE) } }
  56. object SQLiteTestHelper { fun insertEmployee(database: SupportSQLiteDatabase, teamId: Long, name: String,

    phone: String) { val values = ContentValues().apply { put(TEAM_ID, teamId) put(EMPLOYEE_NAME, name) put(EMPLOYEE_PHONE, phone) } database.insert(EMPLOYEE, SQLiteDatabase.CONFLICT_REPLACE, values) } fun getMigrationDatabase(helper: MigrationTestHelper): ContactsDatabase { val database = Room.databaseBuilder(InstrumentationRegistry.getTargetContext(), ContactsDatabase::class.java, DatabaseMigrationTests.DATABASE_NAME) .build() helper.closeWhenFinished(database) return database } fun clearDatabase(database: SupportSQLiteDatabase) { database.execSQL("DROP TABLE IF EXISTS $EMPLOYEE") database.execSQL("DROP TABLE IF EXISTS $TEAM") database.close() } }
  57. object SQLiteTestHelper { fun insertEmployee(database: SupportSQLiteDatabase, teamId: Long, name: String,

    phone: String) { val values = ContentValues().apply { put(TEAM_ID, teamId) put(EMPLOYEE_NAME, name) put(EMPLOYEE_PHONE, phone) } database.insert(EMPLOYEE, SQLiteDatabase.CONFLICT_REPLACE, values) } fun getMigrationDatabase(helper: MigrationTestHelper): ContactsDatabase { val database = Room.databaseBuilder( InstrumentationRegistry.getTargetContext(), ContactsDatabase::class.java, DatabaseMigrationTests.DATABASE_NAME) .build() helper.closeWhenFinished(database) return database } fun clearDatabase(database: SupportSQLiteDatabase) { database.execSQL("DROP TABLE IF EXISTS $EMPLOYEE") database.execSQL("DROP TABLE IF EXISTS $TEAM") database.close() } }
  58. object SQLiteTestHelper { fun insertEmployee(database: SupportSQLiteDatabase, teamId: Long, name: String,

    phone: String) { val values = ContentValues().apply { put(TEAM_ID, teamId) put(EMPLOYEE_NAME, name) put(EMPLOYEE_PHONE, phone) } database.insert(EMPLOYEE, SQLiteDatabase.CONFLICT_REPLACE, values) } fun getMigrationDatabase(helper: MigrationTestHelper): ContactsDatabase { val database = Room.databaseBuilder(InstrumentationRegistry.getTargetContext(), ContactsDatabase::class.java, DatabaseMigrationTests.DATABASE_NAME) .build() helper.closeWhenFinished(database) return database } fun clearDatabase(database: SupportSQLiteDatabase) { database.execSQL("DROP TABLE IF EXISTS $EMPLOYEE") database.execSQL("DROP TABLE IF EXISTS $TEAM") database.close() } }
  59. @RunWith(AndroidJUnit4::class) class DatabaseMigrationTests { @Rule @JvmField var migrationTestHelper: MigrationTestHelper =

    MigrationTestHelper( InstrumentationRegistry.getInstrumentation(), ContactsDatabase::class.java.canonicalName, FrameworkSQLiteOpenHelperFactory()) private lateinit var database: SupportSQLiteDatabase @Before fun setUp() { database = migrationTestHelper.createDatabase(DATABASE_NAME, 1) } @Test fun shouldMigrateDatabaseFromFirstToSecondVersion() { val requiredVersion = 2 val validateDroppedTables = true SQLiteTestHelper.insertTeam(database, TEAM_TITLE) SQLiteTestHelper.insertEmployee(database, TEAM_ID, CONTACT_NAME, CONTACT_PHONE) migrationTestHelper.runMigrationsAndValidate( DATABASE_NAME, requiredVersion, validateDroppedTables, ContactsDatabase.MIGRATION_1_2) val employees = SQLiteTestHelper.getMigrationDatabase(migrationTestHelper).employeesDao().getEmployeeListByTeamId(1) assertEquals(1, employees.size) ... } @After fun tearDown() { SQLiteTestHelper.clearDatabase(database) } }
  60. @RunWith(AndroidJUnit4::class) class DatabaseMigrationTests { @Rule @JvmField var migrationTestHelper: MigrationTestHelper =

    MigrationTestHelper( InstrumentationRegistry.getInstrumentation(), ContactsDatabase::class.java.canonicalName, FrameworkSQLiteOpenHelperFactory()) private lateinit var database: SupportSQLiteDatabase @Before fun setUp() { database = migrationTestHelper.createDatabase(DATABASE_NAME, 1) } @Test fun shouldMigrateDatabaseFromFirstToSecondVersion() { val requiredVersion = 2 val validateDroppedTables = true SQLiteTestHelper.insertTeam(database, TEAM_TITLE) SQLiteTestHelper.insertEmployee(database, TEAM_ID, CONTACT_NAME, CONTACT_PHONE) migrationTestHelper.runMigrationsAndValidate( DATABASE_NAME, requiredVersion, validateDroppedTables, ContactsDatabase.MIGRATION_1_2) val employees = SQLiteTestHelper.getMigrationDatabase(migrationTestHelper) .employeesDao() .getEmployeeListByTeamId(1) assertEquals(1, employees.size) ... } @After fun tearDown() { SQLiteTestHelper.clearDatabase(database) } }
  61. @RunWith(AndroidJUnit4::class) class DatabaseMigrationTests { @Rule @JvmField var migrationTestHelper: MigrationTestHelper =

    MigrationTestHelper( InstrumentationRegistry.getInstrumentation(), ContactsDatabase::class.java.canonicalName, FrameworkSQLiteOpenHelperFactory()) private lateinit var database: SupportSQLiteDatabase @Before fun setUp() { database = migrationTestHelper.createDatabase(DATABASE_NAME, 1) } @Test fun shouldMigrateDatabaseFromFirstToSecondVersion() { val requiredVersion = 2 val validateDroppedTables = true SQLiteTestHelper.insertTeam(database, TEAM_TITLE) SQLiteTestHelper.insertEmployee(database, TEAM_ID, CONTACT_NAME, CONTACT_PHONE) migrationTestHelper.runMigrationsAndValidate(DATABASE_NAME, requiredVersion, validateDroppedTables, ContactsDatabase.MIGRATION_1_2) val employees = SQLiteTestHelper.getMigrationDatabase(migrationTestHelper) .employeesDao() .getEmployeeListByTeamId(1) assertEquals(1, employees.size) ... } @After fun tearDown() { SQLiteTestHelper.clearDatabase(database) } }
  62. @RunWith(AndroidJUnit4::class) class DatabaseMigrationTests { @Rule @JvmField var migrationTestHelper: MigrationTestHelper =

    MigrationTestHelper( InstrumentationRegistry.getInstrumentation(), ContactsDatabase::class.java.canonicalName, FrameworkSQLiteOpenHelperFactory()) private lateinit var database: SupportSQLiteDatabase @Before fun setUp() { database = migrationTestHelper.createDatabase(DATABASE_NAME, 1) } @Test fun shouldMigrateDatabaseFromFirstToSecondVersion() { val requiredVersion = 2 val validateDroppedTables = true SQLiteTestHelper.insertTeam(database, TEAM_TITLE) SQLiteTestHelper.insertEmployee(database, TEAM_ID, CONTACT_NAME, CONTACT_PHONE) migrationTestHelper.runMigrationsAndValidate( DATABASE_NAME, requiredVersion, validateDroppedTables, ContactsDatabase.MIGRATION_1_2) val employees = SQLiteTestHelper.getMigrationDatabase(migrationTestHelper) .employeesDao() .getEmployeeListByTeamId(1) assertEquals(1, employees.size) ... } @After fun tearDown() { SQLiteTestHelper.clearDatabase(database) } }
  63. LiveData RxJava

  64. @Query("SELECT * FROM employees") fun getEmployees(): LiveData<List<Employee>>

  65. @Query("SELECT * FROM employees") fun getEmployees(): Flowable<List<Employee>>

  66. @Query("SELECT * FROM employees") fun getEmployees(): Single<List<Employee>>

  67. @Query("SELECT * FROM employees") fun getEmployees(): Maybe<List<Employee>>

  68. Q&A Room Persistence Library https://developer.android.com/topic/libraries/architecture/room.html 7 Steps To Room https://medium.com/google-developers/7-steps-to-room-27a5fe5f99b2

    7 Pro-tips for Room https://medium.com/google-developers/7-pro-tips-for-room-fbadea4bfbd1 Blog http://alexzh.com/ @Alex_Zhukovich