Android SQLite Database CRUD Tutorial with Example Application

Note: We are currently improving this post.

This is a step by step tutorial where we'll create an Android application that demonstrates a CRUD database application using SQLite. CRUD operations in Android is an essential skill every aspiring Android developer must have.

DOWNLOAD SOURCE CODE

Android SQLite Database Sample Application

Follow the steps below after you created a new Android project called "AndroidCrudExample"

1. Put a "Create Student" button on your res/layout/activity_main.xml

    -Remove the "Hellow World!" TextView there
    -Change Button Label from "Button" to "Create Student"
    -Rename id from "@+id/button1" to "@+id/buttonCreateStudent"

<Button
    android:id="@+id/buttonCreateStudent"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_alignParentLeft="true"
    android:layout_alignParentTop="true"
    android:text="Create Student" />

2. On your src/com.example.androidcrudexample/MainActivity.java, set the OnClickListener of the "Create Student" button.

-Set it up for your "buttonCreateStudent"

Button buttonCreateLocation = (Button) findViewById(R.id.buttonCreateStudent);
buttonCreateLocation.setOnClickListener(new OnClickListenerCreateStudent());

-Create a listener class "OnClickListenerCreateStudent.java" (all .java files are under your src/com.example.androidcrudexample/)

public class OnClickListenerCreateStudent implements OnClickListener {

    @Override
    public void onClick(View view) {
        
    }
}

3. Prepare your student input form in res/layout/student_input_form.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" >

    <EditText
        android:id="@+id/editTextStudentFirstname"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_alignParentLeft="true"
        android:layout_alignParentTop="true"
        android:hint="Student Firstname"
        android:singleLine="true" >

        <requestFocus />
    </EditText>

    <EditText
        android:id="@+id/editTextStudentEmail"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:layout_alignParentLeft="true"
        android:layout_below="@+id/editTextStudentFirstname"
        android:hint="Student Email"
        android:singleLine="true" />

</RelativeLayout>

4. Going back to your "OnClickListenerCreateStudent.java"

-Get the application context

final Context context = view.getContext();    

-Inflate the student_input_form.xml

LayoutInflater inflater = (LayoutInflater) context.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
final View formElementsView = inflater.inflate(R.layout.student_input_form, null, false);

-List down form widgets inside student_input_form.xml as a "final variable" since we'll use it inside an AlertDialog

final EditText editTextStudentFirstname = (EditText) formElementsView.findViewById(R.id.editTextStudentFirstname);
final EditText editTextStudentEmail = (EditText) formElementsView.findViewById(R.id.editTextStudentEmail);

-Create an AlertDialog with the inflated student_input_form and an "Add" button

new AlertDialog.Builder(context)
    .setView(formElementsView)
    .setTitle("Create Student")
    .setPositiveButton("Add",
        new DialogInterface.OnClickListener() {
            public void onClick(DialogInterface dialog, int id) {

                dialog.cancel();
            }

        }).show();

-Try to run.
   

5. Inside the AlertDialog's "Add" onClick() method, we have to save the record.

-Get the user inputted values

String studentFirstname = editTextStudentFirstname.getText().toString();
String studentEmail = editTextStudentEmail.getText().toString();

                       
-We're actually using an OOP approach here. Create a new "ObjectStudent.java" file with properties "firstname" and "email"

public class ObjectStudent {

    int id;
    String firstname;
    String email;
   
    public ObjectStudent(){
       
    }
}

-Set the input values as a object, so we can save it to the database

ObjectStudent objectStudent = new ObjectStudent();
objectStudent.studentFirstname = studentFirstname;
objectStudent.studentEmail = studentEmail;

6. Now we're saving a record to the SQLite database.

    -Create a new "DatabaseHandler.java" file

public class DatabaseHandler extends SQLiteOpenHelper {

    private static final int DATABASE_VERSION = 1;
    protected static final String DATABASE_NAME = "StudentDatabase";
   
    public DatabaseHandler(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {

        String sql = "CREATE TABLE students " +
                "( id INTEGER PRIMARY KEY AUTOINCREMENT, " +
                "firstname TEXT, " +
                "email TEXT ) ";

        db.execSQL(sql);

    }

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

        String sql = "DROP TABLE IF EXISTS students";
        db.execSQL(sql);
       
        onCreate(db);
    }

}

-Create new "TableControllerStudent.java" file, extending the DatabaseHandler

public class TableControllerStudent extends DatabaseHandler {

    public TableControllerStudent(Context context) {
        super(context);
    }

}

    -do the create() method for creating new record

public boolean create(ObjectStudent objectStudent) {
   
    ContentValues values = new ContentValues();

    values.put("firstname", objectStudent.studentFirstname);
    values.put("email", objectStudent.studentEmail);

    SQLiteDatabase db = this.getWritableDatabase();

    boolean createSuccessful = db.insert("students", null, values) > 0;
    db.close();

    return createSuccessful;
}

    -go back inside the AlertDialog of OnClickListenerCreateStudent.java file and call the create() method

boolean createSuccessful = new TableControllerStudent(context).create(objectStudent);

    -tell the user wether insert was a success or not

if(createSuccessful){
    Toast.makeText(context, "Student information was saved.", Toast.LENGTH_SHORT).show();
}else{
    Toast.makeText(context, "Unable to save student information.", Toast.LENGTH_SHORT).show();
}

    -Try to run
   

7. Count records and display it to UI

-On your res/layout/activity_main.xml, place a TextView under your "Create Student" button

<TextView
    android:id="@+id/textViewRecordCount"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"
    android:layout_below="@+id/buttonCreateStudent"
    android:text="0 records found"
    android:padding="1dp" />

-On your MainActivity.java file, create a countRecords() method   

public void countRecords() {
       
}

-On your TableControllerStudent.java, create a count() method

public int count() {

    SQLiteDatabase db = this.getWritableDatabase();

    String sql = "SELECT * FROM students";
    int recordCount = db.rawQuery(sql, null).getCount();
    db.close();

    return recordCount;

}

-Go back to your MainActivity.java > countRecords() method and call the count() method you just created

int recordCount = new TableControllerStudent(this).count();

-display the count to the UI

TextView textViewRecordCount = (TextView) findViewById(R.id.textViewRecordCount);
textViewRecordCount.setText(recordCount + " records found.");

    -On your MainActivity.java > onCreate() method, call the countRecords() method

countRecords();

    -On your OnClickListenerCreateStudent.java > inside AlertDialog, also call the countRecords() method

((MainActivity) context).countRecords();

-Try to run.

8. Read the records from the database and display it to UI

-On your activity_main.xml, put a ScrollView with LinearLayout inside, under textViewRecordCount

<ScrollView
    android:id="@+id/scrollViewRecords"
    android:layout_width="match_parent"
    android:layout_height="wrap_content"
    android:layout_below="@+id/textViewRecordCount" >

    <LinearLayout
        android:id="@+id/linearLayoutRecords"
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:orientation="vertical" >

    </LinearLayout>
</ScrollView>

-On your MainActivity.java file, create a countRecords() method   

public void readRecords() {
       
}

-On your TableControllerStudent.java, create a read() method

public List<ObjectStudent> read() {

    List<ObjectStudent> recordsList = new ArrayList<ObjectStudent>();
   
    String sql = "SELECT * FROM students ORDER BY id DESC";
   
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor cursor = db.rawQuery(sql, null);

    if (cursor.moveToFirst()) {
        do {

            int id = Integer.parseInt(cursor.getString(cursor.getColumnIndex("id")));
            String studentFirstname = cursor.getString(cursor.getColumnIndex("studentFirstname"));
            String studentEmail = cursor.getString(cursor.getColumnIndex("studentEmail"));

            ObjectStudent objectStudent = new ObjectStudent();
            objectStudent.id = id;
            objectStudent.studentFirstname = studentFirstname;
            objectStudent.studentEmail = studentEmail;
           
            recordsList.add(objectStudent);

        } while (cursor.moveToNext());
    }

    cursor.close();
    db.close();

    return recordsList;
}

-On your MainActivity.java, create the readRecords() method to display the records to UI

public void readRecords() {
   
    LinearLayout linearLayoutRecords = (LinearLayout) findViewById(R.id.linearLayoutRecords);
    linearLayoutRecords.removeAllViews();

    List<ObjectStudent> students = new TableControllerStudent(this).read();

    if (students.size() > 0) {

        for (ObjectStudent obj : students) {

            int id = obj.id;
            String studentFirstname = obj.firstname;
            String studentEmail = obj.email;

            String textViewContents = studentFirstname + " - " + studentEmail;
           
            TextView textViewLocationItem = new TextView(this);
            textViewLocationItem.setPadding(0, 10, 0, 10);
            textViewLocationItem.setText(textViewContents);
            textViewLocationItem.setTag(Integer.toString(id));
           
            linearLayoutRecords.addView(textViewLocationItem);
        }

    }

    else {

        TextView locationItem = new TextView(this);
        locationItem.setPadding(8, 8, 8, 8);
        locationItem.setText("No records yet.");

        linearLayoutRecords.addView(locationItem);
    }
   
}

-Call readRecords() method on your MainActivity.java > onCreate() method

readRecords();

    -On your OnClickListenerCreateStudent.java > inside AlertDialog, call the readRecords() method as well, so it will refresh the list everytime we add new record

((MainActivity) context).readRecords();

-Try to run.

9. Now we'll try to update a record

-Create new OnLongClickListenerStudentRecord.java file

public class OnLongClickListenerStudentRecord implements OnLongClickListener {

    @Override
    public boolean onLongClick(View view) {
       
        return false;
    }

}

-Set the OnLongClickListener for each of the display records, Go to your MainActivity.java > readRecords() method()

textViewLocationItem.setOnLongClickListener(new OnLongClickListenerStudentRecord());

-Go back to your OnLongClickListenerStudentRecord.java file, set the following as class variables

Context context;
String id;

    ...

context = view.getContext();
id = view.getTag().toString();

-add an AlertDialog with simple list view for edit and delete

final CharSequence[] items = { "Edit", "Delete" };

new AlertDialog.Builder(context).setTitle("Student Record")
    .setItems(items, new DialogInterface.OnClickListener() {
        public void onClick(DialogInterface dialog, int item) {

            dialog.dismiss();

        }
    }).show();

-Try to run

-Inside the AlertDialog

if (item == 0) {
    editRecord(Integer.parseInt(id));
}

-On your OnLongClickListenerStudentRecord.java, add the following editRecord() method

public void editRecord(final int studentId) {
   
}

-To read single record that will fill up the student form for updating it

final TableControllerStudent tableControllerStudent = new TableControllerStudent(context);
ObjectStudent objectStudent = tableControllerStudent.readSingleRecord(studentId);

-On your TableControllerStudent.java, add the method readSingleRecord()

public ObjectStudent readSingleRecord(int studentId) {

    ObjectStudent objectStudent = null;

    String sql = "SELECT * FROM students WHERE id = " + studentId;

    SQLiteDatabase db = this.getWritableDatabase();

    Cursor cursor = db.rawQuery(sql, null);

    if (cursor.moveToFirst()) {

        int id = Integer.parseInt(cursor.getString(cursor.getColumnIndex("id")));
        String firstname = cursor.getString(cursor.getColumnIndex("firstname"));
        String email = cursor.getString(cursor.getColumnIndex("email"));

        objectStudent = new ObjectStudent();
        objectStudent.id = id;
        objectStudent.firstname = firstname;
        objectStudent.email = email;

    }

    cursor.close();
    db.close();

    return objectStudent;

}

-Inflate student_input_form.xml

LayoutInflater inflater = (LayoutInflater) context.getSystemService(Context.LAYOUT_INFLATER_SERVICE);
final View formElementsView = inflater.inflate(R.layout.student_input_form, null, false);

-List down form elements

final EditText editTextStudentFirstname = (EditText) formElementsView.findViewById(R.id.editTextStudentFirstname);
final EditText editTextStudentEmail = (EditText) formElementsView.findViewById(R.id.editTextStudentEmail);

-Set single record values

editTextStudentFirstname.setText(objectStudent.firstname);
editTextStudentEmail.setText(objectStudent.email);

-Show an AlertDialog with the form and single record filling it up

new AlertDialog.Builder(context)
    .setView(formElementsView)
    .setTitle("Edit Record")
    .setPositiveButton("Save Changes",
        new DialogInterface.OnClickListener() {
            public void onClick(DialogInterface dialog, int id) {

                dialog.cancel();
            }

        }).show();

-inside the AlertDialog, create the object with the updated value

ObjectStudent objectStudent = new ObjectStudent();
objectStudent.id = studentId;
objectStudent.firstname = editTextStudentFirstname.getText().toString();
objectStudent.email = editTextStudentEmail.getText().toString();

-Update the record and tell the user whether it was updated or not

boolean updateSuccessful = tableControllerStudent.update(objectStudent);
                   
if(updateSuccessful){
    Toast.makeText(context, "Student record was updated.", Toast.LENGTH_SHORT).show();
}else{
    Toast.makeText(context, "Unable to update student record.", Toast.LENGTH_SHORT).show();
}

-On your TableControllerStudent.java, add the update() method

public boolean update(ObjectStudent objectStudent) {
   
    ContentValues values = new ContentValues();

    values.put("firstname", objectStudent.firstname);
    values.put("email", objectStudent.email);

    String where = "id = ?";

    String[] whereArgs = { Integer.toString(objectStudent.id) };

    SQLiteDatabase db = this.getWritableDatabase();

    boolean updateSuccessful = db.update("students", values, where, whereArgs) > 0;
    db.close();
   
    return updateSuccessful;

}

-Refresh the count and record list

((MainActivity) context).countRecords();
((MainActivity) context).readRecords();

10. Now we'll try to delete a record

-On your OnLongClickListenerStudentRecord.java > Inside the AlertDialog

else if (item == 1) {

    boolean deleteSuccessful = new TableControllerStudent(context).delete(id);
   
    if (deleteSuccessful){
        Toast.makeText(context, "Student record was deleted.", Toast.LENGTH_SHORT).show();
    }else{
        Toast.makeText(context, "Unable to delete student record.", Toast.LENGTH_SHORT).show();
    }
   
    ((MainActivity) context).countRecords();
    ((MainActivity) context).readRecords();

}

Output Screenshots: 

Please note that the following screenshots is not from the source code above, but it looks pretty much like this. On the code above, we used the student "firstname" and "email" fields.

Our main screen.

When "Create Location" button was touched.

When the "Add" button
on the pop up was touched.

When the added location record
on the list was touched.

When the user chose to edit.

After touching the "Save Changes" button.
It will show the record was updated.

When the user chose to delete.

A Quick Android SQLite Database Example

Here's another version of my Android Sqlite tutorial. This version is very simple, contains only two JAVA files, focuses on Android CRUD and hopefully easier to understand. Some people want it this way.

When you run this code.

DOWNLOAD CODE

MainActivity.java - This is where our CRUD commands will be executed. The location object can also be found here.

package com.example.sqliteexample2;

import java.util.List;

import android.os.Bundle;
import android.util.Log;
import android.app.Activity;

public class MainActivity extends Activity {

    // for our logs
    public static final String LOG_TAG = "MainActivity.java";

    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        DatabaseHandler databaseH = new DatabaseHandler(MainActivity.this);

        // CREATE
        LocationObject LocationObj = new LocationObject("Quezon City",
                "The place where I work.");
        if (databaseH.create(LocationObj)) {
            Log.v(LOG_TAG, "Record successfully created.");
        }

        // READ
        List<LocationObject> locations = databaseH.read();
        for (LocationObject record : locations) {
            Log.v(LOG_TAG, "ID: " + record.locationId);
            Log.v(LOG_TAG, "Name: " + record.locationName);
            Log.v(LOG_TAG, "Description: " + record.locationDescription);
        }

        // UPDATE
        if (databaseH.update(1, "Quezon City, PH",
                "The place where I work AND CODE.")) {
            Log.v(LOG_TAG, "Record successfully updated.");
        }

        // DELETE
        if (databaseH.delete(1)) {
            Log.v(LOG_TAG, "Record successfully deleted.");
        }
    }

}

/*
 * Represents location details or fields.
 */
class LocationObject {

    public int locationId;
    public String locationName;
    public String locationDescription;

    // constructor for adding
    public LocationObject(String locationName, String locationDescription) {
        this.locationName = locationName;
        this.locationDescription = locationDescription;
    }

    // constructor for updating
    public LocationObject(int locationId, String locationName,
            String locationDescription) {
        this.locationId = locationId;
        this.locationName = locationName;
        this.locationDescription = locationDescription;
    }

}

DatabaseHandler.java - This handles all database operations or methods like creation, dropping, upgrading database version and ofcource, CRUD.

package com.example.sqliteexample2;

import java.util.ArrayList;
import java.util.List;

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

public class DatabaseHandler extends SQLiteOpenHelper {

    // for our logs
    public static final String LOG_TAG = "DatabaseHandler.java";

    // database version
    private static final int DATABASE_VERSION = 2;

    // database name
    protected static final String DATABASE_NAME = "NinjaDatabase2";

    // table details
    public String tableName = "locations";

    public String fieldLocationId = "id";
    public String fieldLocationName = "name";
    public String fieldLocationDescription = "description";

    // constructor
    public DatabaseHandler(Context context) {

        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    // creating Tables
    @Override
    public void onCreate(SQLiteDatabase db) {

        String sql = "";

        sql += "CREATE TABLE " + tableName;
        sql += " ( ";
        sql += fieldLocationId + " INTEGER PRIMARY KEY AUTOINCREMENT, ";
        sql += fieldLocationName + " TEXT, ";
        sql += fieldLocationDescription + " TEXT ";
        sql += " ) ";

        db.execSQL(sql);

    }

    /*
     * When upgrading the database, it will drop the current table and recreate.
     */
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        String sql = "DROP TABLE IF EXISTS " + tableName;
        db.execSQL(sql);

        onCreate(db);
    }

    /*
     * Create location record.
     * 
     * @param - location contains location details to be added as single row.
     */
    public boolean create(LocationObject location) {

        boolean createSuccessful = false;
        
        ContentValues values = new ContentValues();

        values.put(fieldLocationName, location.locationName);
        values.put(fieldLocationDescription, location.locationDescription);

        SQLiteDatabase db = this.getWritableDatabase();

        createSuccessful = db.insert(tableName, null, values) > 0;
        db.close();

        return createSuccessful;
    }

    /*
     * Read all location record.
     */
    public List<LocationObject> read() {

        List<LocationObject> recordsList = new ArrayList<LocationObject>();

        // select query
        String sql = "";
        sql += "SELECT * FROM " + tableName;
        sql += " ORDER BY " + fieldLocationId + " DESC";

        SQLiteDatabase db = this.getWritableDatabase();

        // execute the query
        Cursor cursor = db.rawQuery(sql, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {

                int locationId = Integer.parseInt(cursor.getString(cursor
                        .getColumnIndex(fieldLocationId)));

                String locationName = cursor.getString(cursor
                        .getColumnIndex(fieldLocationName));

                String locationDescription = cursor.getString(cursor
                        .getColumnIndex(fieldLocationDescription));

                LocationObject location = new LocationObject(locationId,
                        locationName, locationDescription);

                // add to list
                recordsList.add(location);

            } while (cursor.moveToNext());
        }

        // close the database
        db.close();

        // return the list of records
        return recordsList;
    }

    /*
     * Update location record.
     * 
     * @param id - will identify which record is to be updated.
     * 
     * @param name - the new location name to be saved.
     * 
     * @param description - the new location description to be saved.
     */
    public boolean update(int id, String name, String description) {

        boolean updateSuccessful = false;

        ContentValues values = new ContentValues();

        values.put(fieldLocationName, name);
        values.put(fieldLocationDescription, description);

        // you can use AND if you have multiple conditions
        String where = fieldLocationId + " = ?";

        // you should use commas when you have multiple conditions
        String[] whereArgs = { Integer.toString(id) };

        SQLiteDatabase db = this.getWritableDatabase();

        // use the update command
        updateSuccessful = db.update(tableName, values, where, whereArgs) > 0;
        db.close();

        return updateSuccessful;
    }

    /*
     * Delete location record.
     * 
     * @param id - to identify which location record is to be deleted.
     */
    public boolean delete(int id) {
        boolean deleteSuccessful = false;

        SQLiteDatabase db = this.getWritableDatabase();
        deleteSuccessful = db.delete(tableName, "id ='" + id + "'", null) > 0;
        db.close();

        return deleteSuccessful;

    }

    /*
     * For more methods like reading single record or counting records, see my
     * full version of Android Sqlite tutorial, specifically,
     * LocationTableController.java
     */
}

You can see the output of this code on your logcat. You can find the logcat in Window > Show View > Other > Search for "LogCat" Output should look like this:

Our logcat output.

The first line is our create command. Line 2 to 4 is our read command. Line 5 is our update and the last line, line 6 is our delete command.

Let me know if you have any questions. Hope you'll also check my previous Android Sqlite tutorial after you learn the basics from here! :)

The Code of a Ninja Resources

• Android Programming Tutorials
• CakePHP Programming Tutorials
• jQuery and AJAX Programming Tutorials
• PHP Programming Tutorials
• Facebook Programming Tutorials

Tweet

For FREE programming tutorials, click the red button below and subscribe! :)

SUBSCRIBE to Receive Ninja Codes