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

CRUD functions in SQLite in Android

This article depicts the CRUD(create, read, update, delete) functions on a database table in SQLite. Here I take the example of ‘user’ class which has few properties.

Screenshot_2016-08-31-23-34-41
CRUD in SQLite

First you need to create User class with its constructor and getter-setter methods.

public class User {
  private long id;
  private String firstName;
  private String lastName;
  private int age;

  public User() {
  }

  public long getId() {
    return id;
  }

  public void setId(long 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 int getAge() {
    return age;
  }

  public void setAge(int age) {
    this.age = age;
  }
}

Now we will initialize DataBaseHelper class which will perform all the database related operation.

First we initial DatabaseHelper class by extending SQLiteOpenHelper.

public class DatabaseHelperNew extends SQLiteOpenHelper {

  @Override
  public void onCreate(SQLiteDatabase db) {

  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

  }

  public DatabaseHelperNew(Context c) {
    super(context, &quot;db_name&quot;/*db name*/, null, 1/*version*/);
  }
}

Now we initialize onCreate and onUpgrade methods with appropriate queries.

public class DatabaseHelperNew extends SQLiteOpenHelper {

  static String TABLE_USER = "user";
  static String KEY_USER_ID = "user_id";
  static String KEY_USER_FIRSTNAME = "user_first_name";
  static String KEY_USER_LASTNAME = "user_last_name";
  static String KEY_USER_AGE = "user_age";

  String CREATE_TABLE_USER = "CREATE TABLE " + TABLE_USER + "(" + KEY_USER_ID + " INTEGER PRIMARY KEY," + KEY_USER_FIRSTNAME + " TEXT," + KEY_USER_LASTNAME + " TEXT," + KEY_USER_AGE + " INTEGER" + ")";

  @Override
  public void onCreate(SQLiteDatabase db) {
    db.execSQL(CREATE_TABLE_USER);
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_USER);
    onCreate(db);
  }

  public DatabaseHelperNew(Context c) {
    super(context, "db_name"/*db name*/, null, 1/*version*/);
  }
}

Now we will see CRUD functions one by one.

Create

//create user
public long createUser(User u) {
  SQLiteDatabase db = this.getWritableDatabase();
  ContentValues values = new ContentValues();
  values.put(KEY_USER_FIRSTNAME, u.getFirstName());
  values.put(KEY_USER_LASTNAME, u.getLastName());
  values.put(KEY_USER_AGE, u.getAge());
  long id = db.insert(TABLE_USER, null, values);
  db.close();
  return id;
}

Update

//update user
public void updateUser(User u) {
  SQLiteDatabase db = this.getWritableDatabase();
  ContentValues values = new ContentValues();
  values.put(KEY_USER_FIRSTNAME, u.getFirstName());
  values.put(KEY_USER_LASTNAME, u.getLastName());
  values.put(KEY_USER_AGE, u.getAge());
  db.update(TABLE_USER, values, KEY_USER_ID + " = ?", new String[]{String.valueOf(u.getId())});
  db.close();
}

Delete

//delete user
public void deleteUser(User u){
  SQLiteDatabase db = this.getWritableDatabase();
  db.delete(TABLE_USER, KEY_USER_ID + " = ?", new String[]{String.valueOf(u.getId())});
  db.close();
}

Read

//read all users
public ArrayList<User> readUsers(){
  ArrayList<User> users = new ArrayList<>();
  String selectQuery = "SELECT * FROM " + TABLE_USER;
  SQLiteDatabase db = this.getReadableDatabase();
  Cursor c = db.rawQuery(selectQuery, null);

  if (c.moveToFirst()) {
    do {
      User u = new User();
      u.setId(c.getInt((c.getColumnIndex(KEY_USER_ID))));
      u.setFirstName(c.getString(c.getColumnIndex(KEY_USER_FIRSTNAME)));
      u.setLastName(c.getString(c.getColumnIndex(KEY_USER_LASTNAME)));
      u.setAge(c.getInt(c.getColumnIndex(KEY_USER_AGE)));
      users.add(u);
    } while (c.moveToNext());
  }
  db.close();
  return users;
}

//read single user
public User readUser(long id){
  ArrayList<User> users = new ArrayList<>();
  String selectQuery = "SELECT * FROM " + TABLE_USER + " WHERE " + KEY_USER_ID + " = ?";
  SQLiteDatabase db = this.getReadableDatabase();
  Cursor c = db.rawQuery(selectQuery, new String[]{String.valueOf(id)});

  if (c.moveToFirst()) {
    do {
      User u = new User();
      u.setId(c.getInt((c.getColumnIndex(KEY_USER_ID))));
      u.setFirstName(c.getString(c.getColumnIndex(KEY_USER_FIRSTNAME)));
      u.setLastName(c.getString(c.getColumnIndex(KEY_USER_LASTNAME)));
      u.setAge(c.getInt(c.getColumnIndex(KEY_USER_AGE)));
      users.add(u);
    } while (c.moveToNext());
  }
  db.close();
  return users.get(0);
}

Now the complete DatabaseHelper file looks like below.

public class DatabaseHelper extends SQLiteOpenHelper {

  static String TABLE_USER = "user";
  static String KEY_USER_ID = "user_id";
  static String KEY_USER_FIRSTNAME = "user_first_name";
  static String KEY_USER_LASTNAME = "user_last_name";
  static String KEY_USER_AGE = "user_age";

  String CREATE_TABLE_USER = "CREATE TABLE " + TABLE_USER + "(" + KEY_USER_ID + " INTEGER PRIMARY KEY," + KEY_USER_FIRSTNAME + " TEXT," + KEY_USER_LASTNAME + " TEXT," + KEY_USER_AGE + " INTEGER" + ")";

  @Override
  public void onCreate(SQLiteDatabase db) {
    db.execSQL(CREATE_TABLE_USER);
  }

  @Override
  public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL("DROP TABLE IF EXISTS " + TABLE_USER);
    onCreate(db);
  }

  public DatabaseHelper(Context c) {
    super(c, "db_name"/*db name*/, null, 1/*version*/);
  }

  //create user
  public long createUser(User u) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(KEY_USER_FIRSTNAME, u.getFirstName());
    values.put(KEY_USER_LASTNAME, u.getLastName());
    values.put(KEY_USER_AGE, u.getAge());
    long id = db.insert(TABLE_USER, null, values);
    db.close();
    return id;
  }

  //update user
  public void updateUser(User u) {
    SQLiteDatabase db = this.getWritableDatabase();
    ContentValues values = new ContentValues();
    values.put(KEY_USER_FIRSTNAME, u.getFirstName());
    values.put(KEY_USER_LASTNAME, u.getLastName());
    values.put(KEY_USER_AGE, u.getAge());
    db.update(TABLE_USER, values, KEY_USER_ID + " = ?", new String[]{String.valueOf(u.getId())});
    db.close();
  }

  //delete user
  public void deleteUser(User u){
    SQLiteDatabase db = this.getWritableDatabase();
    db.delete(TABLE_USER, KEY_USER_ID + " = ?", new String[]{String.valueOf(u.getId())});
    db.close();
  }

  //read all users
  public ArrayList<User> readUsers(){
    ArrayList<User> users = new ArrayList<>();
    String selectQuery = "SELECT * FROM " + TABLE_USER;
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor c = db.rawQuery(selectQuery, null);

    if (c.moveToFirst()) {
      do {
        User u = new User();
        u.setId(c.getInt((c.getColumnIndex(KEY_USER_ID))));
        u.setFirstName(c.getString(c.getColumnIndex(KEY_USER_FIRSTNAME)));
        u.setLastName(c.getString(c.getColumnIndex(KEY_USER_LASTNAME)));
        u.setAge(c.getInt(c.getColumnIndex(KEY_USER_AGE)));
        users.add(u);
      } while (c.moveToNext());
    }
    db.close();
    return users;
  }

  //read single user
  public User readUser(long id){
    ArrayList<User> users = new ArrayList<>();
    String selectQuery = "SELECT * FROM " + TABLE_USER + " WHERE " + KEY_USER_ID + " = ?";
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor c = db.rawQuery(selectQuery, new String[]{String.valueOf(id)});

    if (c.moveToFirst()) {
      do {
        User u = new User();
        u.setId(c.getInt((c.getColumnIndex(KEY_USER_ID))));
        u.setFirstName(c.getString(c.getColumnIndex(KEY_USER_FIRSTNAME)));
        u.setLastName(c.getString(c.getColumnIndex(KEY_USER_LASTNAME)));
        u.setAge(c.getInt(c.getColumnIndex(KEY_USER_AGE)));
        users.add(u);
      } while (c.moveToNext());
    }
    db.close();
    return users.get(0);
  }

  //user class
  public class User {
    private long id;
    private String firstName;
    private String lastName;
    private int age;

    public User() {
    }

    public long getId() {
      return id;
    }

    public void setId(long 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 int getAge() {
      return age;
    }

    public void setAge(int age) {
      this.age = age;
    }
  }
}

Now we will take a look at how to use above class to perform various database operation.

DatabaseHelper dh;
User u;

dh = new DatabaseHelper(MainActivity.this);
u = dh.new User();

u.setFirstName("John");
u.setLastName("Doe");
u.setAge(25);

//create user
long id = dh.createUser(u);

u.setId(id);
//update user
u.setAge(30);
dh.updateUser(u);

//read all users
ArrayList<User> alUser = dh.readUsers();

//read single user
User u1 = dh.readUser(u.getId());

//delete user
dh.deleteUser(u);

Download Source Code: https://github.com/dakshbhatt21/a-computer-engineer