movies_screenshot

Database example app with Room ORM

With Android Architecture Components getting a stable 1.0 version, there are now no excuses of not using it 🙂 Along with handling lifecycle events, realtime data updates in UI (ViewModel with LiveData) and pagination of loaded data (Paging), comes Room – small, yet powerful SQLite ORM. In this post I’m gonna demonstrate its core capabilities on an example Android application.

Remember those times implementing SQLiteOpenHelper and checking SQL queries in run-time? Good news is that you don’t have to do it anymore! Room performs compile-time checks on your SQL queries and you don’t have to write any SQLite code which is not in a direct relation with your data queries. Great, lets use it!

First of all, Room is a part of Architecture Components, which means it works really well with ViewModel, LiveData and Paging (but does not depend on them!). Also, RxJava and Kotlin are perfectly fine too. In order to add Room to the project, I’m adding the following lines in app’s build.gradle file:

    // Room
    implementation "android.arch.persistence.room:runtime:1.0.0"
    annotationProcessor "android.arch.persistence.room:compiler:1.0.0"

Check for the latest library version here.

Additionally, provide a location of DB’s schema in defaultConfig scope. This way you can always check how it looks, maybe decide to modify your tables once you notice something in scheme.

   javaCompileOptions {
        annotationProcessorOptions {
            arguments = ["room.schemaLocation":
                                 "$projectDir/schemas".toString()]
        }
    }

In my example application (jump to the source code if you wish) I have one Activity with BottomNavigationView, holding two tabs – Movies and Directors. There is a RecyclerView to show the lists of each category, FloatingActionButton – to add a new movie/director, and two options in ActionBar’s overflow menu – Delete list data and Re-create database. Also, when clicking on a list item, you can update its data.

movies room example screenshot

I use two tables – Director and Movie. Same director can be the author of many movies, so it’s one-to-many relationship. I assume the full name of the director to be unique and mandatory. Movie’s title doesn’t have to be unique, but needs to be provided as well.

Director’s class looks like below:

@Entity(tableName = "director",
        indices = {@Index(value = "full_name", unique = true)})
public class Director {

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "did")
    public int id;

    @ColumnInfo(name = "full_name")
    @NonNull
    public String fullName;

    @Ignore
    public int age;

    public Director(@NonNull String fullName) {
        this.fullName = fullName;
    }
}

Data structure classes such as Director should be annotated with @Entity to become a table in database. You can provide optional parameters such as  tableName and indices. Adding an index on a column which will be used in search queries, speeds the search.  I have checked this with full_name value and the search was indeed faster. Read more on indices here.

Note the unique = true for full_name index. Room does not have @Unique annotation, but you can enforce uniqueness on a index, so it’s one way to do it.

Each table needs to have at least one primary key. In our case it’s the id. @ColumnInfo(name = “did”) renames id to did for a column’s name. I don’t want to bother with providing it myself, hence autoGenerate = true for Room to do that.

I also have an ignored field age with @Ignore annotation. This will exclude it from being added into database’s schema.

Movie’s class is not much different, except it contains foreignKeys param. Remember our assumption that Director can have many movies? We define this relation with foreignKeys, by providing parentColumns and childColumns values (there is an alternative to foreignKeys and it’s a @Relation annotation). You can provide two more params: onUpdate and onDelete. By default their values are 1 or ForeignKey.NO_ACTION. By writing onDelete = ForeignKey.CASCADE I basically tell Room to delete a movie if its director got removed from the database. Other possible values are: RESTRICT, SET_NULL or SET_DEFAULT.

@Entity(tableName = "movie",
        foreignKeys = @ForeignKey(entity = Director.class,
                parentColumns = "did",
                childColumns = "directorId",
                onDelete = ForeignKey.CASCADE),
        indices = {@Index("title"), @Index("directorId")})
public class Movie {

    @PrimaryKey(autoGenerate = true)
    @ColumnInfo(name = "mid")
    public int id;

    @ColumnInfo(name = "title")
    @NonNull
    public String title;

    @ColumnInfo(name = "directorId")
    public int directorId;

    public Movie(@NonNull String title, int directorId) {
        this.title = title;
        this.directorId = directorId;
    }
}

That’s it for the entity classes. Now lets define methods that we’re gonna use to manipulate our data. Create a DirectorDao interface with @Dao annotation (it also can be an abstract class instead of interface). Room provides four different annotations: @Insert, @Update, @Delete and @Query. For insert and update you can provide OnConflictStrategy value, customizing the behavior in case of arisen conflict. By default the transaction is aborted, but in our case, we ignore it. As a parameter of insert(), update() and delete() you can provide a single object, a few of them or a list. In case you need an id of a newly inserted row(s), just return long/long[]. For update() you can return int, indicating how many rows were affected.

In case of DirectorDao, I return long for insert(Director director), as I will need the id of a newly created row for a movie.

There are a few queries I will need as well: findDirectorById(), findDirectorByName() and getAllDirectors(). full_name column is used for searching and ordering. It is a good candidate for an index.

Notice the return type of getAllDirectors(). I  wrap the list of results in LiveData, as I would like the list to update automatically when the underlined data changes.

@Dao
public interface DirectorDao {
    @Query("SELECT * FROM director WHERE did = :id LIMIT 1")
    Director findDirectorById(int id);

    @Query("SELECT * FROM director WHERE full_name = :fullName LIMIT 1")
    Director findDirectorByName(String fullName);

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    long insert(Director director);

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    void insert(Director... directors);

    @Update(onConflict = OnConflictStrategy.IGNORE)
    void update(Director director);

    @Query("DELETE FROM director")
    void deleteAll();

    @Query("SELECT * FROM director ORDER BY full_name ASC")
    LiveData<List<Director>> getAllDirectors();
}

MovieDao is very similar, so I won’t talk about it. Now, that we have entity and dao classes created, lets create a database class itself. It should be abstract and extend RoomDatabase. With @Database annotation we provide the array of entities and a version. Usually, there’s no need for more than one instance of a DB, thus make it a singleton.

For db initialization, use Room.databaseBuilder(). Just for simplifying the code, I’ve added allowMainThreadQueries(). If not provided, each DB transaction will throw

java.lang.IllegalStateException: Cannot access database on the main thread since it may potentially lock the UI for a long period of time

I’m also adding a callback hooked to db’s creation (there’s also another one for opening), so some example data is populated at the first start of the app.

What you definitely need to do in RoomDatabase class, is to provide abstract methods for getting DAOs. In our case it’s movieDao() and directorDao().

@Database(entities = {Movie.class, Director.class}, version = 1)
public abstract class MoviesDatabase extends RoomDatabase {
    private static MoviesDatabase INSTANCE;
    private static final String DB_NAME = "movies.db";

    public static MoviesDatabase getDatabase(final Context context) {
        if (INSTANCE == null) {
            synchronized (MoviesDatabase.class) {
                if (INSTANCE == null) {
                    INSTANCE = Room.databaseBuilder(context.getApplicationContext(),
                            MoviesDatabase.class, DB_NAME)
                            .allowMainThreadQueries() // SHOULD NOT BE USED IN PRODUCTION !!!
                            .addCallback(new RoomDatabase.Callback() {
                                @Override
                                public void onCreate(@NonNull SupportSQLiteDatabase db) {
                                    super.onCreate(db);
                                    Log.d("MoviesDatabase", "populating with data...");
                                    new PopulateDbAsync(INSTANCE).execute();
                                }
                            })
                            .build();
                }
            }
        }

        return INSTANCE;
    }

    public void clearDb() {
        if (INSTANCE != null) {
            new PopulateDbAsync(INSTANCE).execute();
        }
    }

    public abstract MovieDao movieDao();

    public abstract DirectorDao directorDao();

    private static class PopulateDbAsync extends AsyncTask<Void, Void, Void> {
        private final MovieDao movieDao;
        private final DirectorDao directorDao;

        public PopulateDbAsync(MoviesDatabase instance) {
            movieDao = instance.movieDao();
            directorDao = instance.directorDao();
        }

        @Override
        protected Void doInBackground(Void... voids) {
            movieDao.deleteAll();
            directorDao.deleteAll();

            Director directorOne = new Director("Adam McKay");
            Director directorTwo = new Director("Denis Villeneuve");
            Director directorThree = new Director("Morten Tyldum");

            Movie movieOne = new Movie("The Big Short", (int) directorDao.insert(directorOne));
            final int dIdTwo = (int) directorDao.insert(directorTwo);
            Movie movieTwo = new Movie("Arrival", dIdTwo);
            Movie movieThree = new Movie("Blade Runner 2049", dIdTwo);
            Movie movieFour = new Movie("Passengers", (int) directorDao.insert(directorThree));

            movieDao.insert(movieOne, movieTwo, movieThree, movieFour);

            return null;
        }
    }
}

Next, don’t forget about the ViewModel classes. Director and Movie have their own ones. Really they’re just wrappers around DAOs methods.

public class DirectorsViewModel extends AndroidViewModel {
    private DirectorDao directorDao;
    private LiveData<List<Director>> directorsLiveData;

    public DirectorsViewModel(@NonNull Application application) {
        super(application);
        directorDao = MoviesDatabase.getDatabase(application).directorDao();
        directorsLiveData = directorDao.getAllDirectors();
    }

    public LiveData<List<Director>> getDirectorList() {
        return directorsLiveData;
    }

    public void insert(Director... directors) {
        directorDao.insert(directors);
    }

    public void update(Director director) {
        directorDao.update(director);
    }

    public void deleteAll() {
        directorDao.deleteAll();
    }
}

The fragment to display Directors’s list, looks like the following:

public class DirectorsListFragment extends Fragment {
    private DirectorsListAdapter directorsListAdapter;
    private DirectorsViewModel directorsViewModel;
    private Context context;

    public static DirectorsListFragment newInstance() {
        return new DirectorsListFragment();
    }

    @Override
    public void onAttach(Context context) {
        super.onAttach(context);
        this.context = context;
        directorsListAdapter = new DirectorsListAdapter(context);
    }

    @Override
    public void onCreate(@Nullable Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);

        initData();
    }

    @Nullable
    @Override
    public View onCreateView(LayoutInflater inflater, @Nullable ViewGroup container, @Nullable Bundle savedInstanceState) {
        View view = inflater.inflate(R.layout.fragment_directors, container, false);
        RecyclerView recyclerView = view.findViewById(R.id.recyclerview_directors);
        recyclerView.setAdapter(directorsListAdapter);
        recyclerView.addItemDecoration(new DividerItemDecoration(context, DividerItemDecoration.VERTICAL));
        recyclerView.setLayoutManager(new LinearLayoutManager(context));
        return view;
    }

    private void initData() {
        directorsViewModel = ViewModelProviders.of(this).get(DirectorsViewModel.class);
        directorsViewModel.getDirectorList().observe(this, new Observer<List<Director>>() {
            @Override
            public void onChanged(@Nullable List<Director> directors) {
                directorsListAdapter.setDirectorList(directors);
            }
        });
    }

    public void removeData() {
        if (directorsViewModel != null) {
            directorsViewModel.deleteAll();
        }
    }
}

What’s interesting in this code, is the DirectorsViewModel, that I take from ViewModelProviders and attach an observer on it. Whenever onChanged() gets called, new directors list is set on the list adapter and we see the change in UI.

Depending if we came from a list item click  (the directorFullNameExtra is present then) or FloatingActionButton click, there’s only one method to save the director:

    
    private void saveDirector(String fullName) {
        if (TextUtils.isEmpty(fullName)) {
            return;
        }

        DirectorDao directorDao = MoviesDatabase.getDatabase(context).directorDao();

        if (directorFullNameExtra != null) {
            // clicked on item row -> update
            Director directorToUpdate = directorDao.findDirectorByName(directorFullNameExtra);
            if (directorToUpdate != null) {
                if (!directorToUpdate.fullName.equals(fullName)) {
                    directorToUpdate.fullName = fullName;
                    directorDao.update(directorToUpdate);
                }
            }
        } else {
            directorDao.insert(new Director(fullName));
        }
    }

I don’t need to check if the name is unique when inserting, as Room is doing it for me. It will ignore the transaction if the same name already exists in DB (thanks to OnConflictStrategy.IGNORE). I could do the same with update actually 😉

The method for saving movie is a bit more complicated, because I need an id of either newly inserted or updated or just the old director from DB. Once I have it, I provide it to new movie, or update an existing one.

 
 private void saveMovie(String movieTitle, String movieDirectorFullName) {
        if (TextUtils.isEmpty(movieTitle) || TextUtils.isEmpty(movieDirectorFullName)) {
            return;
        }

        DirectorDao directorDao = MoviesDatabase.getDatabase(context).directorDao();
        MovieDao movieDao = MoviesDatabase.getDatabase(context).movieDao();

        int directorId = -1;
        if (movieDirectorFullNameExtra != null) {
            // clicked on item row -> update
            Director directorToUpdate = directorDao.findDirectorByName(movieDirectorFullNameExtra);
            if (directorToUpdate != null) {
                directorId = directorToUpdate.id;

                if (!directorToUpdate.fullName.equals(movieDirectorFullName)) {
                    directorToUpdate.fullName = movieDirectorFullName;
                    directorDao.update(directorToUpdate);
                }
            }
        } else {
            // we need director id for movie object; in case director is already in DB,
            // insert() would return -1, so we manually check if it exists and get
            // the id of already saved director
            Director newDirector = directorDao.findDirectorByName(movieDirectorFullName);
            if (newDirector == null) {
                directorId = (int) directorDao.insert(new Director(movieDirectorFullName));
            } else {
                directorId = newDirector.id;
            }
        }

        if (movieTitleExtra != null) {
            // clicked on item row -> update
            Movie movieToUpdate = movieDao.findMovieByTitle(movieTitleExtra);
            if (movieToUpdate != null) {
                if (!movieToUpdate.title.equals(movieTitle)) {
                    movieToUpdate.title = movieTitle;
                    if (directorId != -1) {
                        movieToUpdate.directorId = directorId;
                    }
                    movieDao.update(movieToUpdate);
                }
            }
        } else {
            // we can have many movies with same title but different director
            Movie newMovie = movieDao.findMovieByTitle(movieTitle);
            if (newMovie == null) {
                movieDao.insert(new Movie(movieTitle, directorId));
            } else {
                if (newMovie.directorId != directorId) {
                    newMovie.directorId = directorId;
                    movieDao.update(newMovie);
                }
            }
        }
    }

Now, download the app, click Run, add some directors and movies. Check if the movie is removed when you delete its director. Check if update works, add your custom fields and queries. Check how database schema looks in movies-room\app\schemas\com.lomza.moviesroom.db.MoviesDatabase\1.json.

{
  "formatVersion": 1,
  "database": {
    "version": 1,
    "identityHash": "b54788198efea4a644d83b8e8aad1cd4",
    "entities": [
      {
        "tableName": "movie",
        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`mid` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `title` TEXT NOT NULL, `directorId` INTEGER NOT NULL, FOREIGN KEY(`directorId`) REFERENCES `director`(`did`) ON UPDATE NO ACTION ON DELETE CASCADE )",
        "fields": [
          {
            "fieldPath": "id",
            "columnName": "mid",
            "affinity": "INTEGER",
            "notNull": true
          },
          {
            "fieldPath": "title",
            "columnName": "title",
            "affinity": "TEXT",
            "notNull": true
          },
          {
            "fieldPath": "directorId",
            "columnName": "directorId",
            "affinity": "INTEGER",
            "notNull": true
          }
        ],
        "primaryKey": {
          "columnNames": [
            "mid"
          ],
          "autoGenerate": true
        },
        "indices": [
          {
            "name": "index_movie_title",
            "unique": false,
            "columnNames": [
              "title"
            ],
            "createSql": "CREATE  INDEX `index_movie_title` ON `${TABLE_NAME}` (`title`)"
          },
          {
            "name": "index_movie_directorId",
            "unique": false,
            "columnNames": [
              "directorId"
            ],
            "createSql": "CREATE  INDEX `index_movie_directorId` ON `${TABLE_NAME}` (`directorId`)"
          }
        ],
        "foreignKeys": [
          {
            "table": "director",
            "onDelete": "CASCADE",
            "onUpdate": "NO ACTION",
            "columns": [
              "directorId"
            ],
            "referencedColumns": [
              "did"
            ]
          }
        ]
      },
      {
        "tableName": "director",
        "createSql": "CREATE TABLE IF NOT EXISTS `${TABLE_NAME}` (`did` INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, `full_name` TEXT NOT NULL)",
        "fields": [
          {
            "fieldPath": "id",
            "columnName": "did",
            "affinity": "INTEGER",
            "notNull": true
          },
          {
            "fieldPath": "fullName",
            "columnName": "full_name",
            "affinity": "TEXT",
            "notNull": true
          }
        ],
        "primaryKey": {
          "columnNames": [
            "did"
          ],
          "autoGenerate": true
        },
        "indices": [
          {
            "name": "index_director_full_name",
            "unique": true,
            "columnNames": [
              "full_name"
            ],
            "createSql": "CREATE UNIQUE INDEX `index_director_full_name` ON `${TABLE_NAME}` (`full_name`)"
          }
        ],
        "foreignKeys": []
      }
    ],
    "setupQueries": [
      "CREATE TABLE IF NOT EXISTS room_master_table (id INTEGER PRIMARY KEY,identity_hash TEXT)",
      "INSERT OR REPLACE INTO room_master_table (id,identity_hash) VALUES(42, \"b54788198efea4a644d83b8e8aad1cd4\")"
    ]
  }
}

Of course, there are topics I didn’t mention, like DB migration, or testing, or how to use Room with data binding, RxJava, etc, but there are many good articles on that (check the ones below).

Just to sum up, here are some advantages and disadvantages or using Room (this might change in the nearest future, when Google adds more features and improvements):

Pluses:

  • smooth integration with LiveData
  • it’s an official Google’s SQLite ORM
  • compile-time checks for SQL statements
  • super easy database creation with Room builder

Minuses:

  • no default values for columns
  • no unique annotation
  • no cascading with @Relation annotation; can be applied to List or Set only
  • not so many features, (yet 🙂

Further reading:

Room Training Guide

7 Pro-tips for Room

Android Architecture Components: Room — Relationships

Understanding Migrations with Room

Squeezing Performance from SQLite: Indexes? Indexes!

Lots of examples with Room

Like and share:

Published by

Tonia Tkachuk

I'm an Android Developer, writing code for living and for fun. Love beautiful apps with a clean code inside. Enjoy travelling and reading.