Room: an SQLite object mapping library

Handling an SQLite database in Android implies challenges like a lot of boilerplate code, database operations on the main thread, queries checked at runtime and especially unmaintainable code. At Google I/O 2017, the Android team launched Room, an SQLite object mapper, that provides a set of components and features that will solve all these challenges and will help us, the developers, to have a better experience when using a database in our app.

The presentation will include examples about how to use the main components from Room (@Entity, @Dao, @Database), how to handle the relations between the entities, how to use the (observable) queries and not run database operations on the main thread. Also, we will discover more details about the migration support, compile time query verification and what’s happening behind the scenes.

Transcript

1. Room: an SQLite object mapping library Magda Miu @magdamiu magdamiu.com
2. If you would have the possibility to change something in the Android development what will it be that thing?
3. ‘’ I don’t want to deal with technical things. I just want to deliver the project. yiğit boyar - Architecture Components – Behind the Scenes
5. ‘’ I don’t want to deal with technical things. I just want to deliver the project. yiğit boyar - Architecture Components – Behind the Scenes
8. public class DatabaseHandler extends SQLiteOpenHelper { // All Static variables // Database Version private static final int DATABASE_VERSION = 1; // Database Name private static final String DATABASE_NAME = "CountriesManager"; // Countries table name private static final String TABLE_Countries = "Country"; // Countries Table Columns names private static final String KEY_ID = "id"; private static final String KEY_NAME = "name"; private static final String KEY_TOWN = "town";
9. public DatabaseHandler(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } // Creating Tables @Override public void onCreate(SQLiteDatabase db) { String CREATE_Countries_TABLE = "CREATE TABLE " + TABLE_Countries + "(" + KEY_ID + " INTEGER PRIMARY KEY," + KEY_NAME + " TEXT," + KEY_TOWN + " TEXT" + ")"; db.execSQL(CREATE_Countries_TABLE); }
10. public void addCountry(Country country) { SQLiteDatabase db = this.getWritableDatabase(); db.beginTransaction(); try { ContentValues values = new ContentValues(); values.put(KEY_NAME, country.getName()); // Country Name values.put(KEY_TOWN, country.getTown()); // Country Town db.insert(TABLE_Countries, null, values); db.setTransactionSuccessful(); } catch (Exception e) { Log.d(TAG, "Error while trying to add country to database"); } finally { db.endTransaction(); } }
11. Country getCountry(int id) { SQLiteDatabase db = this.getReadableDatabase(); Country country = null; Cursor cursor = db.query(TABLE_Countries, new String[]{KEY_ID, KEY_NAME, KEY_TOWN}, KEY_ID + "=?", new String[]{String.valueOf(id)}, null, null, null, null); try { if (cursor != null) { cursor.moveToFirst(); country = new Country(Integer.parseInt(cursor.getString(0)), cursor.getString(1), cursor.getString(2)); } } catch (Exception e) { Log.d(TAG, "Error while trying to get countries from database"); } finally { if (cursor != null && !cursor.isClosed()) cursor.close();
13. SQLite challenges
14. SQLite challenges ❏ Boilerplate code
15. SQLite challenges ❏ Boilerplate code ❏ SQL queries checked at runtime
16. SQLite challenges ❏ Boilerplate code ❏ SQL queries checked at runtime ❏ Database operations on the main thread
17. SQLite challenges ❏ Boilerplate code ❏ SQL queries checked at runtime ❏ Database operations on the main thread ❏ Unmaintainable code
18. SQLite challenges ❏ Boilerplate code ❏ SQL queries checked at runtime ❏ Database operations on the main thread ❏ Unmaintainable code ❏ Untestable code
19. Architecture Components Navigation LiveData Room ViewModel Lifecycle Pagination Work Manager
21. Let’s get started! ROOM
22. Why ORM?
23. Why ORM? ❏ Productivity
24. Why ORM? ❏ Productivity ❏ Application design
25. Why ORM? ❏ Productivity ❏ Application design ❏ Application Maintainability
26. YES NO Compile time query verification x Migration support x Java Query Builder API x SQL Completion in Android Studio x Relationships x Architecture Components - Behind the Scenes by Yiğit Boyar
28. Dependencies
29. 1.1.1 Room dependency 2.0.0-alpha1 AndroidX
30. Components
31. Components Entity
32. Components Dao Entity
33. Components Database Dao Entity
34. @Entity
35. public class Company { private int companyId; private String name; Bitmap picture; }
36. public class Company { private int companyId; private String name; Bitmap picture; } Company id integer name text
37. @Entity(tableName = "Company") public class Company { @PrimaryKey(autoGenerate = true) @ColumnInfo(name = "id") private int companyId; @ColumnInfo(name = "name") private String name; @Ignore Bitmap picture; Company id integer name text
38. @Entity(tableName = "Company") public class Company { @PrimaryKey(autoGenerate = true) @ColumnInfo(name = "id") private int companyId; @ColumnInfo(name = "name") private String name; @Ignore Bitmap picture; Company id integer name text
39. @TypeConverter Company id integer name text
40. @TypeConverter Company id integer name text date_added
41. @TypeConverter Company id integer name text date_added date
42. @TypeConverter Company id integer name text date_added date
43. @TypeConverter Company id integer name text date_added
44. @TypeConverter Company id integer name text date_added integer
45. @TypeConverter Company id integer name text date_added integer
46. @TypeConverter
47. @TypeConverter public class DateConverter { @TypeConverter public static Date toDate(Long timestamp) { return timestamp == null ? null : new Date(timestamp); } @TypeConverter public static Long toTimestamp(Date date) { return date == null ? null : date.getTime(); } } Company id integer name text date integer
48. @TypeConverter @ColumnInfo(name = "date_updated") @TypeConverters(DateConverter.class) private Date itemUpdatedDate; ------------------------------------------------- @Dao @TypeConverters(DateConverter.class) public interface CompanyDao ------------------------------------------------- @Database(entities = {Company.class}, version = 1) @TypeConverters(DateConverter.class) public abstract class AppDatabase extends RoomDatabase
49. @TypeConverter @ColumnInfo(name = "date_updated") @TypeConverters(DateConverter.class) private Date itemUpdatedDate; ------------------------------------------------- @Dao @TypeConverters(DateConverter.class) public interface CompanyDao ------------------------------------------------- @Database(entities = {Company.class}, version = 1) @TypeConverters(DateConverter.class) public abstract class AppDatabase extends RoomDatabase
50. @TypeConverter @ColumnInfo(name = "date_updated") @TypeConverters(DateConverter.class) private Date itemUpdatedDate; ------------------------------------------------- @Dao @TypeConverters(DateConverter.class) public interface CompanyDao ------------------------------------------------- @Database(entities = {Company.class}, version = 1) @TypeConverters(DateConverter.class) public abstract class AppDatabase extends RoomDatabase
51. @Dao
52. @Dao public interface CompanyDao { @Query("SELECT * FROM Company") LiveData<List<Company>> getAllCountries(); @Insert void insertCompany(Company company); @Update void updateCompany(Company company); @Delete void deleteCompany(Company company); }
53. @Dao public interface CompanyDao { @Query("SELECT * FROM Company") List<Company> getAllCompanies(); @Insert void insertCompany(Company company); @Update void updateCompany(Company company); @Delete void deleteCompany(Company company); }
54. @Dao public interface CompanyDao { @Query("SELECT * FROM Companies") List<Company> getAllCompanies(); @Insert void insertCompany(Company company); @Update void updateCompany(Company company); @Delete void deleteCompany(Company company); }
55. @Dao public interface CompanyDao { @Query("SELECT * FROM Companies") List<Company> getAllCompanies(); @Insert void insertCompany(Company company); @Update void updateCompany(Company company); @Delete void deleteCompany(Company company); }
56. @Dao public interface DepartmentDao { @Insert void insertAll(List<Department> departments); }
57. public class DepartmentDao_Impl implements DepartmentDao { Behind the scenes
58. public class DepartmentDao_Impl implements DepartmentDao { private final RoomDatabase __db; Behind the scenes
59. public class DepartmentDao_Impl implements DepartmentDao { private final RoomDatabase __db; public void insertAll(List<Department> departments) { this.__db.beginTransaction(); try { this.__insertionAdapterOfDepartment.insert(departments); this.__db.setTransactionSuccessful(); } finally { this.__db.endTransaction(); } } } Behind the scenes
60. @Database
61. @Database(entities = {Company.class}, version = 1) public abstract class AppDatabase extends RoomDatabase { private static AppDatabase INSTANCE; public abstract CompanyDao companyDao(); public static AppDatabase getAppDatabase(Context context) { if (INSTANCE == null) { INSTANCE = Room.databaseBuilder(context.getApplicationContext(), AppDatabase.class, "company-db") .build(); } return INSTANCE;
62. @Database(entities = {Company.class}, version = 1) public abstract class AppDatabase extends RoomDatabase { private static AppDatabase INSTANCE; public abstract CompanyDao companyDao(); public static AppDatabase getAppDatabase(Context context) { if (INSTANCE == null) { INSTANCE = Room.databaseBuilder(context.getApplicationContext(), AppDatabase.class, "company-db") .build(); } return INSTANCE;
63. @Database(entities = {Company.class}, version = 1) public abstract class AppDatabase extends RoomDatabase { private static AppDatabase INSTANCE; public abstract CompanyDao companyDao(); public static AppDatabase getAppDatabase(Context context) { if (INSTANCE == null) { INSTANCE = Room.databaseBuilder(context.getApplicationContext(), AppDatabase.class, "company-db") .build(); } return INSTANCE;
64. @Database(entities = {Company.class}, version = 1) public abstract class AppDatabase extends RoomDatabase { private static AppDatabase INSTANCE; public abstract CompanyDao companyDao(); public static AppDatabase getAppDatabase(Context context) { if (INSTANCE == null) { INSTANCE = Room.databaseBuilder(context.getApplicationContext(), AppDatabase.class, "company-db") .build(); } return INSTANCE;
65. @Database(entities = {Company.class}, version = 1) public abstract class AppDatabase extends RoomDatabase { private static AppDatabase INSTANCE; public abstract CompanyDao companyDao(); public static AppDatabase getAppDatabase(Context context) { if (INSTANCE == null) { INSTANCE = Room.databaseBuilder(context.getApplicationContext(), AppDatabase.class, "company-db") .build(); } return INSTANCE;
66. “Relations”
67. @Entity(tableName = "Company") public class Company { @PrimaryKey(autoGenerate = true) @ColumnInfo(name = "id") private int companyId;
68. @Entity(tableName = "Company") public class Company { @PrimaryKey(autoGenerate = true) @ColumnInfo(name = "id") private int companyId; private int directorId; private Employee director; public Employee getDirector() { if(director == null) { director = getEmployee(employeeId) } return director; }
69. @Query("SELECT * FROM Company") List<Company> getAllCompanies();
70. @Query("SELECT * FROM Company") List<Company> getAllCompanies(); txtName.setText(company.getDirector().getName());
71. @Query("SELECT * FROM Company") List<Company> getAllCompanies(); txtName.setText(company.getDirector().getName());
74. HQ office Berlin
75. public class Location { private double latitude; private double longitude; // getters // setters
76. @Embedded (Nested objects) @Entity(tableName = "Company") public class Company { @PrimaryKey(autoGenerate = true) @ColumnInfo(name = "id") private int companyId; @ColumnInfo(name = "name") private String name; @ColumnInfo(name = "date_updated") @TypeConverters(DateConverter.class) private Date itemUpdatedDate; @Embedded private Location location; @Embedded private Location headLocation;
77. @Embedded (Nested objects) @Entity(tableName = "Company") public class Company { @PrimaryKey(autoGenerate = true) @ColumnInfo(name = "id") private int companyId; @ColumnInfo(name = "name") private String name; @ColumnInfo(name = "date_updated") @TypeConverters(DateConverter.class) private Date itemUpdatedDate; @Embedded private Location location; @Embedded(prefix = "hq_") private Location headLocation;
78. Company id integer name text date integer latitude real longitude real hq_latitude real hq_longitude real
79. @TypeConverter Employee id integer name text
80. Employee id integer name text company_id integer
81. Employee id integer name text company_id integer Company id integer name text date integer latitude real longitude real hq_latitude real hq_longitude real 1 M
82. @ForeignKey @Entity(foreignKeys = @ForeignKey(entity = Company.class, parentColumns = "id", childColumns = "company_id", onDelete = ForeignKey.NO_ACTION)) public class Employee { @PrimaryKey(autoGenerate = true) @ColumnInfo(name = "id") private int employeeId; @ColumnInfo(name = "name") private String name; @ColumnInfo(name = "company_id") private int companyId;
83. @Relation @Entity public class Department { @PrimaryKey private int id; private int companyId; private String name; … Department id integer name text
84. @Relation public class CompanyAndAllDepartments { @Embedded public Company company; @Relation(parentColumn = "id", entityColumn = "companyId", entity = Department.class) public List<Department> departments; … --------------------------------------------------
85. @Relation public class CompanyAndAllDepartments { @Embedded public Company company; @Relation(parentColumn = "id", entityColumn = "companyId", entity = Department.class) public List<Department> departments; … @Dao public interface CompanyDepartmentsDao { @Query("SELECT * FROM Company WHERE id = :companyId") CompanyAndAllDepartments loadCompanyAllDepartments(long companyId); … --------------------------------------------------
86. Queries
87. @Insert @Delete @Update @Query
88. @Insert @Update @Delete @Insert(onConflict = OnConflictStrategy.ABORT) void insertCompany(Company company); @Insert(onConflict = OnConflictStrategy.REPLACE) void insertAll(List<Company> companies); @Insert(onConflict = OnConflictStrategy.REPLACE) void insertAll(Company... companies); ------------------------------------------------
89. @Insert @Update @Delete @Insert(onConflict = OnConflictStrategy.REPLACE) void insertCompany(Company company); @Insert(onConflict = OnConflictStrategy.REPLACE) void insertAll(List<Company> companies); @Insert(onConflict = OnConflictStrategy.REPLACE) void insertAll(Company... companies); @Update void updateCompany(Company company); @Update void updateCompanies(Company... company); ------------------------------------------------ ------------------------------------------------
90. @Insert @Update @Delete @Insert(onConflict = OnConflictStrategy.REPLACE) void insertCompany(Company company); @Insert(onConflict = OnConflictStrategy.REPLACE) void insertAll(List<Company> companies); @Insert(onConflict = OnConflictStrategy.REPLACE) void insertAll(Company... companies); @Update void updateCompany(Company company); @Update void updateCompanies(Company... company); ------------------------------------------------ @Delete void deleteCompany(Company company); @Delete void deleteCompanies(Company... company); ------------------------------------------------
91. @Query* @Query("SELECT * FROM Company WHERE name = :companyName") List<Company> getCompanies(String companyName); @Query("SELECT * FROM Company") LiveData<List<Company>> getAllCompanies(); @Query("SELECT * FROM Company") Flowable<List<Company>> getAllCompanies(); @RawQuery(observedEntities = Employee.class) LiveData<List<Employee>> getAllEmployeesWithLimit(SupportSQLiteQuery query); java.lang.IllegalStateException: Cannot access database on the main thread since it may potentially lock the UI for a long period of time.
92. @Transaction @Dao public abstract class DepartmentDao { @Insert public abstract void insert(Department product); @Delete public abstract void delete(Department product); @Transaction public void insertAndDeleteInTransaction(Department newDepartment, Department oldDepartment) { // Anything inside this method runs in a single transaction. insert(newDepartment); delete(oldDepartment); } }
93. Threading - Room 1.0 First thread Second Thread
94. Threading - Room 1.0 First thread Second Thread WRITE
95. Threading - Room 1.0 First thread Second Thread WRITE WAITING...
96. Threading - Room 1.0 First thread Second Thread WRITE WAITING... READ
98. Threading - Room 1.1 First thread Second Thread
99. Threading - Room 1.1 First thread Second Thread WRITE READ
100. Migration
101. Company id integer name text date integer latitude real longitude real hq_latitude real hq_longitude real Version 1
102. Company id integer name text date integer latitude real longitude real hq_latitude real hq_longitude real ref_no text Version 2
103. Migration
104. Version y Version x Migration
105. Version y Version x Migration ------------------------------------------------------------
106. Version y Version x Migration Version 2 Version 1 ------------------------------------------------------------
107. // Upgrading database @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // oldVersion -> newVersion }
108. // Upgrading database @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // oldVersion -> newVersion } “CLASSICAL” APPRO ACH
110. final Migration MIGRATION_X_Y = new Migration(X, Y) { @Override public void migrate(SupportSQLiteDatabase database) { // your code here } };
111. final Migration MIGRATION_X_Y = new Migration(X, Y) { @Override public void migrate(SupportSQLiteDatabase database) { // your code here } }; RO O M APPRO ACH
112. static final Migration MIGRATION_1_2 = new Migration(1, 2) { @Override public void migrate(SupportSQLiteDatabase database) { database.execSQL("ALTER TABLE Company " + " ADD COLUMN ref_no TEXT"); } };
113. public static AppDatabase getAppDatabase(Context context) { if (INSTANCE == null) { INSTANCE = Room.databaseBuilder(context.getApplicationContext(), AppDatabase.class, DATABASE_NAME) .addMigrations(MIGRATION_1_2) .build(); } return INSTANCE; }
114. public static AppDatabase getAppDatabase(Context context) { if (INSTANCE == null) { INSTANCE = Room.databaseBuilder(context.getApplicationContext(), AppDatabase.class, DATABASE_NAME) .addMigrations(MIGRATION_1_2) .build(); } return INSTANCE; }
115. Version 2 Version 1 Migration room_master_table id identity_hash 42 <v1_hash> room_master_table id identity_hash 42 <v2_hash>
118. /** * You can set annotation processor argument (room.schemaLocation) to tell Room to * export the schema into a folder. Even though it is not mandatory, it is a good * practice to have version history in your codebase and you should commit that file * into your version control system (but don't ship it with your app!). * */ //export schema javaCompileOptions { annotationProcessorOptions { arguments = ["room.schemaLocation": "$projectDir/schemas".toString()] } }
119. /** * Room will throw an IllegalStateException if you don’t provide a Migration. * */ if (INSTANCE == null) { INSTANCE = Room.databaseBuilder(context.getApplicationContext(), AppDatabase.class, "company-db") .fallbackToDestructiveMigration() .build(); }
120. /** * Room will throw an IllegalStateException if you don’t provide a Migration. * */ if (INSTANCE == null) { INSTANCE = Room.databaseBuilder(context.getApplicationContext(), AppDatabase.class, "company-db") .fallbackToDestructiveMigration() .build(); }
121. /** * Room will throw an IllegalStateException if you don’t provide a Migration. * */ if (INSTANCE == null) { INSTANCE = Room.databaseBuilder(context.getApplicationContext(), AppDatabase.class, "company-db") .fallbackToDestructiveMigrationFrom(int... startVersions) .build(); }
122. Testing
124. @RunWith(AndroidJUnit4.class) public class SimpleEntityReadWriteTest { private CompanyDao mCompanyDao; private AppDatabase mDb; @Before public void createDb() { Context context = InstrumentationRegistry.getTargetContext(); mDb = Room.inMemoryDatabaseBuilder(context, AppDatabase.class).build(); mCompanyDao = mDb.companyDao(); } @After public void closeDb() throws IOException { mDb.close(); }
125. @RunWith(AndroidJUnit4.class) public class SimpleEntityReadWriteTest { private CompanyDao mCompanyDao; private AppDatabase mDb; @Before public void createDb() { Context context = InstrumentationRegistry.getTargetContext(); mDb = Room.inMemoryDatabaseBuilder(context, AppDatabase.class).build(); mCompanyDao = mDb.companyDao(); } @After public void closeDb() throws IOException { mDb.close(); }
126. @Test public void writeCompanyAndReadInList() throws Exception { Company company = TestUtil.createCompany("DevTalksJr"); mCompanyDao.insertCompany(company); List<Company> byName = mCompanyDao.getCompanies("DevTalksJr"); assertEquals("Should be equal", byName.get(0), company); } // If we use LiveData/RxJava we could use InstantTaskExecutorRule @Rule public InstantTaskExecutorRule instantTaskExecutorRule = new InstantTaskExecutorRule();
127. // mockito public class CompanyRepositoryTest { private CompanyDao companyDao = mock(CompanyDao.class); private CompanyRepository companyRepository; @Before private void setUp() throws Exception { companyRepository = new CompanyRepository(companyDao); } } // espresso @Rule public CountingTaskExecutorRule mCountingTaskExecutorRule = new CountingTaskExecutorRule();
128. // testing migration @Rule public MigrationTestHelper testHelper = new MigrationTestHelper( InstrumentationRegistry.getInstrumentation(), <your_database_class>.class.getCanonicalName(), new FrameworkSQLiteOpenHelperFactory()); // Create the database with version 2 SupportSQLiteDatabase db = testHelper.createDatabase(TEST_DB_NAME, 2); // Validate the migration db = testHelper.runMigrationsAndValidate(TEST_DB_NAME, 4, validateDroppedTables, MIGRATION_1_2, MIGRATION_2_3, MIGRATION_3_4);
129. ForeignKey and Conflict Strategy
130. onDelete/onUpdate = ForeignKey.CASCADE int CASCADE • A "CASCADE" action propagates the delete or update operation on the parent key to each dependent child key. int NO_ACTION //default • When a parent key is modified or deleted from the database, no special action is taken. int RESTRICT • The RESTRICT action means that the application is prohibited from deleting (for onDelete()) or modifying (for onUpdate()) a parent key when there exists one or more child keys mapped to it. int SET_DEFAULT • The "SET DEFAULT" actions are similar to SET_NULL, except that each of the child key columns is set to contain the columns default value instead of NULL. OnConflictStrategy int REPLACE = 1; OnConflict strategy constant to replace the old data and continue the transaction. int ROLLBACK = 2; OnConflict strategy constant to rollback the transaction. int ABORT = 3; //default OnConflict strategy constant to abort the transaction. int FAIL = 4 OnConflict strategy constant to fail the transaction. int IGNORE = 5; OnConflict strategy constant to ignore the conflict.
131. Observable Queries
132. Behind the scenes... 1. SQLite supports triggers that fire whenever a DELETE, UPDATE or INSERT happens in a table. 2. Room creates an InvalidationTracker that uses Observers that track whenever something has changed in the observed tables. 3. Both LiveData and Flowable queries rely on the InvalidationTracker.Observer#onInvalidated notification. When this is received, it triggers a re-query. Flowables -> Flowable#distinctUntilChanged LiveData -> MediatorLiveData
133. Danke schön! Thank you! Magda Miu @magdamiu magdamiu.co
134. Room advantages ❏ Less boilerplate code ❏ SQL queries checked at compile time ❏ No database operations on the main thread ❏ Maintainable code ❏ Migration support
135. Questions
136. ▣ Official docs: https://developer.android.com/topic/libraries/a rchitecture/room.html ▣ Google code samples and Youtube channel: https://github.com/googlesamples/android-ar chitecture-components ▣ CommonsWare: https://commonsware.com/AndroidArch/ ▣ Slides from: https://www.slidescarnival.com/ and gifs from: https://gifer.com Resources