• 8 hours
  • Hard

Free online content available in this course.



Got it!

Last updated on 6/12/19

Manipulate your data using the DAO

Log in or subscribe for free to enjoy all this course has to offer!

Now that our entities are defined, it would be nice to be able to manipulate them through our SQLite database. For example, we would like to be able to add a thing to do into our database, update it or delete it... in short, do any of the CRUD actions!

Well, that’s just what we’ll do thanks to a design pattern called Data Access Object (DAO).

What is the DAO?

Hold up, what's a design pattern? Why are you talking about DAO all of a sudden? 😦

Good question! A design pattern is a template, or set of software best practices, intended to help developers design the best solution to a common software problem. No need to reinvent the wheel!

In our case, we have a problem: What is the best way to access data located in our SQLite database? In other words, the cleanest approach in object-oriented programming?

That’s where DAO comes in! 😁 This pattern allows you to group together access to persistent data by separating them into distinct classes, rather than being dispersed throughout the code. What if we applied this to our two tables User and  Item?

To do this, create a package called database/, then a sub-package DAO/, and put the following two interfaces (note, not classes 😉) into it: ItemDao  and userDao.

Interface database/dao/ItemDao.kt
interface ItemDao {
@Query("SELECT * FROM Item WHERE userId = :userId")
fun getItems(userId: Long): LiveData<List<Item>>
@Query("SELECT * FROM Item WHERE userId = :userId")
fun getItemsWithCursor(userId: Long): Cursor
fun insertItem(item: Item): Long
fun updateItem(item: Item): Int
@Query("DELETE FROM Item WHERE id = :itemId")
fun deleteItem(itemId: Long): Int

Room requires you to use a particular format to create a DAO, asking you to create an interface for each DAO. Here, we have created the interface  ItemDao whose objective and responsibility will be to consolidate all the CRUD actions for the item table.

In order to indicate the interface as a DAO class, we have added the annotation @Dao at the top of it. Then we created the four CRUD actions via the following methods:

  • getItems(): Used to retrieve the list of things to do (item) for a user. We've used the annotation @Query  to define the method as a SQL query. We return a list of items of the type LiveData, which I will explain in detail in the next part of this course. 🙂

  • insertItem(): Used to create a new thing to do (item) thanks to the annotation @Insert. Note that we passed an item object directly as a parameter of the method. This object will not need to have a defined identifier, because Room will generate one for us!!!

  • updateItem(): Used to update an existing thing to do (item) using the annotation  @Update. Also note that we passed an Item object to it directly as a parameter. That object absolutely must have a defined identifier, so Room can find in the DB and update it. 😉

  • deleteItem(): Used to delete an existing thing to do (item) in the DB. Note here that we have reused the annotation  @Query, because we need to create a slightly more advanced SQL query. You can also go directly to an Item object and use the annotation  @Delete to delete it.

Interface database/dao/UserDao.kt:
interface UserDao {
@Insert(onConflict = OnConflictStrategy.REPLACE)
fun createUser(user: User)
@Query("SELECT * FROM User WHERE id = :userId")
fun getUser(userId: Long): LiveData<User>

Here we have defined the DAO interface dedicated to manipulating the table  User. We've added just two methods: One to create a new user (createUser), and one to retrieve a user (getUser). Notice that we have added the parameter onConflict = OnConflictStrategy.REPLACE to the annotation @Insert,  to overwrite an existing user with the same id as the one you want to insert.

And that's all! 😀 Lastly, we still need to configure an important class whose role is to bind together all the classes/interfaces we previously created and configure our database!

I’ll leave it to you to create the class SaveMyTripDatabase in the package database/.

Interface database/SaveMyTripDatabase.kt:
Class database/SaveMyTripDatabase.kt:
@Database(entities = arrayOf(Item::class, User::class), version = 1, exportSchema = false)
abstract class SaveMyTripDatabase : RoomDatabase() {
// --- DAO ---
abstract fun itemDao(): ItemDao
abstract fun userDao(): UserDao
companion object {
// --- SINGLETON ---
private var INSTANCE: SaveMyTripDatabase? = null
// --- INSTANCE ---
fun getInstance(context: Context): SaveMyTripDatabase? {
if (INSTANCE == null) {
synchronized(SaveMyTripDatabase::class.java) {
if (INSTANCE == null) {
INSTANCE = Room.databaseBuilder(context.applicationContext,
SaveMyTripDatabase::class.java, "MyDatabase.db")
// ---
private fun prepopulateDatabase(): RoomDatabase.Callback {
return object : RoomDatabase.Callback() {
override fun onCreate(db: SupportSQLiteDatabase) {
val contentValues = ContentValues()
contentValues.put("id", 1)
contentValues.put("username", "Philippe")
contentValues.put("urlPicture", "https://oc-user.imgix.net/users/avatars/15175844164713_frame_523.jpg?auto=compress,format&q=80&h=100&dpr=2")
db.insert("User", OnConflictStrategy.IGNORE, contentValues)

Here we've created an abstract class, inheriting from RoomDatabase , and defined by the annotation @Database. That same annotation will list the various tables (called entities).

Inside this class, we have declared our two DAO interfaces. Next, we created a method,   getInstance() inside a companion object to create a singleton of our class SaveMyTripDatabase.

Sooo... what’s a singleton? 

It's another design pattern! 😉 Despite all our efforts, we've encountered a software problem: How to create the class responsible for our database just once and get only a single reference instance.

Ah. But why do we need a single instance of our class managing our database?

Simply put, if you have multiple instances of your database, you’ll potentially be allowing several instances to manipulate the single file of your database at the same time. If two instances open the SQLite file, and they are trying to edit it at the same time, it might lead to a slight problem! 

That’s why we create a singleton! It will create a new  RoomDatabase object using its builder  Room.databaseBuilder, and will create a file that will contain our SQLite database. If that method is ever called back later, it will only return the reference of our database.

As you can see, we have added the addCallback method to our builder, which will enable us to fill it with a test user thanks to the method that we have created below, prepopulateDatabase.

There you go! Our SQLite database is configured correctly thanks to Room. Now all that’s left is for us to try it out, which is what we’re going to do in the next chapter! 😉

Let's recap!

  • A design pattern is a group of best practices, like a template, which helps developers design better software solutions. 

  • The DAO is a design pattern that groups together access to persistent data via separate classes, rather than scattering them throughout the code. 

  • Room requires you to create an interface for each DAO. 

  • Singletons allow you to create a class once and obtain a single, unique instance of that class which can be used throughout your program.

Example of certificate of achievement
Example of certificate of achievement