SQLite with Android Easy to Understand Tutorial that covers Select, Insert, Update and Delete

There are many tutorials that you would find Online for "SQLite with Android Programming" Most of them are really confusing, too lengthy or very descriptive yet do not cover the basic insert, update, delete and select operations at once.

I will try to keep this simple (hope you have a good idea of SQL) and cover all basic operations needed to work with your Android App.

We will create an Android Application that pretty simple, NO! ListView to make it more confusion for beginners.

We will only have a single EditText field, and three buttons Insert, Update and Delete and a TextView to display the results.

So our SQLite database that has a single table called "records_db" and has just two filed

1. id (auto incremented primary key)
2. record

1. Create the database if not present named "record_db".
2. Create the table "records_master".
3. Perform Insert operations.
4. Perform Update operations.
5. Perform Delete operations.
6. Perform Select operations.

1. User can enter a String Value and click Insert, this record will get inserted into the SQLite database.
2. User can enter ID,TEXT (e.g. 1,Sam) and click Update button to update the value of any record for a given ID.
3. User can enter ID and click Delete button to delete the record.

Note: After every operation, we will call the Select Query and re-print the DB records in the TextView.

So let's get started, we create our simple android Activity first.

<RelativeLayout xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="match_parent"
    android:layout_height="match_parent"

    android:background="#eee"
    android:paddingLeft="@dimen/activity_horizontal_margin"
    android:paddingRight="@dimen/activity_horizontal_margin"
    android:paddingTop="@dimen/activity_vertical_margin"
    android:paddingBottom="@dimen/activity_vertical_margin"
    tools:context=".MainActivity">


    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="@string/title"
        android:id="@+id/textView"
        android:textColor="#222"
        android:textSize="22sp"

        android:layout_alignParentTop="true"
        android:layout_centerHorizontal="true" />

    <EditText
        android:layout_width="fill_parent"
        android:layout_height="wrap_content"
        android:id="@+id/inputField"
        android:hint="@string/hintText"
        android:textColor="#272"
        android:layout_marginTop="40dp"
        android:layout_below="@+id/textView"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true" />

    <TextView
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:textAppearance="?android:attr/textAppearanceSmall"
        android:text="@string/resultHeader"
        android:id="@+id/resultHeader"
        android:layout_marginTop="15dp"
        android:layout_below="@+id/insert"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true" />

    <Button
        style="?android:attr/buttonStyleSmall"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginTop="5dp"
        android:text="@string/insert"
        android:id="@+id/insert"
        android:onClick="insertRecord"
        android:layout_below="@+id/inputField"
        android:layout_alignParentLeft="true"
        android:layout_alignParentStart="true" />

    <Button
        style="?android:attr/buttonStyleSmall"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:layout_marginTop="5dp"
        android:text="@string/update"
        android:onClick="updateRecord"
        android:id="@+id/update"
        android:layout_below="@+id/inputField"
        android:layout_centerHorizontal="true" />

    <Button
        style="?android:attr/buttonStyleSmall"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"
        android:text="@string/delete"
        android:id="@+id/delete"
        android:onClick="deleteRecord"
        android:layout_marginTop="5dp"
        android:layout_below="@+id/inputField"
        android:layout_alignParentRight="true"
        android:layout_alignParentEnd="true" />


    <ScrollView
        android:layout_width="match_parent"
        android:layout_height="200dp"
        android:background="#ddd"
        android:layout_alignParentBottom="true"
        android:layout_below="@+id/resultHeader"
        android:fillViewport="false"
        android:id="@+id/scroll">

        <TextView
            android:layout_width="match_parent"
            android:layout_height="200dp"
            android:padding="10dp"
            android:textColor="#000"

            android:text="@string/result"
            android:id="@+id/result"
            android:layout_below="@+id/resultHeader"
            android:layout_alignParentLeft="true"
            android:scrollbarStyle="insideInset"
            android:scrollbars="vertical"
            android:overScrollMode="always"

            android:layout_alignParentStart="true"
            android:layout_alignParentBottom="true" />
    </ScrollView>
</RelativeLayout>

<?xml version="1.0" encoding="utf-8"?>
<resources>

    <string name="app_name">Android SQLite Example</string>
    <string name="hello_world">Hello world!</string>
    <string name="action_settings">Settings</string>
    <string name="title">Android + SQLite Tutorial</string>
    <string name="hintText">Enter Record</string>
    <string name="resultHeader">Result :</string>
    <string name="insert">Insert</string>
    <string name="update">Update</string>
    <string name="delete">Delete</string>
    <string name="result">No records added!</string>

</resources>

package db;

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

/**
 * Created by code2care on 02/02/15.
 *
 *
 * Create static variables
 *
 * 1. Database name
 * 2. Table name
 * 3. Field 1 : Id
 * 4. Filed 2 : record
 *
 * Create a table with ID as primary key and autoincrement
 *
 * We need to Override onCreate and upgrade methods
 *
 */
public class SQLiteDbHelper extends SQLiteOpenHelper {

    public static final String DB_NAME = "records_db";
    public static final String TABLE_NAME = "record_master";
    public static final String FIELD_ID = "id";
    public static final String FIELD_RECORD = "record";


    public SQLiteDbHelper(Context context) {
        super(context, DB_NAME, null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String CREATE_TABLE = "Create table " + TABLE_NAME + " (" + FIELD_ID + " INTEGER Primary Key AUTOINCREMENT, " + FIELD_RECORD + " TEXT)";
        db.execSQL(CREATE_TABLE);

    }

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

      //  db.execSQL("Drop table if exists " + TABLE_NAME);
        onCreate(db);

    }
}

package db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.widget.Toast;

/**
 * Created by code2care on 02/02/15.
 * 
 * 
 * This class contains the following functions
 * 
 * 
 * 1. insertQuery(String record, Context context)
 * 
 * We call this function when the user clicks on the Insert button
 * 
 * 2. insertQuery(String record, Context context)
 * 
 * We call this function when the user clicks on the Insert button
 * 
 * 3. deleteQuery(String id, Context context)
 * 
 * This function is called when the delete button is clicked.
 * We delete the record from the database for the ID inputted by
 * the user in the EditText
 * 
 * 
 * 4. SelectQuery(Context context)
 * 
 * This method is called every time when an insert, update or
 * delete operation is carried out.
 * 
 * It returns a String value, that has the latest updated records
 * displayed to the user as a TextView
 */
public class DbOperations {


    private SQLiteDbHelper dBHelper;
    private SQLiteDatabase dataBase;

    ContentValues values = new ContentValues();

    //Insert record to SQLite db table
    public void insertQuery(String record, Context context) {


        try {


            dBHelper = new SQLiteDbHelper(context);


            dataBase = dBHelper.getWritableDatabase();

            values.put(SQLiteDbHelper.FIELD_RECORD, record);

            //insert
            dataBase.insert(SQLiteDbHelper.TABLE_NAME, null, values);

        } catch (SQLiteException e) {

            Toast.makeText(context, "Error occurred while inserting record to the table!!", Toast.LENGTH_SHORT).show();

        } catch (Exception e)

        {
            Toast.makeText(context, "Error occurred while inserting record to the table!!", Toast.LENGTH_SHORT).show();

        } finally {

            System.out.println("Helre ");
            dataBase.close();
        }


    }


    //Update record to SQLite db table
    public void updateQuery(String id, String record, Context context) {

        try {


            dataBase = dBHelper.getWritableDatabase();

            values.put(SQLiteDbHelper.FIELD_RECORD, record);

            dBHelper = new SQLiteDbHelper(context);

            //update db table for a given ID
            dataBase.update(SQLiteDbHelper.TABLE_NAME, values, SQLiteDbHelper.FIELD_ID + "=" + id, null);


        } catch (SQLiteException e) {

            Toast.makeText(context, "Error occurred while updating the table!!", Toast.LENGTH_SHORT).show();

        } catch (Exception e)

        {
            Toast.makeText(context, "Error occurred while updating the table!!", Toast.LENGTH_SHORT).show();

        } finally {
            dataBase.close();
        }


    }

    //Delete  record to SQLite db table
    public void deleteQuery(String id, Context context) {
        try {
            dBHelper = new SQLiteDbHelper(context);


            dataBase = dBHelper.getWritableDatabase();

            //Delete the record where id = entered number
            dataBase.delete(SQLiteDbHelper.TABLE_NAME, SQLiteDbHelper.FIELD_ID + "=" + id, null);


        } catch (SQLiteException e) {

            Toast.makeText(context, "Error occurred while deleting record for the table!!", Toast.LENGTH_SHORT).show();

        } catch (Exception e) {
            Toast.makeText(context, "Error occurred while deleting record for the table!!", Toast.LENGTH_SHORT).show();

        } finally {
            dataBase.close();


        }

    }


    //Select record to SQLite db table
    public String SelectQuery(Context context) {

        String result = "";
        try {


            dBHelper = new SQLiteDbHelper(context);

            dataBase = dBHelper.getWritableDatabase();

            Cursor mCursor = dataBase.rawQuery("SELECT * FROM " + SQLiteDbHelper.TABLE_NAME, null);



            result = "ID"+ "\t RECORD" +"\n========================= \n";

            //Loop the records and add it to a string
            if (mCursor.moveToFirst()) {
                do {

                    result = result + mCursor.getString(mCursor.getColumnIndex(SQLiteDbHelper.FIELD_ID)) + "\t";
                    result = result + mCursor.getString(mCursor.getColumnIndex(SQLiteDbHelper.FIELD_RECORD)) + "\n";

                } while (mCursor.moveToNext());
            }

            mCursor.close();

        } catch (SQLiteException e) {

            Toast.makeText(context, "Error occurred while Selecting records for the table!!", Toast.LENGTH_SHORT).show();

        } catch (Exception e) {
            Toast.makeText(context, "Error occurred while Selecting records for the table!!", Toast.LENGTH_SHORT).show();

        } finally {
            dataBase.close();


        }

        return result;
    }
}

And Finally the Manifest file: We do not need any special user permission for this Application.

<?xml version="1.0" encoding="utf-8"?>
<manifest xmlns:android="http://schemas.android.com/apk/res/android"
    package="com.code2care.tools.androidsqliteexample" >

    <application
        android:allowBackup="true"
        android:icon="@drawable/ic_launcher"
        android:label="@string/app_name"
        android:theme="@style/Theme.AppCompat.NoActionBar" >
        <activity
            android:name=".MainActivity"
            android:label="@string/app_name" >
            <intent-filter>
                <action android:name="android.intent.action.MAIN" />

                <category android:name="android.intent.category.LAUNCHER" />
            </intent-filter>
        </activity>
    </application>

</manifest>