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