Android Development – Database Management

By | May 22, 2010

In this post, I’ll go over bare bones database management in an Android application, demonstrating how to create a database and perform simple CRUD actions against this database. I’ll also wire up a very simple UI to test our CRUD functionality. Finally, I’ll show how to utilize the built-in database upgrade functionality.

The Database

Android ships with SQLite, an open-source and very lightweight database engine that is popular in a great many products. From their web site:

SQLite is a software library that implements a self-contained,serverless, zero-configuration, transactional SQL database engine. SQLite is the most widely deployed SQL database engine in the world. The source code for SQLite is in the public domain.

We will be creating a simple one table database for a fictional game that will track player history. Each row will represent a game, and will store whether the player won or lost, the time it took to finish the game, and what difficulty level the computer opponent was set to.

First Steps

We will extend SQLiteOpenHelper to help us talk to our database. Create a new Android project and add a new class file called DbHelper. Extend SQLiteOpenHelper, and let Eclipse add the methods that need to be overridden.

package com.personal.androidfun.databasetests;

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

public class DbHelper extends SQLiteOpenHelper {

    public DbHelper(Context context, String name, CursorFactory factory,
            int version) {
        super(context, name, factory, version);
        // TODO Auto-generated constructor stub
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // TODO Auto-generated method stub

    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // TODO Auto-generated method stub

    }

}

You can see that we have to implement two methods, onCreate and onUpgrade. To ensure that clients of this class don’t have to know all of the nitty gritty database details, we’ll change the constructor as below.

public DbHelper(Context context) {
    super(context, "games.db", null, 1);
}

The onCreate method is called whenever the database is first created. For our simple example, we will create the below schema.

schema

So our onCreate method will now look like the below.

@Override
public void onCreate(SQLiteDatabase db) {
    String sql = "create table Games " +
                 "(PlayerId integer primary key, " +
                 "Result integer, " +
                 "PlayTime integer, " +
                 "Difficulty integer); ";
    db.execSQL(sql);
}

We will ignore the onUpdate method for now and focus on getting our database created with simple CRUD. Now that our DbHelper class is created, we can move on to the CRUD operations.

The Data Layer

Add another class to our project called DataLayer. This class will be responsible for using our DbHelper to perform our desired CRUD functions. We will start with the “C” portion, record creation. Let’s add a new method called AddGame to our DataLayer, giving us a class that looks like below.

public class DataLayer {

    private DbHelper _dbHelper;

    public DataLayer(Context c) {
        _dbHelper = new DbHelper(c);
    }

    public void AddGame(int playerId, int result, int playTime, int difficulty) {
        SQLiteDatabase db = _dbHelper.getWritableDatabase();
        try {
            ContentValues values = new ContentValues();
            values.put("PlayerId", playerId);
            values.put("Result", result);
            values.put("PlayTime", playTime);
            values.put("Difficulty", difficulty);

            db.insert("Games", "", values);
        } finally {
            if (db != null)
                db.close();
        }
    }
}

The code is pretty straightforward. First you’ll notice our constructor, where we are instantiating our DbHelper class that we created above. The Context object will be passed in from our app. For the AddGame method, we are keeping this bare bones, so we just pass in the four values to be written out. We use our DbHelper class to bring back a writable version of our database. We then hydrate a ContentValues object which contains name-value pairs that will be written out to our database. Next, we call the insert method on the database object returned by our DbHelper. Finally, we clean up by closing the database after our insert.

Testing Harness

To test our fun, we’ll whip up a mangled UI that we’ll use to call the methods as we go. Add a button to the main layout, and add an event handler that will instantiate our DataLayer and add a new game. The simple layout is below.

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

<Button
    android:text="Add Record"
    android:id="@+id/AddRecord"
    android:layout_width="wrap_content"
    android:layout_height="wrap_content"></Button>
</LinearLayout>

And below is the code for the main screen.

package com.personal.androidfun.databasetests;

import android.app.Activity;
import android.os.Bundle;
import android.view.View;
import android.view.View.OnClickListener;
import android.widget.Button;

public class main extends Activity implements OnClickListener {
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);

        Button b = (Button) findViewById(R.id.AddRecord);
        b.setOnClickListener(this);
    }

    @Override
    public void onClick(View v) {
        DataLayer d = new DataLayer(getBaseContext());
        d.AddGame(1, 2, 3, 4);
    }
}

Now you can run the app, press the button, and trust me that a record was written out! Yeah, I know, not very satisfactory.

Using sqlite3 With adb

There’s a better way to check out the status of our database than just trusting me. The Android SDK ships with sqlite3, a command line tool that you can use to issue queries against any database on your emulator. Additionally, the Android SDK has a tool called adb, or Android Debug Bridge, that allows you to essentially start a shell prompt on your emulator, as if it were a PC. We will be using both of these tools to first open our database and then verify that we our code is doing the right thing.

The first step is verifying that the Tools folder in the Android SDK is on your path so that we can launch the executable. The actual location may vary; mine is pictured below.

toolsFolder

Add this directory to your path and make sure your Android emulator is running. Then start a command prompt, type adb shell, and press enter.

adbShell

You now have a command shell on your emulator. You can issue standard *nix commands, such as ls and cd. To work with our newly created database, we have to find it. All databases on an Android device are created in /data/data/<your package name>/databases. In our case, we called our package com.personal.androidfun.databasetests, so we issue some cd commands to get to the right spot.

dbDir

So now we’ve used adb to open up a shell onto our emulator and navigate to where our newly created database is stored. Next we have to connect to it using sqlite3. To do this, type sqlite3 <database name>, where our database is called games.db.

dbConnected

And now we’re connected to our database. You can issue any valid SQL against your database, from CRUD to DDL. You can find full instructions on how to use the sqlite3 tool here.

The final step in this section is to verify that our application actually inserted a row into our database. The fact that the database exists verifies that our onCreate method actually created it. To verify our insertion, we issue a standard select statement, terminated by a semicolon.

insertVerification

There’s our simple row, with column values separated by pipes. So far, so good.

RUD…

We’ve taken care of the creation part of our CRUD requirement, now we need to take care of the rest. First, reading information. We need to add a method to our DataLayer class. For now, we will just select all records every time. We’ll create a small class to represent a row in the database, called GameResult.

package com.personal.androidfun.databasetests;

public class GameResult {
    public GameResult(int playerId, int result, int playTime, int difficulty) {
        PlayerId = playerId;
        Result = result;
        PlayTime = playTime;
        Difficulty = difficulty;
    }

    public int PlayerId;
    public int Result;
    public int PlayTime;
    public int Difficulty;
}

Next, we’ll update our DataLayer class to select all records from the table and hydrate a list of our new GameResult objects.

public ArrayList<GameResult> SelectGames() {
    SQLiteDatabase db = _dbHelper.getReadableDatabase();
    try {
        ArrayList<GameResult> results = new ArrayList<GameResult>();
        Cursor c = db.rawQuery("select * from Games", null);
        if (c.getCount() > 0) {
            c.moveToFirst();
            do {
                results.add(new GameResult(
                        c.getInt(c.getColumnIndex("PlayerId")),
                        c.getInt(c.getColumnIndex("Result")),
                        c.getInt(c.getColumnIndex("PlayTime")),
                        c.getInt(c.getColumnIndex("Difficulty"))));
            } while (c.moveToNext());
        }
        return results;
    } finally {
        if (db != null)
            db.close();
    }
}

We use the rawQuery method to do a simple select * from our table. We make sure we have records before we try to access our cursor. If we have records, we run through our cursor, hydrating a GameResult object for every row we find.

We’ll add another button to our layout, call it SelectRecords, and add some code to handle the selection. The updated layout looks like below.

<?xml version="1.0" encoding="utf-8"?>
<LinearLayout
    xmlns:android="http://schemas.android.com/apk/res/android"
    android:orientation="vertical"
    android:layout_width="fill_parent"
    android:layout_height="fill_parent">
    <Button
        android:text="Add Record"
        android:id="@+id/AddRecord"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"></Button>
    <Button
        android:text="Select Records"
        android:id="@+id/SelectRecords"
        android:layout_width="wrap_content"
        android:layout_height="wrap_content"></Button>
</LinearLayout>

Finally, we augment our button handler in our main class to handle both adding and querying, and wire it up to listen to click events on our new button.

public class main extends Activity implements OnClickListener {
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);

        Button b = (Button) findViewById(R.id.AddRecord);
        b.setOnClickListener(this);
        b = (Button) findViewById(R.id.SelectRecords);
        b.setOnClickListener(this);
    }

    @Override
    public void onClick(View v) {
        Button b = (Button) v;
        DataLayer d = new DataLayer(getBaseContext());

        switch (b.getId()) {
        case R.id.AddRecord:
            d.AddGame(1, 2, 3, 4);
            break;
        case R.id.SelectRecords:
            ArrayList<GameResult> results = d.SelectGames();
            Toast t = Toast.makeText(getBaseContext(), "# records: "
                    + results.size(), 1000);
            t.show();
            break;
        }
    }
}

And now we have the R part of CRUD. Run the app, click the Select Records button, and we should have something like below.

selectionPiece

For the U and the D part of our CRUD goal, I’ll skip the layout snippets and just show the code.

For our update piece, we will update all PlayTime columns in our database to be a random number. We again go back to our DataLayer class and add a new method.

public int UpdateGames() {
    SQLiteDatabase db = _dbHelper.getWritableDatabase();
    try {
        Random r = new Random();
        ContentValues values = new ContentValues();
        values.put("PlayTime", r.nextInt());

        int affected = db.update("Games", values, null, null);

        return affected;
    } finally {
        if (db != null)
            db.close();
    }
}

Notice that to perform an update we use the now-familiar ContentValues object, populating it only with the key-value pairs that we want to update.

And below is our new onClick handler in our main class (don’t forget to wire up the new Update button to the handler).

@Override
public void onClick(View v) {
    Toast t = null;

    Button b = (Button) v;
    DataLayer d = new DataLayer(getBaseContext());

    switch (b.getId()) {
    case R.id.AddRecord:
        d.AddGame(1, 2, 3, 4);
        break;
    case R.id.SelectRecords:
        ArrayList<GameResult> results = d.SelectGames();
        t = Toast.makeText(getBaseContext(),
                "# records: " + results.size(), 1000);
        t.show();
        break;
    case R.id.UpdateRecords:
        int affected = d.UpdateGames();
        t = Toast.makeText(getBaseContext(), "records affected: "
                + affected, 1000);
        t.show();
    }
}

And now we have the U part of our CRUD goal. To verify that our update ran successfully, switch back to our adb session and re-run our select statement.

updateVerification

We’re almost there. Now for the delete part, and fixing our schema so we can add more than one row for a player. Again, we go back to our DataLayer class and add a new method to handle deletes.

public int DeleteGames() {
    SQLiteDatabase db = _dbHelper.getWritableDatabase();
    try {
        int recordsDeleted = db.delete("Games", "1", null);
        return recordsDeleted;
    } finally {
        if (db != null)
            db.close();
    }
}

Note here that we are passing a “1” as the where clause to our delete. This tells the method to both delete everything and to return the number of rows that were deleted.

Then we add a new button to fire our Delete method, augment the onClick handler to call our new method on the DataLayer class, and wire up the new button to our handler. I’ll only show the augmented onClick handler below.

@Override
public void onClick(View v) {
    Toast t = null;

    Button b = (Button) v;
    DataLayer d = new DataLayer(getBaseContext());

    switch (b.getId()) {
    case R.id.AddRecord:
        d.AddGame(1, 2, 3, 4);
        break;
    case R.id.SelectRecords:
        ArrayList<GameResult> results = d.SelectGames();
        t = Toast.makeText(getBaseContext(),
                "# records: " + results.size(), 1000);
        t.show();
        break;
    case R.id.UpdateRecords:
        int affected = d.UpdateGames();
        t = Toast.makeText(getBaseContext(), "records affected: "
                + affected, 1000);
        t.show();
        break;
    case R.id.DeleteRecords:
        int deleted = d.DeleteGames();
        t = Toast.makeText(getBaseContext(), "records deleted: "
                + deleted, 1000);
        t.show();
        break;
    }
}

And that’s it – we’ve created a database and implemented very bare bones CRUD against it. But what about that hokey primary key?

Updating the Database Version

You’ll notice that our initial database version had PlayerId as the primary key. What this tells us is that we can only ever have one row per player in our database, which obviously goes against our goal of storing a row for every game every player ever finishes. We are going to use the onUpgrade method that we left unimplemented at the beginning to auto-upgrade our database.

For this we need to go back to the DbHelper class that we started with, which we review again below.

public class DbHelper extends SQLiteOpenHelper {

    public DbHelper(Context context) {
        super(context, "games.db", null, 1);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        String sql = "create table Games " + "(PlayerId integer primary key, "
                + "Result integer, " + "PlayTime integer, "
                + "Difficulty integer); ";
        db.execSQL(sql);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        // do nothing for now
    }
}
 

Upon reviewing the onUpgrade method, we see that we have two parameters specifying the old version of the database and the new version of the database. Our flawed database version started as version 1, as we can see in our constructor. We’ll decree our new database version to be version 2. To update our games database from version 1 to version 2, we will drop the existing table and create a new one that adheres to the below diagram.

newSchema

Our onUpgrade method will look like below.

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    if (oldVersion == 1 && newVersion == 2) {
        String sql = "drop table Games;";
        db.execSQL(sql);
        sql = "create table Games " + "(Id integer primary key, "
                + "PlayerId integer, " + "Result integer, "
                + "PlayTime integer, " + "Difficulty integer); ";
        db.execSQL(sql);
    }
}

And we have to pass the new version to our base class constructor.

public DbHelper(Context context) {
    super(context, "games.db", null, 2);
}

Now whenever the new version of our app that uses our version 2 database runs on a device with a version 1 database, the onUpgrade method is called to update the database schema. Our onUpgrade code is destructive in that it drops the table and recreates it; you can implement this in a safer fashion to preserve your users’ data.

Next Steps

Our bare bones CRUD app has about 0 robustness built in, but it does demonstrate the absolute basics of Android database access. Improving on said robustness is beyond the scope of this article, and is something we might be seeing in the next few weeks.