Things I wish I'd known about sooner: Lists, SQLite and ContentProviders

This tutorial is good if you want to learn about SQLite and how to use it effectively in Android. I have upgraded the project to Android Studio so some of the Eclipse-centric content is slightly out-dated, but the gist of it is the same. All the same functionality is present in Android Studio as it was in Eclipse, with the same kind of wizards etc. You can also enable a setting in Android Studio so most of the key commands are the same.

Introduction

Lists must be one of the most ubiquitous UI-elements. What app doesn't have them? If they don't they probably have some kind of grid view. This tutorial will apply to them both. The final result will look like this:

The structure of the tutorial might not be common. I am going to start by creating the app, documenting all the steps on the way. Once that's done, I will explain some things in more detail.

To start with, we're going to have to start a new project. This means there will be a small amount of boiler plate to deal with before we can actually get our code running.

Somewhere in the middle, I will be taking a detour to make those nice looking layouts. If you don't care about the layout stuff, just skip that section.

The final step is to make the app use the new layouts, and load the data from our database into them.

Once the app is complete, I can explain a few things in more detail by pointing to the code just created.

Motivation

Many tutorials only show how to use ArrayAdapters and I find that problematic. ArrayAdapters are fine if you're showing a static list, but I think most devs have something more dynamic in mind. Simple rule of thumb is, if the data backing the list can change while the list is displaying it, then you want to use a ContentProvider. While it isn't the only way to get everything working, together with Loaders, it's just very smooth. The data is fetched on a background thread and is always up to date. And the system does it all for you (as long as you have a ContentProvider). What's not to like?

What's a ContentProvider

A ContentProvider is seperate from the actual data. In this example, and many real world applications, the data is stored in an SQLite database. But it doesn't have to be. We will use the Provider to supply our list with content. The advantage over a simple ArrayAdapter will be that if the data changes, the list will change, all by itself, as it happens.

What's a Loader

Loader is a class offered by the Android Framework. It loads (surprise) data in a background thread and offers a callback interface to allow you to use that data once it's loaded. We will focus entirely on the case of using the Loader together with a database cursor. You can use a Loader to load anything in the background though. So if you have data stored as txt files, you can use a Loader then as well.

What's SQLite and why do I want to store data in a database

SQLite is a dialect of SQL, which stands for Structured Query Language. There are many dialects of SQL and this is the one supported by Android. It is a light and fast version of SQL which does not support all features present in other dialects, but you can do anything even though you might have to phrase it differently.

The reason you want to store your data in a SQLite database in the first place is because it is fast. I mean deadly fast. Properly coded, you can do full text searches among millions of entries in real time.

Let's start our project

I will be using Eclipse together with the standard SDK only. Target will be Jelly Bean MR2 (Android 4.2), but compatibility will be preserved back to Ice Cream Sandwich. I am assuming that you have managed to install Eclipse together with the SDK and are good to go. If not, follow the steps here. Make sure you have downloaded the SDK for the Android version you intend to compile against (in our case, android-17). This is not the same as the least required android version to run your app.

Create a new project

Go to the menu, select New, Other, Android Application Project.

I am going to go with the MasterDetail style to get a nice two-pane view on tablets for free.

I selected to name my "items" Persons.

A note about Android versions

Many outdated tutorials and things talk about supporting Android versions as far back as 1.6. Don't. If you really want to reach 95% of the market, set your minimum requirement to Gingerbread 2.3.3 (this is android-10 in the SDK). The reason is that several nice APIs and tools were introduced then and not using them is only stupid. If you only want to make an app for your own enjoyment and actually have a modern device, or were smart enough to get the Nexus S back in the day, put your least required version to ICS (android-14). ICS also introduced a lot of great APIs which you don't want to miss out on. Many of them can be used with the support package but to get the action bar and good looks from ICS, you'll need some library like ActionBarSherlock, and that's a tutorial for another day. The project wizard adds the support library by default, so staying backwards compatible shouldn't be too hard.

Let's make our database

By selecting the MasterDetail style in the wizard, we got a lot of code for free. You will only need to concern yourself with the two fragment classes, named PersonDetailFragment and PersonListFragment in my case. The listFragment extends ListFragment (surprise), but I'll show how to do this in the general case in case you want to use a generic fragment (or activity) later.

The wizard created a dummy datastore which is just a fixed array of items. The first thing we want to do is to replace this with the SQLite database.

Create the DatabaseHandler

First create a new package for your database (not necessary, it's just nice). I'll name it "com.example.providerexample.database".

Next, in that package create a new class called "DatabaseHandler".

Once you have the class open, make it extend "SQLiteOpenHelper" and save. Eclipse should now complain about SQLiteOpenHelper needing to be imported. Put the text cursor somewhere in "SQLiteOpenHelper", hit CTRL-1, and select "import SQLiteOpenHelper.."

Save. Now, we need to implement the needed methods. Put the text cursor instead somewhere in "DatabaseHandler", CTRL-1, "Add unimplemented methods..".

The last thing missing is a constructor. Instead of adding a default one, add the following code to the top of the class:

    private static DatabaseHandler singleton;

	public static DatabaseHandler getInstance(final Context context) {
		if (singleton == null) {
			singleton = new DatabaseHandler(context);
		}
		return singleton;
	}

	private static final int DATABASE_VERSION = 1;
	private static final String DATABASE_NAME = "providerExample";

	private final Context context;

	public DatabaseHandler(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
        // Good idea to use process context here
		this.context = context.getApplicationContext();
	}

Your project should no longer show any errors.

Before we make any further changes to the DatabaseHandler, let's make a Person class. We are now doing a kind of DAO/ORM approach (Data As Objects and Object Relational Mapping respectively), basically a Class will be responsible for converting the data stored in the database into variables useable in the Java code. The advantage of this approach, as compared to dealing with the database directly everytime is primarily safety and convenience. Putting all the code associated with a type of data in one place is good practice. If the data defnition has to change, then that class should be the only place that needs changes ideally.

Creating the Person class

So, once again create a new Class in the database package. I name my class "Person" to be consistent. I now decide that every person will have a first name, last name (never make that assumption in real life) and a "bio".

The class is fairly simple but it's easier to show the finished result:

package com.example.providerexample.database;

import android.content.ContentValues;
import android.database.Cursor;

/**
 * A class representation of a row in table "Person".
 */
public class Person {

	// SQL convention says Table name should be "singular", so not Persons
	public static final String TABLE_NAME = "Person";
	// Naming the id column with an underscore is good to be consistent
	// with other Android things. This is ALWAYS needed
	public static final String COL_ID = "_id";
	// These fields can be anything you want.
	public static final String COL_FIRSTNAME = "firstname";
	public static final String COL_LASTNAME = "lastname";
	public static final String COL_BIO = "bio";

	// For database projection so order is consistent
	public static final String[] FIELDS = { COL_ID, COL_FIRSTNAME, COL_LASTNAME,
		COL_BIO };

	/*
	 * The SQL code that creates a Table for storing Persons in.
	 * Note that the last row does NOT end in a comma like the others.
	 * This is a common source of error.
	 */
	public static final String CREATE_TABLE =
			"CREATE TABLE " + TABLE_NAME + "("
			+ COL_ID + " INTEGER PRIMARY KEY,"
			+ COL_FIRSTNAME + " TEXT NOT NULL DEFAULT '',"
			+ COL_LASTNAME + " TEXT NOT NULL DEFAULT '',"
			+ COL_BIO + " TEXT NOT NULL DEFAULT ''"
			+ ")";

	// Fields corresponding to database columns
	public long id = -1;
	p