Code2care : TechZone Apps & Tutorials

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





Android and SQLite simplified example


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 good idea of SQL) and cover all basic operations needed to work with your Android App.

We will create an Android Application thats 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 as "records_db" and have just two filed

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


What will we do using SQLite



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.



How does our Android + SQLite Example App works !!


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 lets get started, we create our simple android Activity first.


Layout file : activity_main.xml


<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>




String file : strings.xml


<?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>




MainActivity.java



Warning: htmlspecialchars() expects parameter 2 to be long, string given in /home/content/04/10623904/html/pages/sqlite-with-android-covers-select-insert-update-delete/index.php on line 438



SQLiteDbHelper.java

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 table with ID as primary key and autoincrement
 *
 * We need to Override onCreate and onUpgrade 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);

    }


}





DbOperations.java


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 Insert button
 * 
 * 2. insertQuery(String record, Context context)
 * 
 * We call this function when the user clicks on Insert button
 * 
 * 3. deleteQuery(String id, Context context)
 * 
 * This function is called when delete button is clicked.
 * We delete the record from database for the ID inputted by
 * the user in the EditText
 * 
 * 
 * 4. SelectQuery(Context context)
 * 
 * This method is called everytime when a insert, update or
 * delete operation is carried out.
 * 
 * It returns an String value, that has the latest updated records
 * displayed to the user as an 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.

AndroidManifest.xml


<?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>












Top




Posts related to sqlite.


1. List of Open Source SQLite Editor for Mac OS X, Windows and Linux

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

3. SQLite Error: unknown command or invalid arguments: "open". Enter ".help" for help


Posts related to android.


1. Share image and text Twitter using your Android Application Programatically

2. The Android Virtual Device 'myEmulator' is currently running an emulator and cannot be deleted.

3. Make Android View Scrollable both Horizontally and Vertically

4. Can we move apps like WhatsApp, Facebook to external MicroSD card

5. Parsing Data for android-21 failed unsupported major.minor version 51.0

6. Android : Prevent App for rotation landscape or portrait

7. ADT quit unexpectedly error on Mac OSX Android Eclipse SDK

8. Android-Failed to install apk on device EOF Timeout Error

9. How to change Android EditText Cursor Color

10. JavaDocs for Android SDK Eclipse IDE

11. Two Buttons next to each other in Android Layout

12. Change Title text for Android Activity using java code

13. How to add Newline to text in Android TextView

14. Android : Unable to load VM from snapshot : Mac OS X Error

15. INVALID FILE NAME: MUST CONTAIN ONLY [a-z0-9_.] Android Eclipse Error

16. Hide Tittle bar and Navigation bar from android activity

17. appcompat_v7 errors after updates to API level 21 Material Theme

18. How to Copy Text to Android Clipboard Programatically ClipboardManager (Both Old and New SDK)

19. [Solution] Running Android Lint has encountered a problem NullPointerException Error

20. Remove Trailing zeros BigDecimal Java

    more...






Popular tags
android
x 175
eclipse
x 29
notepadplusplus
x 20
macosx
x 14
sharepoint
x 14
html
x 14
mac
x 13
androidstudio
x 11




1000+ C Programs     PHP Tutorial     JSON Tutorial     Swift Tutorial     India Pinocdes     About Us     Privacy Policy


Code2care © 2012-17