Let us play in Room with Kotlin @Alex_Zhukovich 

No content

No content

No content

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 

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 

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) { ... } ... } 

DBFlow SQLBrite GreenDao Realm Room etc. 

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 

"SELECT * FROM employees WHERE team_id = :teamId" 

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

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

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

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

@Dao interface EmployeeDao { @Query("SELECT * FROM employees WHERE team_id = :teamId") fun getEmployeesByTeamId(teamId: Long): List } @Entity data class Employee( @PrimaryKey val teamId: Long, val name: String, val phone: String) 

@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 } 

Room.databaseBuilder(context.applicationContext, ContactsDatabase::class.java, DATABASE_NAME).build() @Database(entities = [Employee::class], version = 1) abstract class ContactsDatabase: RoomDatabase() { abstract fun employeesDao(): EmployeesDAO } 

@Insert fun insert(employee: Employee): Long @Insert fun insertListOfEmployees(employees: List) @Insert fun insertEmployees(vararg employee: Employee): List 

@Update fun updateEmployee(employee: Employee): Int @Update fun updateEmployees(employees: List): Int @Update fun updateEmployee(employee: Employee) 

@Delete fun deleteEmployee(employee: Employee): Int @Delete fun deleteEmployee(employees: List) @Delete fun deleteEmployee(vararg employee: Employee): Int 

onConflict = ABORT FAIL IGNORE REPLACE ROLLBACK 

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

Compile time errors 

@Query("SELECT id, surname FROM employee") fun getEmployees(): List 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) 

@Query("SELECT * FROM employee") fun getEmployees(): List 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) 

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

@Dao interface EmployeesDAO { @SuppressWarnings(RoomWarnings.CURSOR_MISMATCH) @Query("SELECT name FROM employees") fun getEmployees(): List } 

@Dao interface EmployeesDAO { @Query("SELECT name FROM employees") fun getEmployees(): List } 

Entity 

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

@Entity data class Employee( @PrimaryKey val id: Long, val name: String, val phone: String) 

@Entity data class Employee( val name: String, val phone: String) { @PrimaryKey(autoGenerate = true) var id: Long? = 0 } 

@Entity(tableName = "employees") data class Employee( @PrimaryKey val id: Long, val name: String, val phone: String) 

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

@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) 

@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 } 

@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 } 

@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) 

@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) 

Type Converters 

@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; 

object DateConverter { @TypeConverter @JvmStatic fun toDate(value: Long): Date { return Date(value) } @TypeConverter @JvmStatic fun toLong(value: Date): Long { return value.time } } 

@Database( entities = [Team::class, Employee::class], version = 1) @TypeConverters(DateConverter::class) abstract class ContactsDatabase: RoomDatabase() { abstract fun teamsDao(): TeamsDAO abstract fun employeesDao(): EmployeesDAO } 

Testing 

No content

adb shell Instrumentation am instrument Application Test Application 

No content

No content

@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() } } 

@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() } } 

@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() } } 

Migration 

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() 

val database = Room.databaseBuilder( context.applicationContext, ContactsDatabase::class.java, DATABASE_NAME) .fallbackToDestructiveMigration() .build() 

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

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

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

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

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

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

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

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

LiveData RxJava 

@Query("SELECT * FROM employees") fun getEmployees(): LiveData> 

@Query("SELECT * FROM employees") fun getEmployees(): Flowable> 

@Query("SELECT * FROM employees") fun getEmployees(): Single> 

@Query("SELECT * FROM employees") fun getEmployees(): Maybe> 

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