Persisting Data on SQLite using Room

Persisting data in
SQLite using Room
+Nelson Glauber
@nglauber 
www.nglauber.com.br

Room Database
Get DAO
Data Access Objects
Get entities from
database
Persist changes
back to database
Entities
get / set  
ﬁeld values
Application

apply plugin: 'kotlin-kapt'
...
dependencies {
def room_version = "1.1.0"
implementation "android.arch.persistence.room:runtime:$room_version"
kapt "android.arch.persistence.room:compiler:$room_version"
}

import android.arch.persistence.room.*
@Entity
data class Event(
@PrimaryKey (autoGenerate = true)
var id : Long = 0,
var name : String = "",
@ColumnInfo(name = "info")
var description : String = ""
)

@Entity(tableName = "tbEvent")
data class Event(
var id : Long = 0,
)

@Entity(indices = [Index("name", unique = true)])
data class Event(
var id : Long = 0,
)

@Dao
interface EventDao {
@Insert(onConflict = OnConflictStrategy.REPLACE)
fun insert(event: Event): Long
@Update
fun update(event: Event): Int
@Delete
fun delete(vararg event: Event): Int
@Query("SELECT * FROM Event WHERE name LIKE :name ORDER BY name")
fun eventsByName(name: String = "%"): List<Event>
@Query("SELECT * FROM Event WHERE id = :id")
fun eventById(id: Long): Event?
}

@Database(entities = [Event::class], version = 1)
abstract class AppDatabase : RoomDatabase() {
abstract fun eventDao() : EventDao
}

val db = Room.databaseBuilder(appContext, AppDatabase::class.java, "eventDb")
.build()
val dao = db.eventDao()
val event = Event(0, "Google I/O 2018", "Google's annual conference")
val id = dao.insert(event)
val googleIo = dao.eventById(id)
Log.d("NGVL", "${googleIo.id} ${googleIo.name} - ${googleIo.description}")
val events = dao.eventsByName()
events.forEach {
Log.d("NGVL", "${it.id} ${it.name} - ${it.description}")
}

java.lang.RuntimeException: Unable to start activity ComponentInfo{nglauber.com.br.roomtest/
nglauber.com.br.roomtest.MainActivity}:
java.lang.IllegalStateException: Cannot access database on the main thread since it may
potentially lock the UI for a long period of time.
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2830)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2909)
at android.app.ActivityThread.-wrap11(Unknown Source:0)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1606)
at android.os.Handler.dispatchMessage(Handler.java:105)
at android.os.Looper.loop(Looper.java:164)
at android.app.ActivityThread.main(ActivityThread.java:6592)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.Zygote$MethodAndArgsCaller.run(Zygote.java:240)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:769)
Caused by: java.lang.IllegalStateException: Cannot access database on the main thread since it
may potentially lock the UI for a long period of time.
at android.arch.persistence.room.RoomDatabase.assertNotMainThread(RoomDatabase.java:204)
at android.arch.persistence.room.RoomDatabase.beginTransaction(RoomDatabase.java:251)
at nglauber.com.br.roomtest.EventDao_Impl.insert(EventDao_Impl.java:85)
...
😱😱😱

val db = Room.databaseBuilder(appContext, AppDatabase::class.java, "eventDb")
.allowMainThreadQueries()
.build()
"

SQLite data types
• INTEGER
• REAL
• TEXT
• BLOB

data class Event(
var id : Long = 0,
var description : String = "",
var date: Date = Date()
)

import android.arch.persistence.room.TypeConverter
import java.util.*
class Converters {
@TypeConverter
fun fromTimestamp(value: Long?): Date? {
return value?.let { Date(value) }
}
@TypeConverter
fun dateToTimestamp(date: Date?): Long? {
return date?.time ?: 0
}
}
@TypeConverters(Converters::class)
}

java.lang.RuntimeException: Unable to start activity ComponentInfo{nglauber.com.br.roomtest/
nglauber.com.br.roomtest.MainActivity}:
java.lang.IllegalStateException: Room cannot verify the data integrity.
Looks like you have changed schema but forgot to update the version number.
You can simply fix this by increasing the version number.
at android.app.ActivityThread.performLaunchActivity(ActivityThread.java:2830)
at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2909)
at android.app.ActivityThread.-wrap11(Unknown Source:0)
at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1606)
at android.os.Handler.dispatchMessage(Handler.java:105)
at android.os.Looper.loop(Looper.java:164)
at android.app.ActivityThread.main(ActivityThread.java:6592)
at java.lang.reflect.Method.invoke(Native Method)
at com.android.internal.os.Zygote$MethodAndArgsCaller.run(Zygote.java:240)
at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:769)

object Migration_1_2 : Migration(1, 2) {
override fun migrate(db: SupportSQLiteDatabase) {
db.execSQL("ALTER TABLE Event " +
"ADD COLUMN date INTEGER NOT NULL DEFAULT 0")
}
}
val db = Room.databaseBuilder(this.applicationContext,
AppDatabase::class.java,
"eventDb")
.addMigrations(Migration_1_2)
.build()
}

val db = Room.databaseBuilder(this.applicationContext,
AppDatabase::class.java,
"eventDb")
.fallbackToDestructiveMigration()
.build()

data class Event(
var id : Long = 0,
var date: Date = Date(),
@Embedded
var location: Address? = null
)
data class Address (
var street: String? = null,
var state: String? = null,
var city: String? = null
)
Event
id
name
info
date
street
state
city

data class Address (
var street: String? = null,
var state: String? = null,
var city: String? = null
)
data class Event(
var id : Long = 0,
var date: Date = Date(),
@Embedded(prefix = "location_")
var location: Address? = null
)
Event
id
name
info
date
location_street
location_state
location_city

@Entity(foreignKeys = [
ForeignKey(entity = Event::class,
parentColumns = arrayOf("id"),
childColumns = arrayOf("eventId"),
onDelete = CASCADE
)
])
data class Topic(
@PrimaryKey(autoGenerate = true)
var id: Long = 0,
var name: String = "",
var eventId: Long = 0
)

@Entity(...)
data class Event(
...
@Ignore
var topics: List<Topic>? = null
)

@Dao
interface TopicDao {
fun insertTopics(topics: List<Topic>)
@Query("SELECT * FROM Topic WHERE eventId = :eventId")
fun topicsByEvent(eventId: Long): List<Topic>
}
@Database(entities = [Event::class, Topic::class], version = 1)
abstract fun topicDao() : TopicDao
}

val tdc = Event(0, "TDC", "The Dev Conf",
location = Address("Rua Casa do Ator, 275", "SP", "São Paulo")
)
val eventId = dao.insert(tdc)
val topicDao = db.topicDao()
topicDao.insertTopics(listOf(
Topic(name = "Android", eventId = eventId),
Topic(name = "Mobile", eventId = eventId)
))

@Dao
...
fun insertTopics(topics: List<Topic>)
@Transaction
fun insertEventWithTopics(event: Event) {
val id = insert(event)
event.topics.run {
this.forEach {
it.eventId = id
}
insertTopics(this)
}
}
}

dao.insertEventWithTopics(
Event().apply {
name = "TDC"
description = "The Dev Conf"
location =
Address("Rua Casa do Ator, 275", "SP", "São Paulo")
topics = listOf(
Topic(name = "Android"),
Topic(name = "Mobile")
)
}
)

The “issue” with  
Foreign Keys
• When should I load the children records?
• Lazy loading is a good approach? 🤔

class EventWithTopics {
@Embedded
var event: Event = Event()
@Relation(parentColumn = "id",
entityColumn = "eventId",
entity = Topic::class)
var topics: List<Topic> = emptyList()
}

@Entity(...)
open class Event(
...
@Ignore
open var topics: List<Topic> = emptyList()
)
class EventWithTopics: Event() {
@Relation(parentColumn = "id",
entityColumn = "eventId",
entity = Topic::class)
override var topics: List<Topic> = emptyList()
}

@Dao
...
@Query("SELECT * FROM Event ORDER BY name")
fun eventsWithTopics(): List<EventWithTopics>
}
val eventsWithTopics = dao.eventsWithTopics()
eventsWithTopics.forEach { event ->
// Do something with event and topics
}

dependencies {
def lifecycle_version = "1.1.1"
implementation "android.arch.lifecycle:livedata:$lifecycle_version"
...
}

@Dao
...
fun eventsWithTopics(): LiveData<List<EventWithTopics>>
}
dao.eventsWithTopics().observe(this, Observer { eventListWithTopics ->
// Do something...
})

dependencies {
def room_version = "1.1.0"
def rxjava2_version = "2.1.13"
def rx2android_version = "2.0.2"
implementation "android.arch.persistence.room:rxjava2:$room_version"
implementation "io.reactivex.rxjava2:rxjava:$rxjava2_version"
implementation "io.reactivex.rxjava2:rxandroid:$rx2android_version"
}

@Dao
…
fun eventsWithTopics(): Flowable<List<EventWithTopics>>
}
dao.eventsWithTopics()
.subscribeOn(Schedulers.io())
.observeOn(AndroidSchedulers.mainThread())
.subscribe { events ->
// Do something
}

A quick recap!
✓ Room uses annotations instead of reflection to abstract the SQL
operations.
✓ Room uses the DAO to deal with Entities to persist data in the Database.
✓ Room verify the SQL statements at compile time and has auto-complete
(in Android Studio 3.2).
✓ Room does not force your entities to extends of a base class.
✓ Supports programmatically migration.
✓ Provides an easy way to deal with transactions and relation between
entities.
✓ Supports Live Data and RXJava2.

+Nelson Glauber
@nglauber 
www.nglauber.com.br
Thank you!

Persisting Data on SQLite using Room

More Related Content

What's hot

Similar to Persisting Data on SQLite using Room

More from Nelson Glauber Leal

Persisting Data on SQLite using Room