This lesson:

- storing data with SQLite;

The last lesson we took a look at the most simple way of data storing  - Preferences. This way is very limited and not suitable for big structured data storing. This lesson we will take a look at SQLite. This is a database with table and requests, just like a common database.

At first, we need to enquire about the way an app interacts with a database. 

Connecting to database within an app we set its name and version. The following situations may occur:

1) Database doesn’t exist. It may happen in case the application has been installed for the first time. In such case the application should itself create a database with tables and interact with it then.

2) Database exists, but its version is obsolete. It may happen in case the application has been updated. For example: an updated program needs new fields in existing tables or even new tables. In such case the application should update existing tables or create new, if it’s necessary.

3) Database exists and its version is actual. In such case an application just connect to database and interact with it.

As you understand, when we say “application should” that means “developer should”. I.e. It’s our responsibility.  To do all operations described before, we should create a class which extends SQLiteOpenHelper. Let’s call it DBHelper. This class will provide methods for update and creation of a database in cases when it doesn’t exist or it is obsolete.

onCreate is a method which will be called in case the database doesn’t exist or obsolete.

onUpgrade will be called if we try to connect to a newer version of the database than the existing one.

Let’s create a simple contacts directory application which will store name and mail info. We will enter data with the phone screen and use logs for data displaying. List is often used for that, but we don’t know about it for now and I don’t want to make our application too heavy. The main thing is to master the techniques of working with the database.

Let’s create a new project: 

Project name: P0341_SimpleSQLite
Build Target: Android 2.3.3
Application name: SimpleSQLite
Package name: ru.startandroid.develop.p0341simplesqlite
Create Activity: MainActivity

We will create two screens for data entering and table clearing. Open main.xml and code:

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout
    xmlns:android="http://schemas.android.com/apk/res/android"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent"
    android:orientation="vertical">
    <LinearLayout
        android:id="@+id/linearLayout1"
        android:layout_width="match_parent"
        android:layout_height="wrap_content">
        <TextView
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Name"
            android:layout_marginLeft="5dp"
            android:layout_marginRight="5dp">
        </TextView>
        <EditText
            android:id="@+id/etName"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1">
            <requestFocus>
            </requestFocus>
        </EditText>
    </LinearLayout>
    <LinearLayout
        android:id="@+id/linearLayout3"
        android:layout_width="match_parent"
        android:layout_height="wrap_content">
        <TextView
            android:id="@+id/textView2"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Email"
            android:layout_marginLeft="5dp"
            android:layout_marginRight="5dp">
        </TextView>
        <EditText
            android:id="@+id/etEmail"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:layout_weight="1">
        </EditText>
    </LinearLayout>
    <LinearLayout
        android:id="@+id/linearLayout2"
        android:layout_width="match_parent"
        android:layout_height="wrap_content">
        <Button
            android:id="@+id/btnAdd"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Add">
        </Button>
        <Button
            android:id="@+id/btnRead"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Read">
        </Button>
        <Button
            android:id="@+id/btnClear"
            android:layout_width="wrap_content"
            android:layout_height="wrap_content"
            android:text="Clear">
        </Button>
    </LinearLayout>
</LinearLayout>

There are two views for data entering and buttons for adding and loading data and clearing the table.

Open MainActivity.java and code:

package ru.startandroid.develop.p0341simplesqlite;

import android.app.Activity;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Bundle;
import android.util.Log;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;
import android.widget.EditText;

public class MainActivity extends Activity implements OnClickListener {

  final String LOG_TAG = "myLogs";

  Button btnAdd, btnRead, btnClear;
  EditText etName, etEmail;

  DBHelper dbHelper;

  /** Called when the activity is first created. */
  @Override
  public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);

    btnAdd = (Button) findViewById(R.id.btnAdd);
    btnAdd.setOnClickListener(this);

    btnRead = (Button) findViewById(R.id.btnRead);
    btnRead.setOnClickListener(this);

    btnClear = (Button) findViewById(R.id.btnClear);
    btnClear.setOnClickListener(this);

    etName = (EditText) findViewById(R.id.etName);
    etEmail = (EditText) findViewById(R.id.etEmail);
    
    // create object for database creation and control
    dbHelper = new DBHelper(this);
  }

  
  @Override
  public void onClick(View v) {
    
    // create object for data
    ContentValues cv = new ContentValues();
    
    // get data from editText fields
    String name = etName.getText().toString();
    String email = etEmail.getText().toString();

    // connect to database
    SQLiteDatabase db = dbHelper.getWritableDatabase();
    

    switch (v.getId()) {
    case R.id.btnAdd:
      Log.d(LOG_TAG, "--- Insert in mytable: ---");
      // create data for insertion in a form of pairs: column name and value
      
      cv.put("name", name);
      cv.put("email", email);
      // insert row and get it’s ID
      long rowID = db.insert("mytable", null, cv);
      Log.d(LOG_TAG, "row inserted, ID = " + rowID);
      break;
    case R.id.btnRead:
      Log.d(LOG_TAG, "--- Rows in mytable: ---");
      // make a request for all data from mytable, get Cursor 
      Cursor c = db.query("mytable", null, null, null, null, null, null);

      // put the cursor to the first row
      // false will be returned in case there are no rows
      if (c.moveToFirst()) {

        // determine the column numbers by names
        int idColIndex = c.getColumnIndex("id");
        int nameColIndex = c.getColumnIndex("name");
        int emailColIndex = c.getColumnIndex("email");

        do {
          // get values according to column numbers and put them to log
          Log.d(LOG_TAG,
              "ID = " + c.getInt(idColIndex) + 
              ", name = " + c.getString(nameColIndex) + 
              ", email = " + c.getString(emailColIndex));
          // moving to the next row 
          // if the current row is the last and there are no rows we get false and leave the statement
        } while (c.moveToNext());
      } else
        Log.d(LOG_TAG, "0 rows");
      c.close();
      break;
    case R.id.btnClear:
      Log.d(LOG_TAG, "--- Clear mytable: ---");
      // delete all rows
      int clearCount = db.delete("mytable", null, null);
      Log.d(LOG_TAG, "deleted rows count = " + clearCount);
      break;
    }
    // close database connection
    dbHelper.close();
  }
  
  

  class DBHelper extends SQLiteOpenHelper {

    public DBHelper(Context context) {
      // superclass constructor
      super(context, "myDB", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
      Log.d(LOG_TAG, "--- onCreate database ---");
      // create table with columns
      db.execSQL("create table mytable ("
          + "id integer primary key autoincrement," 
          + "name text,"
          + "email text" + ");");
    }

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

    }
  }

}

There are lots of new words in this code. Let’s find out what it is.

Within Activity’s onCreate method we determine objects, set listeners and for database management we create dbHelper which is an instance of DBHelper class. DBHelper will be described later.

Let’s take a look at onClick method within which we process buttons clicking.

ContentValues class is used to determine values for table fields. We create cv and then use it. We initialize name and email variables with values from editText views. After that using getWritableDatabase  method, we connect to the database and get SQLiteDatabase object. It will allow us to work with DB. We will use its methods insert, query and delete which have many entering parametres, but we will use them in the most simple way.

Following the next step we determine which button was clicked:

btnAdd is for adding data into We put the field name and value pairs into cv. Inserting data into a table, values will be inserted into table fields under appropriate names we have specified.  We fill with data the name and email fields, an id will be filled automatically (primary key autoincrement). Now we call  insert method and pass table name and cv object which includes entered values to it. The second method’s argument is used to paste an empty row to the table, we don’t need it this time so we pass null.  The insert method returns an ID of the pasted row, we save it into rowID and display in logs.

btnRead is used for loading all existing rows from mytable. query method is used for loading, its input parameters are the table name, the list of requested fields, the conditions of the selection, grouping and sorting. As we need all table rows we only pass the table name and all other parameters will be null. The method will return a Cursor It can be considered as a table with data. moveToFirst method makes the first row active and checks if there are any rows (if something was selected with query method). The next step we get columns order number within Cursor by their names with getColumnIndex method. This numbers we use for data loading with getInt  and getString methods and then we display data in logs. Using the moveToNext  method we go through all data in Cursor until we get the last one. If there was not any rows in the table we would display an appropriate message in logs - 0 rows. At the end we close cursor (release resources) with close method because we won’t use it further.

btnClear is used for table clearing. delete method deletes rows, we pass table name and null to it, as a condition for removing, everything will be deleted this case. The method returns a count of deleted rows.

After this we close database connection with close method.

DBHelper class is nested to MainActivity and declared at the end of the code. As I said before, DBHelper must extend SQLiteOpenHelper class.

Within constructor we call super class’ constructor and pass to it:
context;
mydb as a database name;
null as an object for interaction with cursos, we don’t need it for now;
1 is a database version.

Within this class’ onCreate method we use SQLiteDatabase object’s execSQL  method to make SQL request, which creates the table. I will remind you that this method is called when a table doesn’t exist and it must be created. Looking at the request, we see that we have created a table named mytable  with id, name and email fields.

We don’t fill onUpgrade method because we use only one database version and not gonna change it.

Let’s save and launch our app. We will interact with database and look at the logs which will show us the methods were called, and what is happening within them.

Fill editText fields with something and click Add.

Look at logs:

--- onCreate database ---
--- Insert in mytable: ---
row inserted, ID = 1

We see that DBHelper class’ onCreate method was called, that means that a table creation script has been executed. It happened because it was the first application launch and DB  has not been created. Now DB exists and it is possible to interact with it.

Next, we see that the paste method was called and returned ID equals to 1.

Let’s add some more row.

Look at logs:

--- Insert in mytable: ---
row inserted, ID = 2

This time onCreate  wasn’t called, because DB already exists. The row with ID equals to 2 was pasted.

Let’s  look at the table’s content. We click the read button and look at the logs:

--- Rows in mytable: ---
ID = 1, name = John Smith, email = This email address is being protected from spambots. You need JavaScript enabled to view it.
ID = 2, name = Some body, email = This email address is being protected from spambots. You need JavaScript enabled to view it.

We see the rows we have inserted.

Now let’s clear the table by clicking Clear button and look at the logs:

--- Clear mytable: ---
deleted rows count = 2

Two rows were deleted. that’s alright. Now click Read button and loot at the table content:

--- Rows in mytable: ---
0 rows

There is not any rows.

It’s necessary to understand within this topic that we used two classes to interact with the database:

- DBHelper which extends SQLiteOpenHelper. We called a super class’ constructor within its constructor and passed to it the database name and version. The getWritableDatabase method connects to database and returns SQLiteDatabase object for interaction with database. The close method closes the connection to database. In case database doesn’t exist or its version is obsolete, class lets us to implement its creation or update by ourselves within onCreate and onUpgrate  methods.

- SQLiteDatabase which contains methods such as interaction with data, paste, update, delete and reading.

The database file can be found in the File Explorer, as in the previous lesson. The link to it is data/data/ru.startandroid.develop.p0341simpelsqlite/databases/myDB.

The next lesson we will go on with this application. We will add an opportunity to delete and update exact rows.

Notice

I make all database operations process in the main thread in my examples for not to make this lesson too difficult. In a real application code you should use an individual thread for interaction with database so that your app won’t slow down visually. How to make it I will tell within lessons number 80-91 and 135-136.

Next lesson:

• usage of delete and query methods with the conditions.