Introduction

Every app involves data. Most data are supplied by users through the various input controls, such as text field, check box, radio group, spinner, and button. While some data is transient, most will require to stay or persist even after the app has stopped running. Android provides many ingenious technologies for storing persistent data locally. In this article, you will learn to perform CRUD, i.e., Create, Read, Update and Delete, on data using a SQLite Database on Android.

Setting the Stage

On your favorite Android IDE, start a new Android app project. Let’s give it an application name of AndroidSQLite and a domain name of peterleowblog.com. The resulting package name of your project will be com.peterleowblog.androidsqlite.

In the project, create an Android activity called MainActivity. As shown in Figure 1, the user interface (UI) of this MainActivity comprises the following controls:

One EditText text field for email input.
One RadioGroup control with two RadioButtons for gender selection.
Three CheckBox controls for hobbies selection.
One Spinner control for zodiac selection.
Three Button controls – one each for saving, retrieving, and updating of data respectively.

A user can key in and save new information to a SQLite database. Subsequently, the user can retrieve the information using an email as the keyword, edit and update the information into, or delete it from the SQLite database.

Figure 1: MainActivity

Getting the Resources Ready

You will need the following string resources in the strings.xml:

<?xml version="1.0" encoding="utf-8"?>
<resources>
    <string name="app_name">SQLite on Android</string>
    <string name="email">Email</string>
    <string name="gender">Gender</string>
    <string name="female">Female</string>
    <string name="male">Male</string>
    <string name="hobbies">Hobbies</string>
    <string name="coding">Coding</string>
    <string name="writing">Writing</string>
    <string name="jogging">Jogging</string>
    <string name="zodiac">Zodiac</string>
    <string name="save">Save</string>
    <string name="retrieve">Retrieve</string>
    <string name="delete">Delete</string>

    <string-array name="zodiac">
        <item>Aries</item>
        <item>Taurus</item>
        <item>Gemini</item>
        <item>Cancer</item>
        <item>Leo</item>
        <item>Virgo</item>
        <item>Libra</item>
        <item>Scorpio</item>
        <item>Sagittarius</item>
        <item>Capricorn</item>
        <item>Aquarius</item>
        <item>Pisces</item>
    </string-array>

</resources>

Rendering the View

The XML code that is responsible for rendering the view of Figure 1 is contained in activity_main.xml and shown below:

<?xml version="1.0" encoding="utf-8"?>
<ScrollView xmlns:android="http://schemas.android.com/apk/res/android"
    xmlns:tools="http://schemas.android.com/tools"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent">

    <RelativeLayout
        android:layout_width="match_parent"
        android:layout_height="wrap_content"
        android:paddingLeft="16dp"
        android:paddingRight="16dp"
        android:paddingTop="16dp"
        android:paddingBottom="16dp">

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:textAppearance="?android:attr/textAppearanceLarge"
            android:text=""
            android:id="@+id/textView"
            android:layout_alignParentTop="true"
            android:layout_centerHorizontal="true" />

        <EditText
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:inputType="textEmailAddress"
            android:ems="10"
            android:id="@+id/txtEmail"
            android:layout_below="@+id/textView4"
            android:layout_alignParentLeft="true"
            android:layout_alignParentStart="true" />

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:textAppearance="?android:attr/textAppearanceMedium"
            android:text="@string/email"
            android:id="@+id/textView4"
            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/textAppearanceMedium"
            android:text="@string/gender"
            android:id="@+id/textView5"
            android:layout_below="@+id/txtEmail"
            android:layout_alignParentLeft="true"
            android:layout_alignParentStart="true" />

        <RadioGroup
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_below="@+id/textView5"
            android:layout_alignParentLeft="true"
            android:layout_alignParentStart="true"
            android:id="@+id/radioGroupGender">

            <RadioButton
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="@string/male"
                android:id="@+id/radMale"
                android:checked="false" />

            <RadioButton
                android:layout_width="wrap_content"
                android:layout_height="wrap_content"
                android:text="@string/female"
                android:id="@+id/radFemale"
                android:checked="false" />

        </RadioGroup>

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:textAppearance="?android:attr/textAppearanceMedium"
            android:text="@string/hobbies"
            android:id="@+id/textView6"
            android:layout_below="@+id/radioGroupGender"
            android:layout_alignParentLeft="true"
            android:layout_alignParentStart="true" />

        <CheckBox
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="@string/coding"
            android:id="@+id/chkCoding"
            android:onClick="onCheckboxClicked"
            android:checked="false"
            android:layout_below="@+id/textView6"
            android:layout_alignParentLeft="true"
            android:layout_alignParentStart="true" />

        <CheckBox
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="@string/writing"
            android:id="@+id/chkWriting"
            android:onClick="onCheckboxClicked"
            android:layout_below="@+id/chkCoding"
            android:layout_alignParentLeft="true"
            android:layout_alignParentStart="true"
            android:checked="false" />

        <CheckBox
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="@string/jogging"
            android:id="@+id/chkJogging"
            android:onClick="onCheckboxClicked"
            android:layout_below="@+id/chkWriting"
            android:layout_alignParentLeft="true"
            android:layout_alignParentStart="true"
            android:checked="false" />

        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:textAppearance="?android:attr/textAppearanceMedium"
            android:text="@string/zodiac"
            android:id="@+id/textView7"
            android:layout_below="@+id/chkJogging"
            android:layout_alignParentLeft="true"
            android:layout_alignParentStart="true" />

        <Spinner
            android:layout_width="match_parent"
            android:layout_height="wrap_content"
            android:id="@+id/spinnerZodiac"
            android:layout_below="@+id/textView7"
            android:layout_alignParentLeft="true"
            android:layout_alignParentStart="true" />

        <Button
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="@string/save"
            android:id="@+id/btnSave"
            android:onClick="save"
            android:layout_below="@+id/spinnerZodiac"
            android:layout_alignParentLeft="true"
            android:layout_alignParentStart="true" />

        <Button
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="@string/retrieve"
            android:onClick="retrieve"
            android:id="@+id/btnRetrieve"
            android:layout_alignParentBottom="true"
            android:layout_centerHorizontal="true"
            android:layout_centerInParent="false"
            android:layout_centerVertical="false" />

        <Button
            android:text="@string/delete"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_below="@+id/spinnerZodiac"
            android:layout_alignParentRight="true"
            android:layout_alignParentEnd="true"
            android:id="@+id/btnDelete" />

    </RelativeLayout>

</ScrollView>

Controlling the Activity

Last but not least, the code for handling the UI controls and their behaviors is contained in MainActivity.java and shown below:

package com.peterleowblog.androidsqlite;

import android.app.Activity;
import android.content.res.Resources;
import android.os.Bundle;
import android.view.View;
import android.widget.AdapterView;
import android.widget.ArrayAdapter;
import android.widget.CheckBox;
import android.widget.EditText;
import android.widget.RadioButton;
import android.widget.RadioGroup;
import android.widget.Spinner;
import android.widget.TextView;

public class MainActivity extends Activity implements RadioGroup.OnCheckedChangeListener,
        AdapterView.OnItemSelectedListener{

    private String email, gender, hobbies, zodiac;

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

        ((TextView) findViewById(R.id.textView)).setText
         (getResources().getString(R.string.app_name));

        email = gender = hobbies = zodiac = "";

        RadioGroup radioGroupGender = (RadioGroup) findViewById(R.id.radioGroupGender);
        radioGroupGender.setOnCheckedChangeListener(this);

        Spinner spinnerZodiac = (Spinner) findViewById(R.id.spinnerZodiac);
        // Populate the spinner with data source
        ArrayAdapter<CharSequence> adapter = 
        ArrayAdapter.createFromResource(this, R.array.zodiac, 
                                        android.R.layout.simple_spinner_item);
        adapter.setDropDownViewResource(android.R.layout.simple_spinner_dropdown_item);
        spinnerZodiac.setAdapter(adapter);
    }

    @Override
    public void onCheckedChanged(RadioGroup radioGroup, int i) {
        int radioButtonId = radioGroup.getCheckedRadioButtonId();
        RadioButton radioButton = (RadioButton)radioGroup.findViewById(radioButtonId);
        gender = radioButton.getText().toString();
    }

    public void onCheckboxClicked(View view) {

        CheckBox chkJogging = (CheckBox) findViewById(R.id.chkJogging);
        CheckBox chkCoding = (CheckBox) findViewById(R.id.chkCoding);
        CheckBox chkWriting = (CheckBox) findViewById(R.id.chkWriting);

        StringBuilder sb = new StringBuilder();

        if (chkJogging.isChecked()) {
            sb.append(", " + chkJogging.getText());
        }

        if (chkCoding.isChecked()) {
            sb.append(", " + chkCoding.getText());
        }

        if (chkWriting.isChecked()) {
            sb.append(", " + chkWriting.getText());
        }

        if (sb.length() > 0) { // No toast if the string is empty
            // Remove the first comma
            hobbies = sb.deleteCharAt(sb.indexOf(",")).toString();
        } else {
            hobbies = "";
        }
    }

    public void onItemSelected(AdapterView<?> parent, 
        View view, int position, long id) {
        zodiac = parent.getItemAtPosition(position).toString();
    }

    public void onNothingSelected(AdapterView<?> parent) {
        // An interface callback
    }

    public void save(View view) {

        // Add code to insert/update data into SQLite
    }

    public void retrieve(View view) {

        // Add code to retrieve data from SQLite

        setupUI(); // A method to set the data on the UI controls
    }

    public void delete(View view) {

        // Add code to delete data from SQLite

        setupUI(); // A method to set the data on the UI controls
    }

    protected void setupUI(){
        ((EditText)findViewById(R.id.txtEmail)).setText(email);

        RadioButton radMale = (RadioButton)findViewById(R.id.radMale);
        RadioButton radFemale = (RadioButton)findViewById(R.id.radFemale);

        if (gender.equals("Male")){
            radMale.setChecked(true);
        } else if (gender.equals("Female")){
            radFemale.setChecked(true);
        } else {
            radMale.setChecked(false);
            radFemale.setChecked(false);
        }

        CheckBox chkCoding = (CheckBox)findViewById(R.id.chkCoding);
        CheckBox chkWriting = (CheckBox)findViewById(R.id.chkWriting);
        CheckBox chkJogging = (CheckBox)findViewById(R.id.chkJogging);

        chkCoding.setChecked(false);
        chkWriting.setChecked(false);
        chkJogging.setChecked(false);

        if (hobbies.contains("Coding")) {
            chkCoding.setChecked(true);
        }

        if (hobbies.contains("Writing")) {
            chkWriting.setChecked(true);
        }

        if (hobbies.contains("Jogging")) {
            chkJogging.setChecked(true);
        }

        Resources resource = getResources();
        String[] zodiacArray = resource.getStringArray(R.array.zodiac);
        for(int i = 0; i < zodiacArray.length; i++){
            if(zodiacArray[i].equals(zodiac)){
                ((Spinner)findViewById(R.id.spinnerZodiac)).setSelection(i);
            }
        }
    }
}

You are now really to delve into the SQLite database implementation. Let’s go…

Learning the Basics

SQLite is an open source transactional SQL database engine that is self-contained, serverless, and requires no configuration. Android provides the android.database.sqlite package that contains the classes that an app can use to create and manage its own private database.

To implement a SQLite database, the recommended way is to create a subclass of SQLiteOpenHelper and override its onCreate() and onUpgrade() methods. When this subclass is instantiated, it will open the database if it exists, create it if it does not, or upgrade it when necessary. Transactions are used to make sure that the database is always kept in a consistent state. You add the methods to manipulate the database in the subclass. For example, the code below shows a subclass of SQLiteOpenHelper called SqliteHelper:

public class SqliteHelper extends SQLiteOpenHelper {

    public static final String DATABASE_NAME = "MyDatabase";

    public static final int DATABASE_VERSION = 1;

    public SqliteHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = "CREATE TABLE users 
                      (email TEXT PRIMARY KEY, gender Text, hobbies Text, zodiac Text)";
        db.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i2) {
        db.execSQL("DROP TABLE IF EXISTS users");
        onCreate(db);
    }

    // Implement methods to manipulate the database
}

Let’s move on to explore the implementation of SQLite’s CRUD on Android.

Write

Writing to a SQLite database involves either an insert or update operation. Follow these steps:

Call the getWritableDatabase() method of the SQLiteOpenHelper to get a SQLiteDatabase object that represents the database and provides methods for read and write operations. For example:
```
SQLiteDatabase db = this.getWritableDatabase();
```

Put the values to write in a ContentValues object. For example:

ContentValues contentValues = new ContentValues();
contentValues.put("gender", gender);
contentValues.put("hobbies", hobbies);

To insert the values as a new record, call the insert() method of the SQLiteDatabase object and passing it the ContentValues object as the third parameter. The first parameter is the table name. This method will return the row ID of the newly inserted row, or -1 if an error occurred. For example:
```
result = db.insert("users", null, contentValues);
```
To update an existing record with the new value, call the update() method of the SQLiteDatabase object and passing it the ContentValues object as the second parameter. The third parameter is the WHERE clause, and the last parameter the argument to pass to the ? placeholder in the WHERE clause. This method will return the number of rows affected. For example:
```
result = db.update("users", contentValues, "email=?", new String[] { email });
```

Read

To perform read operation on the SQLite database, follow these steps:

Delete

Lastly, to delete data from the SQLite database, follow these steps:

Call the getWritableDatabase() method of the SQLiteOpenHelper to get a SQLiteDatabase object that represents the database. For example:
```
SQLiteDatabase db = this.getWritableDatabase();
```
Call the delete() method of the SQLiteDatabase object. The second parameter of this method is the WHERE clause and the last parameter the argument to replace the ? placeholder in the WHERE clause. This method will return the number of rows affected. For example:
```
db.delete("users", "email=?", new String[] { email });
```

Making It Happen

It is time to make your project SQLite-capable.

In your project, add a new Java class file called SqliteHelper that extends the SQLiteOpenHelper class in your project. When instantiated, it will create a database called MyDatabase that contains one table called users that has four fields—email (primary key), gender, hobbies, and zodiac.

Three methods are included to take care of the SQLite’s CRUD operations. The saveUser() method performs either an insertion operation if the record is new (no matching email is found), or an update on an existing record that has a matching email value. The getUser() method takes one email value as parameter and retrieves a record that matches this email value from the users table. Finally, the deleteUser() method takes one email value as parameter and deletes a record that matches this email value from the users table. The complete code for the SqliteHelper.java is shown below:

package com.peterleowblog.androidsqlite;

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

public class SqliteHelper extends SQLiteOpenHelper {

    public static final String DATABASE_NAME = "MyDatabase";
    private static final int DATABASE_VERSION = 1;

    public SqliteHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = "CREATE TABLE users 
        (email TEXT PRIMARY KEY, gender Text, hobbies Text, zodiac Text)";
        db.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int i, int i2) {
        db.execSQL("DROP TABLE IF EXISTS users");
        onCreate(db);
    }

    public boolean saveUser (String email, String gender, String hobbies, String zodiac)
    {
        Cursor cursor = getUser(email);

        SQLiteDatabase db = this.getWritableDatabase();

        ContentValues contentValues = new ContentValues();
        contentValues.put("gender", gender);
        contentValues.put("hobbies", hobbies);
        contentValues.put("zodiac", zodiac);

        long result;
        if (cursor.getCount() == 0) { // Record does not exist
            contentValues.put("email", email);
            result = db.insert("users", null, contentValues);
        } else { // Record exists
            result = db.update
            ("users", contentValues, "email=?", new String[] { email });
        }

        if (result == -1) {
            return false;
        } else {
            return true;
        }
    }

    public Cursor getUser(String email){

        SQLiteDatabase db = this.getReadableDatabase();

        String sql = "SELECT * FROM users WHERE email=?";

        return db.rawQuery(sql, new String[] { email });
    }

    public void deleteUser(String email){

        SQLiteDatabase db = this.getWritableDatabase();

        db.delete("users", "email=?", new String[] { email });
    }
}

Once the SqliteHelper.java is ready, turn to MainActivity.java, follow these steps to implement the SQLite Database option:

Declare and instantiate a SqliteHelper class variable as shown:

public class MainActivity extends Activity 
             implements RadioGroup.OnCheckedChangeListener,
        AdapterView.OnItemSelectedListener{

    private String email, gender, hobbies, zodiac;

    SqliteHelper sqliteHelper;

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

        sqliteHelper = new SqliteHelper(this);

        // omitted

Add the code for writing to the database to the save() method as shown:

public void save(View view) {
    
    email = ((EditText)findViewById(R.id.txtEmail)).getText().toString();

    if (email.isEmpty()){
        Toast.makeText(getApplicationContext(), 
        "Email cannot be empty!", Toast.LENGTH_LONG).show();
        return;
    }

    boolean result = sqliteHelper.saveUser(email, gender,  hobbies,  zodiac);
    if (result){
        Toast.makeText(getApplicationContext(), 
        "Successfully saved!", Toast.LENGTH_LONG).show();
    } else {
        Toast.makeText(getApplicationContext(), 
        "Failed to save!", Toast.LENGTH_LONG).show();
    }
}

Add the code for reading from the database to the retrieve() method as shown:

public void retrieve(View view) {

    email = ((EditText)findViewById(R.id.txtEmail)).getText().toString();

    Cursor cursor = sqliteHelper.getUser(email);
    if (cursor.getCount() != 0) {
        cursor.moveToFirst();
        email = cursor.getString(cursor.getColumnIndex("email"));
        gender = cursor.getString(cursor.getColumnIndex("gender"));
        hobbies = cursor.getString(cursor.getColumnIndex("hobbies"));
        zodiac = cursor.getString(cursor.getColumnIndex("zodiac"));
        if (!cursor.isClosed()) {
            cursor.close();
        }
    } else {
        email = "";
        gender = "";
        hobbies = "";
        zodiac = "";
    }

    setupUI();
}

Add the code for deleting from the database to the delete() method as shown:

public void delete(View view) {

    email = ((EditText)findViewById(R.id.txtEmail)).getText().toString();

    sqliteHelper.deleteUser(email);

    email = gender = hobbies = zodiac = "";

    setupUI();
}

Testing 1, 2, 3, …

Launch your app on a real device or an AVD, you should see the view appear as shown in Figure 1. Enter an email, make some selections, and then click the Save button to save the input values to the database table called users. Each saving with new email value will result in a new record being inserted into the users table. On the other hand, each saving with email value that already exists in the database will result in that existing record being updated in the users table. To retrieve a record, enter an email of that record in the Email text field and press the Retrieve button. To delete a record, simply enter an email of that record in the Email text field and press the Delete button.

Last but Not Least

When the SQLite database is closed, calling the getWritableDatabase() and getReadableDatabase() methods inevitably consume expensive resources and time. As such, you should defer calling them until they are really needed. Once opened successfully, however, the database is cached, and you can call these method whenever you need to write to the database without any more penalty. You should take advantage of the database cache by leaving the database connection open for as long as it is needed. One sure way to close the database is at the end of the life cycle of the calling activity, that is:

@Override
protected void onDestroy() {
    sqliteHelper.close();
    super.onDestroy();
}

What are you waiting for? Add this code to your MainActivity.java!

History

1^st January, 2017: Initial version

Getting Started with SQLite on Android