Getting Started with SQLite on Android

Best Mobile Article of January 2017 (First Prize) by Code Project

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

Figure 1: MainActivity

Getting Ready the Resources

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

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

  1. 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();
  2. Put the values to write in a ContentValues object. For example,
    ContentValues contentValues = new ContentValues();
    contentValues.put("gender", gender);
    contentValues.put("hobbies", hobbies);
  3. 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);
  4. 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:

  1. Call the getReadableDatabase() method of the “SQLiteOpenHelper” to get a SQLiteDatabase object that represents the database and provides methods for read operations. For example:
    SQLiteDatabase db = this.getReadableDatabase();
  2. Call the rawQuery() method of the SQLiteDatabase object and passing it the SQL query statement as the first parameter. The second parameter is the argument to pass to the “?” placeholder in the SQL query statement. This method will return a Cursor object that represents the result set returned by the database query. For example:
    String sql = "SELECT * FROM users WHERE email=?";
    Cursor cursor =  db.rawQuery(sql, new String[] { email });

Delete

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

  1. Call the getWritableDatabase() method of the “SQLiteOpenHelper” to get a SQLiteDatabase object that represents the database. For example,
    SQLiteDatabase db = this.getWritableDatabase();
  2. 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 retrieve a record that matches this email value from the “users” table.  Finally, the “deleteUser()” method takes one email value as parameter and delete 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”!

Download

Posted in Android, Java Tagged with: , ,