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.

Transcript

1. Let us play in Room with Kotlin @Alex_Zhukovich
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
46. adb shell Instrumentation am instrument Application Test Application
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