Android Database SQLite tutorial : Insert, update, delete, deleting data base and table

All though we know mobile do not have large memory to save data in comparison of Desktop and Laptop
but data base is highly important in android (and other mobile OS also).
In android we use SQlite database. Sq-lite is light wight and design according to support mobile device limited memory.
First i am listing some basic operation and will explain every thing with example
1) Creating data base - In android, we have an API classes to create it that is  SQLiteOpenHelper to create data base. Best way is to create a different class and extends SQLiteOpenHelper

package com.gu;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

public class DataBaseHub extends SQLiteOpenHelper {

    private static final String dbname = "demo.db";
    private static final int version = 2;
    public static String Ename="Ename";
    public static String Eid="Eid";
    public static String Eadd="Eadd";
    public static String Emp="Emp";
    
    public DataBaseHub(Context context) {
        super(context, dbname, null, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String employee1 = "create table "+Emp+"("+Eid+" integer primary key,"+Ename+" tex                            t,"+Eadd+" text)";
        db.execSQL(employee1);
    }

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

        if (oldVersion < newVersion) {
            String employee1 = "create table emp("+Eid+" integer,"+Ename+" text,"+Eadd+" t            ext)";
            db.execSQL(employee1);
        }
    }
}

2)Creating a data base Table

String employee1 = "create table "+Emp+"("+Eid+" integer primary key,"+Ename+" text,"+Eadd+" text)";
db.execSQL(employee1);

2)Open Data Base - when we open data base in two way either we want to open data base only for reading or for writing into data base. If we open data base in writing mode then we will get access to reading automatically.

  DataBaseHub dbh=new DataBaseHub(activitycontext);                                       

SqliteDatabase db= dbhgetWritableDatabase();                                                    SqliteDatabase db= dbh.getReadableDatabase();

3) Inserting Values into data base - Now we have created data base and open it for performing operation on it so basic operation is to insert value into data base. I have taken table so i will insert values into this table

DataBaseHub dbh=new DataBaseHub(this);
SQLiteDatabase db=dbh.getWritableDatabase();
ContentValues cv=new ContentValues();
cv.put(DataBaseHub.Eid,101);
cv.put(DataBaseHub.Ename,"Tofeeq");
cv.put(DataBaseHub.Eadd,"142,Ananad Delhi");
long i=db.insert(DataBaseHub.Emp, null, cv);
Log.i("Row ID=",String.valueOf(i));

4) Deleting particular row from data base -  Deleting  particular row in data base is damn simple. We have to specify column name( to identify which row we want to delete)

DataBaseHub dbh=new DataBaseHub(this);
SQLiteDatabase db=dbh.getWritableDatabase();
i=db.delete(DataBaseHub.Emp, DataBaseHub.Eid+"=?",new String[]{"101"});
Log.i("Number of Row=",String.valueOf(i)););

5)Updating a row into data base - Updating row into data base is little bit complicated so i will explain there in code

DataBaseHub dbh=new DataBaseHub(this);
SQLiteDatabase db=dbh.getWritableDatabase();
// Create content values that contains the name of the column you want to update and the value you want to assign to it 
ContentValues cv = new ContentValues();
cv.put("my_column", "5");
String where = DataBaseHub.Eid+"=?"; // The where clause to identify which columns to update.
String[] value = { "2" }; // The value for the where clause.
// Update the database (all columns in TABLE_NAME where my_column has a value of 2 will be changed to 5)
db.update(DataBaseHub.Emp, cv, where, value);

6) Reading record from data base - you can read all record by querying a table it will store table records into Cursor. Use cursor method's cusor.movetoNext() cursor.movetToprevious()

DataBaseHub dbh=new DataBaseHub(this);
SQLiteDatabase db = dbh.getReadableDatabase();
Cursor cursor = db.query("Table_name",null,null,null,null,
           null, null, null, null, null);
    if (cursor != null)
    cursor.moveToFirst();
//Now you can read record from cursor easily

7) Deleting Table from data base - Deleting table in android data base is very important. E.g if you are making Music Player. Then you need to create dynamic table while you creating play list

DataBaseHub dbh=new DataBaseHub(this);
SQLiteDatabase db=dbh.getWritableDatabase();
db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);

8) Deleting complete data base - all though we do not need this operation generally but in case if you need to delete your data base you can delete easily by using activity context.

context.deleteDatabase(DATABASE_NAME);