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 ""
    annotationProcessor ""

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":

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")
    public String fullName;

    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")
    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.

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() {
                                public void onCreate(@NonNull SupportSQLiteDatabase db) {
                                    Log.d("MoviesDatabase", "populating with data...");
                                    new PopulateDbAsync(INSTANCE).execute();

        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();

        protected Void doInBackground(Void... voids) {

            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) {
        directorDao = MoviesDatabase.getDatabase(application).directorDao();
        directorsLiveData = directorDao.getAllDirectors();

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

    public void insert(Director... directors) {

    public void update(Director director) {

    public void 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();

    public void onAttach(Context context) {
        this.context = context;
        directorsListAdapter = new DirectorsListAdapter(context);

    public void onCreate(@Nullable Bundle savedInstanceState) {


    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(;
        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>>() {
            public void onChanged(@Nullable List<Director> directors) {

    public void removeData() {
        if (directorsViewModel != null) {

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)) {

        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;
        } 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)) {

        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 =;

                if (!directorToUpdate.fullName.equals(movieDirectorFullName)) {
                    directorToUpdate.fullName = movieDirectorFullName;
        } 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 =;

        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;
        } 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;

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",
        "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": [
          "autoGenerate": true
        "indices": [
            "name": "index_movie_title",
            "unique": false,
            "columnNames": [
            "createSql": "CREATE  INDEX `index_movie_title` ON `${TABLE_NAME}` (`title`)"
            "name": "index_movie_directorId",
            "unique": false,
            "columnNames": [
            "createSql": "CREATE  INDEX `index_movie_directorId` ON `${TABLE_NAME}` (`directorId`)"
        "foreignKeys": [
            "table": "director",
            "onDelete": "CASCADE",
            "onUpdate": "NO ACTION",
            "columns": [
            "referencedColumns": [
        "tableName": "director",
        "fields": [
            "fieldPath": "id",
            "columnName": "did",
            "affinity": "INTEGER",
            "notNull": true
            "fieldPath": "fullName",
            "columnName": "full_name",
            "affinity": "TEXT",
            "notNull": true
        "primaryKey": {
          "columnNames": [
          "autoGenerate": true
        "indices": [
            "name": "index_director_full_name",
            "unique": true,
            "columnNames": [
            "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):


  • 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


  • 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.

230 thoughts on “Database example app with Room ORM”

  1. Pingback: cialis cost
  2. Pingback: viagra vs cialis
  3. Pingback: cialis 20mg pills
  4. Pingback: viagra price cvs
  5. Pingback: safe buy cialis
  6. Pingback: buy cialis 36 hour
  7. Pingback: ycojfvik
  8. Pingback: cialis in europe
  9. Pingback: buy essay help
  10. Pingback: lasix 40
  11. Pingback: ivermectin human
  12. Pingback: prednisolone cream
  13. Pingback: clomid experiences
  14. Pingback: dapoxetine dragon
  15. Pingback: define synthroid
  16. Pingback: neurontin seizures
  17. Pingback: 30 mg paxil
  18. Pingback: buy cialis drug
  19. Pingback: Zakhar Berkut hd
  20. Pingback: buy rush
  21. Pingback: safe buy
  22. Pingback: amoxil USA
  23. Pingback: furosemide 30 mg
  24. Pingback: neurontin 300
  25. Pingback: plaquenil price uk
  26. Pingback: dapoxetine priligy
  27. Pingback: modafinil sale
  28. Pingback: ivermectin 80 mg
  29. Pingback: albuterol 100
  30. Pingback: zithromax purchase
  31. Pingback: lasix order
  32. Pingback: avana
  33. Pingback: ivermectin canada
  34. Pingback: aralen for ra
  35. Pingback: molnupiravir merck
  36. Pingback: baricitinib covid
  37. Pingback: baricitinib 4 mg
  38. Pingback: clomid steroids
  39. Pingback: Anonymous
  40. Pingback: otc tizanidine
  41. Pingback: molnupiravir oral
  42. Pingback: Anonymous
  43. Pingback: Anonymous
  44. Pingback: order ivermectin
  45. Pingback: ivermectin kaufen
  46. Pingback: madridbet
  47. Pingback: meritking
  48. Pingback: meritroyalbet
  49. Pingback: meritroyalbet
  50. Pingback: elexusbet
  51. Pingback: meritroyalbet
  52. Pingback: meritroyalbet
  53. Pingback: viagra or cialis
  54. Pingback: viagra bestellen
  55. Pingback: nih ivermectin
  56. Pingback: cialis genérico
  57. Pingback: baymavi
  58. Pingback: baymavi
  59. Pingback: cialis mg
  60. Pingback: ivermectin 2
  61. Pingback: sildenafil tablets
  62. Pingback: cialis best price
  63. Pingback: tadalafil sandoz
  64. Pingback: tadalafilo
  65. Pingback: tombala siteleri
  66. Pingback: ceallias
  67. Pingback: tadalafil vidal
  68. Pingback: ivermectin tablets
  69. Pingback: child porn
  70. Pingback: meritking
  71. Pingback: eurocasino
  72. Pingback: child porn
  73. Pingback: prednisone
  74. Pingback: buy cialis online
  75. Pingback: real casino online
  76. Pingback: viagra uk
  77. Pingback: elexusbet
  78. Pingback: trcasino
  79. Pingback: cialis pills
  80. Pingback: stromectol 3mg
  81. Pingback: ivermectin 50ml
  82. Pingback: casino online usa
  83. Pingback: ivermectin powder
  84. Pingback: cialis goodrx
  85. Pingback: black viagra pill
  86. Pingback: ivermectin human
  87. Pingback: stromectol sale
  88. Pingback: ivermectin 0.1
  89. Pingback: liquid ivermectin
  90. Pingback: mylan tadalafil
  91. Pingback: order ivermectin
  92. Pingback: ivermectin 6mg otc
  93. Pingback: meritking
  94. Pingback: meritroyalbet
  95. Pingback: meritking
  96. Pingback: buy lasix 40 mg
  97. Pingback: trcasino
  98. Pingback: ivermectin monash
  99. Pingback: eurocasino
  100. Pingback: eurocasino
  101. Pingback: stromectol 3mg
  102. Pingback: ivermectin syrup
  103. Pingback: buy ivermectin nz
  104. Pingback: madridbet
  105. Pingback: cialis overdose
  106. Pingback: hydroxychloroquine
  107. Pingback: cialis at walmart
  108. Pingback: stromectol uk
  109. Pingback: ivermectin buy
  110. Pingback: child porn
  111. Pingback: driponin online
  112. Pingback: mazhor4sezon
  113. Pingback: ivermectin 200 mcg
  114. Pingback: ivermectin 2%
  115. Pingback: psy-
  116. Pingback: Gz92uNNH
  117. Pingback: do-posle-psihologa
  118. Pingback: ivermectin otc
  119. Pingback: bahis siteleri
  120. Pingback: 1vulnerable
  121. Pingback: 3constituting
  122. Pingback: cheap stromectol
  123. Pingback: stromectol 12mg
  124. Pingback: psikholog
  125. Pingback: cialis walmart
  126. Pingback: cialis cost
  127. Pingback: confeitofilm
  128. Pingback: A片

Leave a Reply

Your email address will not be published.