SQLite Database Programming In Android

Storing structured and relational data is an integral part of any modern real life application. Serializing objects into XML files is easy to implement, but hard to scale. A recent trend of storing data as JSON files is easy to conceptualize, but less suitable for machines. In the land of machines we need to understand machines first and then abstract like humans, but always keeping performance and scaling in mind. Storing data is not just having the work done but it's an art. In real life scenarios one set of data is related to another and we need keep them related in digital forms.

This calls for data integrity, fault tolerance, portability and a lot of other things in one package, but making such a system is harder than you might think. The worst part is—and where nightmares kick in—is maintaining such a system. Again, we humans do feel uneasy with the introduction of new systems. We need something that is relatively familiar to us.

A solution to all of these problems is a good database system. We have a lot of options available ranging from MySQL, Oracle database systems, SQLite and countless others. But, we cannot use all of them. Android is meant to run on devices with limited power and resource with most Android devices begin battery powered. We need something lightweight, but that doesn’t sacrifice performance, integrity, portability and ease of use along with a lot of other things.

One great option that’s been available for several years now is SQLite. It is a file based, database system that does not need the extra overhead of running a database server. It is small but fast. It is portable, performant, fault tolerant, easy to setup and use, and familiar to a lot of developers from various programming backgrounds. You can use most parts of the traditional SQL language for interacting with the database. Additionally, Android provides a wrapper around SQLite to help ease the way for Android programmers.

Creating A Simple Application

To get started with SQLite in Android you have to have prior knowledge in Java and Android programming. Use Android Studio or IntelliJ IDEA for practicing with the code shown here. I am using Android Studio for the purpose. Create an Android project and name it whatever you like (try to keep it sensible). I am putting SQLite Part1 as the application name, sabuj.me as the application domain and me.sabuj.sqlitepart1 as the application id. In the next screen I am selecting API level 15 as the Minimum SDK—currently 100% of devices active in the Play Store will support that level of API. On the next screen select Empty Activity and on the following, keep it as is and finish creating the project.

Your activity_main.xml should look like the following to follow what I am going to do.

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout xmlns:android="http://schemas.android.com/apk/res/android"
   xmlns:tools="http://schemas.android.com/tools"
   android:orientation="vertical" android:layout_width="match_parent"
   android:layout_height="match_parent"
   android:layout_margin="8dip">

   <EditText
       android:id="@+id/editName"
       android:layout_width="match_parent"
       android:layout_height="wrap_content"
       android:ems="10"
       android:inputType="textPersonName"
      android:hint="Name"
       />

   <EditText
       android:id="@+id/editEmail"
       android:layout_width="match_parent"
       android:layout_height="wrap_content"
       android:ems="10"
       android:inputType="textEmailAddress"
       android:hint="Email"
       />

   <EditText
       android:id="@+id/editProfession"
       android:layout_width="match_parent"
       android:layout_height="wrap_content"
       android:ems="10"
       android:inputType="text"
       android:hint="Profession" />

   <Button
       android:id="@+id/save"
       android:layout_width="match_parent"
       android:layout_height="wrap_content"
       android:text="Save" />

   <Button
       android:id="@+id/searchByName"
       android:layout_width="match_parent"
       android:layout_height="wrap_content"
       android:text="Search By Email" />

   <TextView
       android:id="@+id/textInfo"
       android:layout_width="match_parent"
       android:layout_height="match_parent"
       android:textAlignment="center"
       />
</LinearLayout>

Currently the application looks like the following:

SQLite Part1

We are going to develop an application that will help save the personal information of various people. The SAVE button will save data to the database and the SEARCH BY EMAIL button will help us search people by their email addresses. The area below the last button is a TextView where we will show various information including the result of searches.

Initial code in MainActivity.java should look like the following:

package me.sabuj.sqlitepart1;

import android.support.v7.app.AppCompatActivity;
import android.os.Bundle;
import android.view.View;
import android.widget.Button;
import android.widget.EditText;

public class MainActivity extends AppCompatActivity {

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

       Button saveBtn = (Button) findViewById(R.id.save);
       Button searchBtn = (Button) findViewById(R.id.searchByName);

       EditText editName = (EditText) findViewById(R.id.editName);
       EditText editEmail = (EditText) findViewById(R.id.editEmail);
       EditText editProfession = (EditText) findViewById(R.id.editProfession);

       // Setting up click listeners
       saveBtn.setOnClickListener(new View.OnClickListener() {
           @Override
           public void onClick(View v) {
               // code for onclick here
           }
       });
       searchBtn.setOnClickListener(new View.OnClickListener() {
           @Override
           public void onClick(View v) {
               // code for on click here
           }
       });
   }
   
}

The SQLiteOpenHelper

The SQLiteOpenHelper is the single most important class in SQLite database programming in Android for the beginners. It helps us create and use a database without any command for creating, configuring, and maintaining a database. You need to create a class by extending it. I am going to name this class MyDbHelper. After extending it you need to override two methods called onCreate() and onUpgrade().

The Constructor: No matter what we do in the constructor of our extended class MyDbHelper, we need to call the super class constructor by calling super() with four arguments. The first argument is the context, the second one is the database name, until you get much more advanced don’t worry about the third one and just pass null to it, and the fourth one is the database version. So, in my code I have done this the following way:

public MyDbHelper(Context ctx) {
       super(ctx, "db1.sqlite", null, 1);
   }

I would like to pass a context to the constructor when we instantiate MyDbHelper from our main activity.

Warning: The database version must be a positive integer. 0 is not a positive integer but a neutral number. So, you cannot put 0 there!

onCreate(): onCreate() is called the first time the database is created. The database is not directly created until it needs to be. When the database is created, this method is called. In our code we want to create a table with id, name, email and profession:

@Override
   public void onCreate(SQLiteDatabase db) {
       // Create table
       db.execSQL("CREATE TABLE persons(id integer primary key autoincrement, name text NOT NULL, email text NOT NULL UNIQUE, profession text NOT NULL)");

   }

onUpgrade(): Sometimes we may need to change the schema or database version of our application. In these cases we may do some housekeeping through this method. But in our application we are not going to use it as we are not yet willing to change our database schema and things for what we need to do some housekeeping.

How do we tell our application that the database version has changed? Next time you make an updated version of the application, pass a higher number to the super() in the constructor as the fourth parameter.

So, the code in our MyDbHelper.java file should look like the following, eventually:

package me.sabuj.sqlitepart1;

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


public class MyDbHelper extends SQLiteOpenHelper {
   public MyDbHelper(Context ctx) {
       super(ctx, "db1.sqlite", null, 1);
   }

   @Override
   public void onCreate(SQLiteDatabase db) {
       // Create table
       db.execSQL("CREATE TABLE persons(id integer primary key autoincrement, name text NOT NULL, email text NOT NULL UNIQUE, profession text NOT NULL)");

   }

   @Override
   public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
       // We do not want to do anything on upgrade in this application
   }
   
}

Adding Data To The Database

After filling the form in the application we need to save it by calling the save button. That means we need to start the saving task in the click listener of the SAVE button. Instead of scattering the code around, I would like to add a save() method on the helper class. So, my code looks like this:

public String save(String name, String email, String profession){
       String msg = "";
       email = email.toLowerCase(); // Usually emails are case insensitive. So, I am converting it to lowercase to avoid duplication.
       ContentValues cvals = new ContentValues();
       cvals.put("name", name);
       cvals.put("email", email);
       cvals.put("profession", profession);
       long res = this.getWritableDatabase().insert("persons", null, cvals);
       if (res < 0){
           msg = "An error occured";
       }else{
           msg = "Data inserted Successfully";
       }
       return msg;
   }

In the main activity put the following code on the class level:

MyDbHelper myDbHelper;

Also, instantiate MyDbHelper inside the onCreate() method like below:

// Preparing the database
myDbHelper = new MyDbHelper(this);

Inside the click listener's onClick() put the following code:

String res = myDbHelper.save(
    editName.getText().toString(),
    editEmail.getText().toString(),
    editProfession.getText().toString()
);
infoView.setText(res);

Now, start the application in emulator or real device to insert some data. Type the information and click save. You will see a Data inserted Successfully message. Click save a second time to see An error occurred. The error will take place due to the attempted inserting of the same email address a second time. We told SQLite that the email field is unique. Look at the following screenshot:

Searching For Data

Storing data is useless if we cannot retrieve it on demand. Without writing scattered code, let's add another method called search() to our database helper method. This will search data by email address.

public String search(String email){
       String msg = "";
       email = email.toLowerCase();
       Cursor cursor = this.getReadableDatabase().rawQuery("SELECT name, email, profession FROM persons WHERE email='" + email + "'", null);
       if (cursor.getCount() < 1){
           msg = "Person with email " + email + "NOT FOUND";
       }else{
           cursor.moveToNext();
           msg = "Name: " + cursor.getString(0) + "\n" +
                 "Email: " + cursor.getString(1) + "\n" +
                 "Position: " + cursor.getString(2) + "\n";
       }
       return msg;
   }

In the click listener for the search button put the following code:

String res = myDbHelper.search(editEmail.getText().toString());
infoView.setText(res);

Now, search with the email address that you previously saved with other two fields of information in the email input box and click SEARCH BY EMAIL to see some result on the screen. Below is a screenshot from my test:

Conclusions

Talking about SQLite reminds me of the famous quote from Muhammad Ali, "Float like a butterfly, sting like a bee. The hands can't hit what the eyes can't see". Both the biggest advantages and disadvantages of SQLite are: it is file based and it does not need a client-server model of working. One advantage of client-server database model is that multiple clients can interact with the database at the same time. Android has a workaround for that drawback: content providers. I do not call this a drawback, I call this a feature.