Room - SQLite Object Mapping Library

Room is a object mapping library for SQLite, made and announced at Google I/O by Google, Inc. It fully supports SQLite and focuses on what SQLite can do while making it dramatically easy to map database queries and objects.

* Room Persistence Library | Android Developers
https://developer.android.com/topic/libraries/architecture/room.html

* SQLiteDatabaseのINSERT/UPDATE文でコンフリクト対応する話 - Qiita
http://qiita.com/Yuki_312/items/9267dc31d2720fe36505

* Room Persistence Libraryを訳してみた(※2017年5月末時点) - へいへいブログ📔
http://shaunkawano.hatenablog.com/entry/2017/05/29/202111

Transcript

1. Room SQLite object mapping library Shohei Kawano @shaunkawano
2. Many apps make data persistent to improve User Experience.
3. Data Persistence for UX • Enabling Offline mode • Preventing redundant API calls for unchangeable data • Loading data from local storage is much faster than loading data from network => Enabling faster data loading
4. What are some options for persisting data in Android?
5. Persistent Storage in Android • Content Providers • Shared Preferences • SQLite
6. Persistent Storage in Android • Content Providers: Publicly sharing data • Shared Preferences: key-value data • SQLite: Structured data
7. Persistent Storage in Android • Content Providers: Publicly sharing data • Shared Preferences: key-value data • SQLite: Structured data
8. Persistent Storage in Android • Content Providers: Publicly sharing data • Shared Preferences: key-value data • SQLite: Structured data → Android provides Core Framework API for SQLite
9. Persistent Storage in Android • Content Providers: Publicly sharing data • Shared Preferences: key-value data • SQLite: Structured data → Android provides Core Framework API for SQLite; works for API level 1~ Let’s read the documentation!
10. https://developer.android.com/training/basics/data-storage/databases.html
11. https://developer.android.com/training/basics/data-storage/databases.html
12. https://developer.android.com/training/basics/data-storage/databases.html Define SQL query statements using String objects
13. https://developer.android.com/training/basics/data-storage/databases.html Declare custom SQLiteOpenHelper class
14. https://developer.android.com/training/basics/data-storage/databases.html Instantiate the helper class
15. Now we are ready to read information from a Database.
16. https://developer.android.com/training/basics/data-storage/databases.html
17. https://developer.android.com/training/basics/data-storage/databases.html Specify FROM WHICH COLUMN to fetch data from
18. https://developer.android.com/training/basics/data-storage/databases.html Specify IN WHAT Condition =(WHERE) to fetch data from
19. https://developer.android.com/training/basics/data-storage/databases.html Specify IN WHAT ORDER to fetch if there are multiple data
20. https://developer.android.com/training/basics/data-storage/databases.html Query data and get result as Cursor object
21. https://developer.android.com/training/basics/data-storage/databases.html
22. https://developer.android.com/training/basics/data-storage/databases.html Finally, move cursor and get information…
23. “This is horrible!”
24. • No compile-time analysis for SQLite statements, so hard to debug, prone to bug. • A lot of boilerplate codes required for converting SQL queries into Application- specific data objects.
26. Room
27. What is Room? - 1 • Introduced in Google I/O 2017 • One of the Architecture Components
 (a.k.a. arch) • Object mapping library for persisting data with SQLite
28. What is Room? - 2 • Fully supports SQLite; focuses on what SQLite can do. • Provide compile-time analysis for SQL queries for better debugging experience and less bugs • Provide Observability and testability
29. What is Room? - 3 • Generate boilerplate codes and implementation by declaring interface with annotations (Similar to Retrofit or Dagger) • No direct “R”elationship between entities; No object references between @Entities • Use @Embedded or @ForeignKey to save or access to minimal data
30. Rest of the Content • Basics of Room • Reading Some of the generated source codes • TypeConverter and observable queries • Conclusion • Extra
31. Not to cover in this talk • About Migration • How to write tests using Room • Other in-depth content
32. Basics of Room
33. Add Room dependencies
34. root/build.gradle allprojects { repositories { jcenter() maven { url 'https://maven.google.com' } } }
35. app/build.gradle compile "android.arch.persistence.room:runtime:1.0.0-alpha1" kapt “android.arch.persistence.room:compiler:1.0.0-alpha1"
36. app/build.gradle compile "android.arch.persistence.room:runtime:1.0.0-alpha1" kapt “android.arch.persistence.room:compiler:1.0.0-alpha1" // RxJava2 support compile "android.arch.persistence.room:rxjava2:1.0.0-alpha1" // Testing support testCompile "android.arch.persistence.room:testing:1.0.0- alpha1"
37. Room Components
38. https://developer.android.com/topic/libraries/architecture/room.html
39. https://developer.android.com/topic/libraries/architecture/room.html
40. @Entity
41. User.kt class User( val uid: Int, val firstName: String, val lastName: String)
42. User.kt @Entity class User( val uid: Int, val firstName: String, val lastName: String)
43. @Entity class User( @PrimaryKey val uid: Int, val firstName: String, val lastName: String) User.kt
44. @Entity class User( @PrimaryKey val uid: Int, val firstName: String, val lastName: String) User.kt
45. @Database(entities = arrayOf(User::class), version = 1) abstract class AppDatabase : RoomDatabase() { abstract fun userDao(): UserDao } AppDatabase.kt
46. @Entity class User( @PrimaryKey val uid: Int, val firstName: String, val lastName: String) User.kt
47. @Entity(tableName = "follower") class User( @PrimaryKey val uid: Int, val firstName: String, val lastName: String) User.kt
48. @Entity(tableName = "follower") class User( @PrimaryKey val uid: Int, @ColumnInfo(name = "first_name") val firstName: String, @ColumnInfo(name = "last_name") val lastName: String) User.kt
49. @Annotations for @Entity
50. @PrimaryKey @Annotations for @Entity
51. User.kt @Entity class User( @PrimaryKey(autoGenerate = true) val uid: Int,

52. User.kt @Entity class User( @PrimaryKey(autoGenerate = true) val uid: Int,
 At least one field annotated with @PrimaryKey is required.
53. User.kt @Entity class User( @PrimaryKey(autoGenerate = true) val uid: Int,
 Room will assign unique ID automatically.
54. @Ignore @Annotations for @Entity
55. User.kt @Entity class User( @PrimaryKey val uid: Int, … @Ignore val profileImage: Bitmap)
56. User.kt @Entity class User( @PrimaryKey val uid: Int, … @Ignore val profileImage: Bitmap)
57. @Embedded @Annotations for @Entity
58. @Entity class User( @PrimaryKey(autoGenerate = true) val uid: Long, …. val address: Address ) class Address( @ColumnInfo(name = "post_code") val postCode: Int, val street: String, val province: String, val city: String )
59. @Entity class User( @PrimaryKey(autoGenerate = true) val uid: Long, …. @Embedded val address: Address ) class Address( @ColumnInfo(name = "post_code") val postCode: Int, val street: String, val province: String, val city: String )
60. @Embedded User table VJE QPTU@DPEF TUSFFU QSPWJODF DJUZ   “Maruyamacho …“ l5PLZPz l4IJCVZBz
61. @Embedded User table VJE QPTU@DPEF TUSFFU QSPWJODF DJUZ   “Maruyamacho …“ l5PLZPz l4IJCVZBz 6TFSLU "EESFTTLU "EESFTTLU "EESFTTLU "EESFTTLU
62. Multiple @Embedded fields @Annotations for @Entity
63. @Entity class User( @PrimaryKey(autoGenerate = true) val uid: Long, …. @Embedded val address: Address ) class Address( @ColumnInfo(name = "post_code") val postCode: Int, val street: String, val state: String, val city: String )
64. @Entity class User( @PrimaryKey(autoGenerate = true) val uid: Long, …. @Embedded val primaryAddress: Address @Embedded val secondaryAddress: Address ) class Address( @ColumnInfo(name = "post_code") val postCode: Int, val street: String, val state: String, val city: String )
65. @Entity class User( @PrimaryKey(autoGenerate = true) val uid: Long, …. @Embedded val primaryAddress: Address @Embedded (prefix = “secondary_”) val secondaryAddress: Address ) class Address( @ColumnInfo(name = "post_code") val postCode: Int, val street: String, val state: String, val city: String )
66. @Embedded User Table VJE QPTU@DPE F TUSFFU QSPWJODF DJUZ TFDPOEB SZ@QPTU@ DPEF TFDPOEB SZ@TUSFFU ʜ 1 1500043 “Maruyam acho…“ “Tokyo” “Shibuya” 1500043 “Kamiyam acho…”
67. Parameters for @Entity
68. @Entity( primaryKeys = arrayOf("first_name", "last_name"), tableName = "follower", indices = arrayOf(Index("last_name"), Index(value = "first_name", unique = true)), inheritSuperIndices = false ) @Entity Parameters
69. @ForeignKey Parameters for @Entity
70. @ForeignKey on Book.kt @Entity(foreignKeys = arrayOf(ForeignKey( entity = User::class, parentColumns = arrayOf("id"), childColumns = arrayOf("user_id"), onDelete = ForeignKey.CASCADE ))) class Book( @PrimaryKey val bookId: Long, @ColumnInfo(name = "user_id") val userId: Long, val title: String)
71. @ForeignKey on Book.kt @Entity(foreignKeys = arrayOf(ForeignKey( entity = User::class, parentColumns = arrayOf("id"), childColumns = arrayOf("user_id"), onDelete = ForeignKey.CASCADE ))) class Book( @PrimaryKey val bookId: Long, @ColumnInfo(name = "user_id") val userId: Long, val title: String)
72. @ForeignKey on Book.kt @Entity(foreignKeys = arrayOf(ForeignKey( entity = User::class, parentColumns = arrayOf("id"), childColumns = arrayOf("user_id"), onDelete = ForeignKey.CASCADE ))) class Book( @PrimaryKey val bookId: Long, @ColumnInfo(name = "user_id") val userId: Long, val title: String)
73. Relationship in Room
74. No object references between @Entities
75. SQLite Object Mapping library Room is
76. Book.kt val title: String val description: String val author: Author
77. Book.kt val title: String val description: String val author: Author
79. { Book { Book { Book { Book { Book
80. { { { { { Book Book Book Book Book
81. authorNameTextView.setText(user.getAuthor().getName());
82. authorNameTextView.setText(user.getAuthor().getName()); Querying SQLite table in UI thread may create junk…
83. https://developer.android.com/topic/libraries/architecture/room.html
84. https://developer.android.com/topic/libraries/architecture/room.html
85. @Dao
86. UserDao.kt @Dao interface UserDao { @Insert fun insertAll(users: List<User>) @Update fun update(users: List<User>) @Delete fun delete(user: User) @Query("SELECT * FROM user") fun getAll(): List<User>
87. UserDao.kt @Dao interface UserDao { @Insert fun insertAll(users: List<User>) @Update fun update(users: List<User>) @Delete fun delete(user: User) @Query("SELECT * FROM user") fun getAll(): List<User>
88. UserDao.kt @Dao interface UserDao { @Insert fun insertAll(users: List<User>) @Update fun update(users: List<User>) @Delete fun delete(user: User) @Query("SELECT * FROM user") fun getAll(): List<User>
89. UserDao.kt @Dao interface UserDao { @Insert fun insertAll(users: List<User>) @Update fun update(users: List<User>) @Delete fun delete(user: User) @Query("SELECT * FROM user") fun getAll(): List<User>
90. UserDao.kt @Dao interface UserDao { @Insert fun insertAll(users: List<User>) @Update fun update(users: List<User>) @Delete fun delete(user: User) @Query("SELECT * FROM user") fun getAll(): List<User>
91. UserDao.kt @Dao interface UserDao { @Insert fun insertAll(users: List<User>) @Update fun update(users: List<User>) @Delete fun delete(user: User) @Query("SELECT * FROM user") fun getAll(): List<User>
92. UserDao.kt @Dao interface UserDao { @Insert fun insertAll(users: List<User>) @Update fun update(users: List<User>) @Delete fun delete(user: User) @Query("SELECT * FROM user") fun getAll(): List<User>
93. UserDao.kt @Dao interface UserDao { @Insert fun insertAll(users: List<User>) @Update fun update(users: List<User>) @Delete fun delete(user: User) @Query("SELECT * FROM user") fun getAll(): List<User> Very similar to Retrofit’s service interfaces
94. UserDao.kt @Dao interface UserDao { @Insert fun insertAll(users: List<User>) @Update fun update(users: List<User>) @Delete fun delete(user: User) @Query("SELECT * FROM user") fun getAll(): List<User>
95. @Database(entities = arrayOf(User::class), version = 1) abstract class AppDatabase : RoomDatabase() { abstract fun userDao(): UserDao } AppDatabase.kt
96. UserDao.kt @Dao interface UserDao { @Insert fun insertAll(users: List<User>) @Update fun update(users: List<User>) @Delete fun delete(user: User) @Query("SELECT * FROM USER") fun getAll(): List<User>
97. UserDao_Impl.java @Override public List<User> getAll() { final String _sql = "SELECT * FROM USER"; final RoomSQLiteQuery _statement = RoomSQLiteQuery.acquire(_sql, 0); final Cursor _cursor = __db.query(_statement); try { final int _cursorIndexOfUid = _cursor.getColumnIndexOrThrow("uid"); …
98. UserDao_Impl.java @Override public List<User> getAll() { final String _sql = "SELECT * FROM USER"; final RoomSQLiteQuery _statement = RoomSQLiteQuery.acquire(_sql, 0); final Cursor _cursor = __db.query(_statement); try { final int _cursorIndexOfUid = _cursor.getColumnIndexOrThrow("uid"); …
99. UserDao_Impl.java final List<User> _result = new ArrayList<User>(_cursor.getCount()); while(_cursor.moveToNext()) { final User _item; final int _tmpUid; _tmpUid = _cursor.getInt(_cursorIndexOfUid); … _item = new User(…); _result.add(_item); }
100. UserDao_Impl.java final List<User> _result = new ArrayList<User>(_cursor.getCount()); while(_cursor.moveToNext()) { final User _item; final int _tmpUid; _tmpUid = _cursor.getInt(_cursorIndexOfUid); … _item = new User(…); _result.add(_item); }
101. UserDao_Impl.java final List<User> _result = new ArrayList<User>(_cursor.getCount()); while(_cursor.moveToNext()) { final User _item; final int _tmpUid; _tmpUid = _cursor.getInt(_cursorIndexOfUid); … _item = new User(…); _result.add(_item); }
102. UserDao_Impl.java … return _result; } finally { _cursor.close(); _statement.release(); }
103. … return _result; } finally { _cursor.close(); _statement.release(); } UserDao_Impl.java
104. Methods using @Query
105. UserDao.kt @Dao interface UserDao { @Query("SELECT * FROM user") fun getAll(): List<User>
106. UserDao.kt @Dao interface UserDao { @Query("SELECT * FROM user") fun getAll(): List<User>
107. Bind Parameters Methods using @Query
108. UserDao.kt @Dao interface UserDao { @Query("SELECT * FROM user") fun getAll(): List<User> @Query("SELECT * FROM user WHERE age > :minAge" ) fun loadAllUsersOlderThan(minAge: Int): List<User>
109. @Query("SELECT * FROM user WHERE age BETWEEN :minAge AND :maxAge") public User[] loadAllUsersBetweenAges(int minAge, int maxAge); UserDao.kt @Dao interface UserDao { @Query("SELECT * FROM user") fun getAll(): List<User> @Query("SELECT * FROM user WHERE age > :minAge" ) fun loadAllUsersOlderThan(minAge: Int): List<User>
110. @Query("SELECT * FROM user WHERE first_name LIKE :search" + "OR last_name LIKE :search") fun findUserWithName(search: String): List<User> UserDao.kt @Dao interface UserDao { @Query("SELECT * FROM user") fun getAll(): List<User> @Query("SELECT * FROM user WHERE age > :minAge" ) fun loadAllUsersOlderThan(minAge: Int): List<User>
111. Returning Subsets of Columns Methods using @Query
112. NameTaple.kt @Entity class NameTuple( @ColumnInfo(name = "first_name") val firstName: String, @ColumnInfo(name = "last_name") val lastName: String )
113. NameTaple.kt @Entity class NameTuple( @ColumnInfo(name = "first_name") val firstName: String, @ColumnInfo(name = "last_name") val lastName: String )
114. UserDao.kt @Dao interface UserDao { @Query("SELECT first_name, last_name FROM user") fun loadFullName(): List<NameTuple>
115. UserDao.kt @Dao interface UserDao { @Query("SELECT first_name, last_name, middleName FROM user") fun loadFullName(): List<NameTuple>
116. UserDao.kt @Dao interface UserDao { @Query("SELECT first_name, last_name, FROM user") fun loadFullName(): List<NameTuple>
117. OnConflictStrategy for @Insert, @Update
118. UserDao.kt @Dao interface UserDao { @Insert(onConflict = OnConflictStrategy.ROLLBACK) fun insertAll(users: List<User>) @Update(onConflict = OnConflictStrategy.REPLACE) fun updateUsers(users: List<User>)
119. @Retention(SOURCE) … public @interface OnConflictStrategy { int REPLACE = 1; int ROLLBACK = 2; int ABORT = 3; int FAIL = 4; int IGNORE = 5; } OnConflictStrategy.java
120. @Retention(SOURCE) … public @interface OnConflictStrategy { int REPLACE = 1; int ROLLBACK = 2; int ABORT = 3; int FAIL = 4; int IGNORE = 5; } OnConflictStrategy.java https://sqlite.org/lang_conflict.html Based on SQLite Conflict Algorithm
121. https://developer.android.com/topic/libraries/architecture/room.html
122. https://developer.android.com/topic/libraries/architecture/room.html
123. @Database
124. @Database(entities = arrayOf(User::class, Address::class), version = 1) abstract class AppDatabase : RoomDatabase() { abstract fun userDao(): UserDao } AppDatabase.kt
125. @Database(entities = arrayOf(User::class, Address::class), version = 1) abstract class AppDatabase : RoomDatabase() { abstract fun userDao(): UserDao } AppDatabase.kt
126. @Database(entities = arrayOf(User::class, Address::class), version = 1) abstract class AppDatabase : RoomDatabase() { abstract fun userDao(): UserDao } AppDatabase.kt
127. AppDatabase.kt appDatabase = Room.databaseBuilder(this, AppDatabase::class.java, "room-sample-database") .build();
128. AppDatabase.kt appDatabase = Room.databaseBuilder(this, AppDatabase::class.java, "room-sample-database") .build(); // InMemoryDatabase appDatabase = Room.inMemoryDatabaseBuilder(this, AppDatabase::class.java).build();
129. Room Extensions
130. @TypeConverters
131. @Entity class User( @PrimaryKey(autoGenerate = true) val uid: Int, @ColumnInfo(name = "first_name") val firstName: String, @ColumnInfo(name = "last_name") val lastName: String, @JvmField val birthday: LocalDateTime ) User.kt
132. class LocalDateTimeConverter { @TypeConverter fun fromTimestamp(value: Long?): LocalDateTime? { return if (value == null) null else LocalDateTime.ofEpochSecond(value, 0, ZoneOffset.UTC) } @TypeConverter fun dateToTimestamp(date: LocalDateTime?): Long? { return date?.toEpochSecond(ZoneOffset.UTC) …. LocalDateTimeConverter.kt
133. class LocalDateTimeConverter { @TypeConverter fun fromTimestamp(value: Long?): LocalDateTime? { return if (value == null) null else LocalDateTime.ofEpochSecond(value, 0, ZoneOffset.UTC) } @TypeConverter fun dateToTimestamp(date: LocalDateTime?): Long? { return date?.toEpochSecond(ZoneOffset.UTC) …. LocalDateTimeConverter.kt
134. @Database(entities = arrayOf(User::class, Address::class), version = 1) @TypeConverters(LocalDateTimeConverter::class) abstract class AppDatabase : RoomDatabase() { abstract fun userDao(): UserDao } AppDatabase.kt
135. private final LocalDateTimeConverter __localDateTimeConverter = new LocalDateTimeConverter(); ... final LocalDateTime _tmpBirthday; final Long _tmp; ... _tmpBirthday = __localDateTimeConverter.fromTimestamp(_tmp); UserDao_Impl.java
136. Observable Queries
137. @Query("SELECT * FROM user WHERE first_name LIKE :search" + "OR last_name LIKE :search") fun findUserWithName(search: String): List<User> UserDao.kt @Dao interface UserDao { @Query("SELECT * FROM user") fun getAll(): List<User>
138. @Query("SELECT * FROM user WHERE first_name LIKE :search" + "OR last_name LIKE :search") fun findUserWithName(search: String): LiveData<List<User>> UserDao.kt @Dao interface UserDao { @Query("SELECT * FROM user") fun getAll(): Flowable<List<User>>
139. @Query("SELECT * FROM user WHERE first_name LIKE :search" + "OR last_name LIKE :search") fun findUserWithName(search: String): LiveData<List<User>> UserDao.kt @Dao interface UserDao { @Query("SELECT * FROM user") fun getAll(): Flowable<List<User>>
140. Conclusion
141. Conclusion • Room provides simple and customizable APIs with raw SQL queries to solve current problems on Framework APIs • Room forbids direct references between entities and forces to access/fetch as minimal data as possible • Room supports LiveData and Flowable integration for observability
142. Extra
143. Extra • You may not need Room if current DB or ORM library works better for you • Compared to other Architecture Components, Room is more stable and ready to be used • Room currently does not support query builders - “That’s a Android Studio’s job and we are working on it”
144. Room SQLite object mapping library Shohei Kawano @shaunkawano
145. Room SQLite object mapping library Shohei Kawano @shaunkawano Thank You!