SQLite with RecyclerView

"SQLite with RecyclerView"

   I hope you enjoying with android coding. Today we are going to study about how to perform CRUD operation with SQLite using RecyclerView.
   SQLite is a opensource database to store text data in a device. The SQLite comes with built in database implementation in android. There is no need to established any kind of connections like JDBC etc. You can implement ist by extending SQLiteOpenHelper class.

Ok, let's start with CREATE table , INSERT data and SELECT data operation.

   In previous blog we have study about RecyclerView implementation. So, here i'am going to post only about SQLite database.

   Let's start with creating table, First you need to create one class and extend with SQLiteOpenHelper as shown in below code snippet. There are two methods are override by extendning SQLiteOpenHelper class, namely onCreate and onUpgrade method.

   In onCreate method we have to create table in Database. As per shown in below code, using SQLiteDatabase instance call execSQL method and pass create table query as a parameter. The execSQL execute the single SQL statement that is not a SELECT,INSERT,UPDATE,DELETE.

   The onUpgrade called when need to upgrade database version and drop old table if exists. It need to upgrade the new schema version.

   See the following code for better understanding of creating table.

public class SQLiteHelper extends SQLiteOpenHelper {

private static final String DatabaseName = "ContactDB";
    private static final int DBVersion = 1;
    private static final String TableName = "ContactInfo";
    private static final String ID = "ID";
    public static final String COL1 = "NAME";
    public static final String COL2 = "NUMBER";
    public static final String COL3 = "EMAIL";

public SQLiteHelper(Context context) {
        super(context, DatabaseName, null, DBVersion);
    }

@Override
    public void onCreate(SQLiteDatabase db) {

String CREATE_TABLE = "CREATE TABLE " + TableName + " ( " + COL1 + " TEXT," + COL2 + " TEXT," + COL3 + " TEXT )";

db.execSQL(CREATE_TABLE);   // Execute create table query.

}

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

db.execSQL("DROP TABLE IF EXISTS " + TableName);  //Drop table if alredy exists
        onCreate(db); //Call onCreate method for creating new table
    }

After creating table, now we are going to insert data. For insert data in table first we have get writable database instance, as following line.

SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();

See the following code snippet of insertData method, The ContentValues class is used for store the set of values. To insert data in table call insert method using SQLiteDatabase instance and pass table name and content values as a prameter as shown in following code snippet.

To get data from table we have run select query as a rawQuery and it returns result in cursor.

For deleting data from table i have createdeleteData method and pass one parameter is name for deleting based on name selection. In below code shown how to delete data from database table.

The following three methods insertData,getAllData and deleteData place in the above created class (SQLiteHelper).

//insert data
    public long insertData(String name, String number, String email) {

SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();

ContentValues contentValues = new ContentValues();

contentValues.put(COL1, name);
        contentValues.put(COL2, number);
        contentValues.put(COL3, email);

long result = sqLiteDatabase.insert(TableName, null, contentValues); //insert data in table and returns long value. if positive then inserted data successfully
        return result;
    }

//get all data
    public Cursor getAllData() {

String selectAll = "SELECT * FROM " + TableName;

SQLiteDatabase sqLiteDatabase = this.getReadableDatabase();
        Cursor cursor = sqLiteDatabase.rawQuery(selectAll, null);
        if (cursor != null) {
            return cursor;
        } else {
            return null;
        }

}

//Delete data by name
    public boolean deleteData(String name) {

SQLiteDatabase sqLiteDatabase = this.getWritableDatabase();
        long result = sqLiteDatabase.delete(TableName, COL1 + "=?", new String[]{name});
        if (result > 0) {
            return true;
        } else {
            return false;
        }
    }

   To access method of

   Lets, create one application for understanding of CREATE, INSERT, SELECT AND DELETE operation on SQLite database.

   In following code, I have created one demo app in which we can add contact info like number and email id and added call event and email event. Download the above code and just import it in your android studio and run it.

Screenshot :