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
A Computer Engineer 