Insert, Read, Delete & Update Operation In SQLite

Android provides different ways to store data locally so using SQLite is one the way to store data. SQLite is a structure query base database, hence we can say it’s a relation database. Android os has its own implementation to perform CRUD (Create, Read, Update, Delete)operations, so Android provides set of classes available in android.database and android.database.sqlite packages.

While using SQLite there could be two different ways to perform different operations like create, read, update and delete. One writing raw queries and another is using parameterized functions or we can say parametrized queries.

Create: Creating a database is very simple in android by using SQLiteOpenHelper class. SQLiteOpenHelper is an abstract class with two abstract methods onCreate(SQLiteDatabase db) and onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) and many more database helpful functions. Whenever we need to create a database we have to extend SQLiteOpenHelper class as follows:

/**A helper class to perform database related queries*/

public class SqliteManager extends SQLiteOpenHelper {
public static final String DATABASE_NAME = "abhiandroid.db";
public static final int version = 1;
 
public SqliteManager(Context context) {
   super(context, DATABASE_NAME, null, version);
  }

@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
   String dbQuery = "CREATE TABLE Items (id INTEGER PRIMARY KEY                          
   AUTOINCREMENT,name TEXT, description TEXT)";
   sqLiteDatabase.execSQL(dbQuery);
  }

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

onCreate(SQLiteDatabase sqLiteDatabase) method is called only for once throughout the application lifecycle, it will be called whenever there is first call to getReadableDatabase() or getWritableDatabase() function available in super SQLiteOpenHelper class so SQLiteOpenHelper class call the onCreate() method after creating database and instantiate SQLiteDatabase object. Database name is passed in constructor call.

onUpgrade(SQLiteDatabase db,int oldVersion, int newVersion) is only called whenever there is a updation in existing version so to update a version we have to increment the value of version variable passed in the superclass constructor. In onUpgrade method we can write queries to perform whatever action is required. In most example you will see that existing table(s) are being dropped and again onCreate() method is being called to create tables again. But it’s not mandatory to do so it all depends upon your requirements. We have to change database version if we have added a new row in the database table in this case if we have requirement that we don’t want to lost existing data in the table then we can write alter table query in the onUpgrade(SQLiteDatabase db,int oldVersion, int newVersion) method.

Likewise if there is no requirement of existing data whenever we upgrade database version then we can write drop table query in onUpgrade(SQLiteDatabase db,int oldVersion, int newVersion) method and call onCreate(SQLiteDatabase sqLiteDatabase) method again to create table again. Remember never call onCreate(SQLiteDatabase sqLiteDatabase) method if you have written alter table query in onUpgrade(SQLiteDatabase db,int oldVersion, int newVersion) method.

Insert, Read, Delete & Update Operation In Sqlite:

To perform insert, read, delete, update operation there are two different ways:

• Write parameterized queries (Recommended)
• Write raw queries

Parameterized Queries: These are those queries which are performed using inbuilt functions to insert, read, delete or update data. These operation related functions are provided in SQLiteDatabase class.

Raw Queries: These are simple sql queries similar to other databases like MySql, Sql Server etc, In this case user will have to write query as text and passed the query string in rawQuery(String sql,String [] selectionArgs) or execSQL(String sql,Object [] bindArgs) method to perform operations.

Important Note: Android documentation don’t recommend to use raw queries to perform insert, read, update, delete operations, always use SQLiteDatabase class’s insert, query, update, delete functions.

Following is an example of raw query to insert data:

public void insertItem(Item item) {
  String query = "INSERT INTO " + ItemTable.NAME + " VALUES (0,?,?)";
  SQLiteDatabase db = getWritableDatabase();
  db.execSQL(query, new String[]{item.name, item.description});
  db.close();
}

While using raw queries we never come to know the result of operation, however with parameterized queries function a value is returned for success or failure of operation.

Insert: To perform insert operation using parameterized query we have to call insert function available in SQLiteDatabase class. insert() function has three parameters like public long insert(String tableName,String  nullColumnHack,ContentValues values) where tableName is name of table in which data to be inserted.

public long insert(String tableName,String  nullColumnHack,ContentValues values)

NullColumnHack may be passed null, it require table column value in case we don’t put column name in ContentValues object so a null value must be inserted for that particular column, values are those values that needs to be inserted- ContentValues is a key-pair based object which accepts all primitive type values so whenever data is being put in ContentValues object it should be put again table column name as key and data as value. insert function will return a long value i.e number of inserted row if successfully inserted, – 1 otherwise.

Here is simple example:

//Item is a class representing any item with id, name and description.
public void addItem(Item item) {
  SQLiteDatabase db = getWritableDatabase();
  ContentValues contentValues = new ContentValues();
  contentValues.put("name",item.name);
   // name - column
   contentValues.put("description",item.description);
   // description is column in items table, item.description has value for description
  db.insert("Items", null, contentValues);//Items is table name
   db.close();
}

Update: Update function is quite similar to insert but it requires two additional parameters, it doesn’t required nullColumnHack. It has total four parameters two are similar to insert function that is tableName and contentValues. Another two are whereClause(String) and whereArgs(String[]).

Update function is available in SQLiteDatabase class it looks as follows:

public int update(String tableName,ContentValues contentValues,String whereClause,String[] whereArgs)

Here whereClause is tell the database where to update data in table, it’s recommended to pass ?s (questions) along with column name in whereClause String. Similarly whereArgs array will contain values for those columns whose against ?s has been put in whereClause. Update function will return number of rows affected if success, 0 otherwise.

Here is simple use of update:

//Item is a class representing any item with id, name and description
public void updateItem(Item item) {
  SQLiteDatabase db = getWritableDatabase();
  ContentValues contentValues = new ContentValues();
  contentValues.put("id", item.id);
  contentValues.put("name", item.name);
  contentValues.put("description", item.description);
  String whereClause = "id=?";
  String whereArgs[] = {item.id.toString()};
  db.update("Items", contentValues, whereClause, whereArgs);
}

Delete: Similar to insert and update, delete function is available in SQLiteDatabase class, So delete is very similar to update function apart from ContentValues object as it’s not required in delete. public int delete(String tableName,String whereClause,String [] whereArgs) function has three parameters these are totally similar to update function’s parameters and are used in same way as in update function.

Here is simple use of delete:

public void deleteItem(Item item) {
SQLiteDatabase db = getWritableDatabase();
String whereClause = "id=?";
String whereArgs[] = {item.id.toString()};
db.delete("Items", whereClause, whereArgs);
}

Here whereClause is optional, passing null will delete all rows in table. delete function will return number of affected row if whereClause passed otherwise will return 0.

Important Note: If you want to remove all rows and require count of deleted ones also then pass 1 as whereClause.

Read(select): Reading from a database table is bit different from other functions like insert,update and delete. SQLiteDatabase class provides query() method to read data from table. query() method is overloaded with different set of parameters. It returns Cursor object so Cursor is a result-set with queried data, it provides different functions really helpful while reading data.

Following are some overloaded query functions:

• public Cursor query (String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)
• public Cursor query (String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy)
• public Cursor query (boolean distinct, String table, String[] columns, String selection, String[] selectionArgs, String groupBy, String having, String orderBy, String limit)

Most of the parameters in query overloaded functions are optional except from table and distinct any of other parameters can be passed as null. if distinct is passed as true Cursor data set will not have any duplicate row.

public ArrayList<Item> readAllItems() {
ArrayList<Item> items = new ArrayList<>();
SQLiteDatabase db = getReadableDatabase();
//see above point 2 function
Cursor cursor = db.query("Items"
, null// columns - null will give all
, null// selection 
, null// selection arguments
, null// groupBy
, null// having 
, null// no need or order by for now;
if (cursor != null) {
  while (cursor.moveToNext()) {
  // move the cursor to next row if there is any to read it's data
           Item item = readItem(cursor);
           items.add(item);
     }
  }
return items;
}

private Item readItem(Cursor cursor) {
  Item item = new Item();
  item.id = cursor.getInt(cursor.getColumnIndex(ItemTable.COL_ID));
  item.name = cursor.getString(cursor.getColumnIndex(ItemTable.COL_NAME));
  item.description = cursor.getString(cursor.getColumnIndex(ItemTable.COL_DESCRIPTION));
  return item;
}

DOWNLOAD THIS FREE eBook!

This free eBook will help you master the learning of Android App Development in Android Studio!

Name

Email

Subscribe & Download eBook Now >>