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.

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, "db_name"/*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