$30 off During Our Annual Pro Sale. View Details »

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.

Alex Zhukovich

March 03, 2018
Tweet

More Decks by Alex Zhukovich

Other Decks in Programming

Transcript

  1. 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
  2. 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
  3. 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) { ... } ... }
  4. 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
  5. @Dao interface EmployeeDao { @Query("SELECT * FROM employees WHERE team_id

    = :teamId") fun getEmployeesByTeamId(teamId: Long): List<Employee> }
  6. @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)
  7. @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> }
  8. @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)
  9. @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)
  10. @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.
  11. @Entity data class Employee( val id: Long, val name: String,

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

    { @PrimaryKey(autoGenerate = true) var id: Long? = 0 }
  13. @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)
  14. @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 }
  15. @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 }
  16. @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)
  17. @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)
  18. @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;
  19. object DateConverter { @TypeConverter @JvmStatic fun toDate(value: Long): Date {

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

    class ContactsDatabase: RoomDatabase() { abstract fun teamsDao(): TeamsDAO abstract fun employeesDao(): EmployeesDAO }
  21. @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() } }
  22. @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() } }
  23. @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() } }
  24. 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()
  25. 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) } }
  26. 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() } }
  27. 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() } }
  28. 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() } }
  29. @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) } }
  30. @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) } }
  31. @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) } }
  32. @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) } }
  33. 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