Room SQLite demo with CRUD operations in Android

This post includes the complete example of how to store and retrieve data with Room SQLite database storage in Android. Actually Room provides an abstract layer over SQLite which allows us to use SQLite features very easily and in manageable way. Here you can find more information about it: https://developer.android.com/training/data-storage/room/

Now we will add Room dependencies in our module level gradle file.

implementation "android.arch.persistence.room:runtime:1.1.1"
annotationProcessor "android.arch.persistence.room:compiler:1.1.1"

Change to the latest version if available any. Sync the project and you are ready to go.

Now Room SQLite structure consists of 3 things in general.
1. Database
2. Data Access Objects
3. Entities

3. Entity
We will understand them one by one. We will start from the last one. Entity is same as the model class we have in our project which contains the contractors and getter-setter methods. It’s same as our POJO class but it also represents the entity name and columns name for our database. It acts like the database table definition which displays the table name, primary key, column names, etc.

Here is the example for the User table which we are going to use in our example.

@Entity
public class User {
  @PrimaryKey(autoGenerate = true)
  private int id;

  @ColumnInfo(name = "first_name")
  private String firstName;

  @ColumnInfo(name = "last_name")
  private String lastName;

  @ColumnInfo(name = "phone")
  private String phone;

  @ColumnInfo(name = "email")
  private String email;

  @ColumnInfo(name = "address")
  private String address;

  public int getId() {
    return id;
  }

  public void setId(int id) {
    this.id = id;
  }

  public String getFirstName() {
    return firstName;
  }

  public void setFirstName(String firstName) {
    this.firstName = firstName;
  }

  public String getLastName() {
    return lastName;
  }

  public void setLastName(String lastName) {
    this.lastName = lastName;
  }

  public String getPhone() {
    return phone;
  }

  public void setPhone(String phone) {
    this.phone = phone;
  }

  public String getEmail() {
    return email;
  }

  public void setEmail(String email) {
    this.email = email;
  }

  public String getAddress() {
    return address;
  }

  public void setAddress(String address) {
    this.address = address;
  }
}

2. Data Access Object
Now we will create an interface which act as data access object for the User entity. This DAO will communicate with the database using this entity. So basically this DAOs will handle all the queries that we fire in SQLite like insert, update, delete and read. It provides some default functionalities like insert, update, delete as well as also support the regular SQLite queries. Let’s have a look at it.

@Dao
public interface UserDao {
  @Insert
  Long insert(User u);
    
  @Query("SELECT * FROM `User` ORDER BY `id` DESC")
  List<User> getAllUsers();
    
  @Query("SELECT * FROM `User` WHERE `id` =:id")
  User getUser(int id);
    
  @Update
  void update(User u);
    
  @Delete
  void delete(User u);
}

Here as you can see the main benefit of the Room is it will tell you if you misspell any table name of column name so that you don’t have to run the app to detect the issue. It will directly throw you an error if such things happens. So the error are compile time error instead of run time error so it will save a lot of time and makes the code more cleaner and easy to maintain.

1. Database
Now we will create the database class which will contains all the entities and the DAOs from the project. It will look like this.

@Database(entities = {User.class}, version = 1, exportSchema = false)
public abstract class UserDatabase extends RoomDatabase {
  public abstract UserDao userDao();

  private static UserDatabase INSTANCE;

  public static UserDatabase getAppDatabase(Context context) {
    if (INSTANCE == null) {
      INSTANCE = Room.databaseBuilder(context.getApplicationContext(), UserDatabase.class, "user-database").build();
    }
    return INSTANCE;
  }

  public static void destroyInstance() {
    INSTANCE = null;
  }
}

You can include more that one entity and DAO in above class if you have any. In this database class we also initiate the database instance using which we can use the DAOs and perform database operations.

Now we will see the CRUD operations one by one.

1. Insert

//get the database instance
UserDatabase ud = UserDatabase.getAppDatabase(c.get());

//init the entity
User u = new User();
u.setFirstName("John");
u.setLastName("Doe");
u.setPhone("1234567890");
u.setEmail("johndoe@website.com");
u.setAddress("Unknown");

//init dao and perform operation
UserDao dao = ud.userDao();
dao.insert(u);

2. Update

//get the database instance
UserDatabase ud = UserDatabase.getAppDatabase(c.get());

//init the entity
User u = new User();
u.setId(3);
u.setFirstName("Jane");
u.setLastName("Doe");
u.setPhone("0987654321");
u.setEmail("janedoe@website.com");
u.setAddress("Unknown");

//init dao and perform operation
UserDao dao = ud.userDao();
dao.update(u);

3. Delete

//get the database instance
UserDatabase ud = UserDatabase.getAppDatabase(c.get());

//init the entity
User u = new User();
u.setId(3);
u.setFirstName("Jane");
u.setLastName("Doe");
u.setPhone("0987654321");
u.setEmail("janedoe@website.com");
u.setAddress("Unknown");

//init dao and perform operation
UserDao dao = ud.userDao();
dao.delete(u);

4. Read

//get the database instance
UserDatabase ud = UserDatabase.getAppDatabase(c.get());

//init dao and perform operation
UserDao dao = ud.userDao();
  //get all users
List<User> users = dao.getAllUsers();
  //get single user by id
User u = dao.getUser(3);

Note: Please do not perform these operations on main UI thread. Use separate thread for it like AsyncTask. Please checkout sample on github for more information.

You can checkout the whole example on my Github repo, I created the complete UI for it so it will be easy for you to understand it and use it in your projects.

Github repository link: https://github.com/dakshbhatt21/a-computer-engineer