Getting started with database-backed content storage on Android

Introduction

In this tutorial I will cover some of the fundamentals of developing, implementing solid design design processes for database-backed storage in Android applications. 

This will cover simply the basics with a future post introducing some amazing ActiveAndroid style ORM libraries that will give extensive control over your stored data.

This tutorial assumes you are familiar with databases (generally) as well as the pretty straightforward syntax of SQLite. Feel free to review the query documentation for SQLite on their website here.

Fundamentals

To begin with, you will need to think about what data it is that you are storing, how you want to represent this data across different tables and, lastly, how you want to abstract the stored data in Java classes.

For this tutorial, let us assume a pretty straight forward example: storing a list of movies. Each movie has the following attributes:

• Title
• Genre
• Rating (1 - 5)
• Description

Each one of these attributes will become a column in our 'movies' table. If we were going to create such a table by hand we would likely use a query like this:

CREATE TABLE IF NOT EXISTS movies (

    _id INTEGER PRIMARY KEY, 

    title TEXT NOT NULL UNIQUE,

    genre TEXT,

    rating INTEGER,

    description TEXT);

It's important to realize the _id column specification. Many of the aspects of content providers in Android assume that the PRIMARY KEY 'ID' column will be called '_id'. We will get into this more later but suffice to say; '_id' is a good choice for the name.

The other columns are pretty straight forward and I included a constraint that no two entries can have the same 'title' value and that this value must not be NULL (can't think of a movie without a title).

With this information and the above SQLite statement, we can begin creating our application's contract class. This class will be a final class that defines the interface and structure of our actual schema.

This class should go something like this:

public final class MovieContract {

    public MovieContract() { }

    public static abstract class MovieEntry implements BaseColumns {

        public static final String TABLE_NAME = "movies";

        public static final String COLUMN_TITLE = "title";

        public static final String COLUMN_GENRE = "genre";

        public static final String COLUMN_RATING = "rating";

        public static final String COLUMN_DESCRIPTION = "description";

    }

}

Next we will create a class that inherits from SQLiteOpenHelper. This class handles most of the heavy lifting involved with opening and managing a SQLite database. Our should look something like this:

public class MovieDbHelper extends SQLiteOpenHelper {

    public static final int DATABASE_VERSION = 1;

    public static final String DATABASE_NAME = "movies.db";

    private static final String SQL_CREATE_MOVIES_DB = 

        "CREATE TABLE " + MovieEntry.TABLE_NAME + " (" +

        MovieEntry._ID + " INTEGER PRIMARY KEY, " +

        MovieEntry.COLUMN_TITLE + " TEXT NOT NULL UNIQUE, " +

        MovieEntry.COLUMN_GENRE + " TEXT, " +

        MovieEntry.COLUMN_RATING + " INTEGER, " +

        MovieEntry.COLUMN_DESCRIPTION + " TEXT)";

    private static final String SQL_DROP_MOVIES_DB =

        "DROP TABLE IF EXISTS " + MovieEntry.TABLE_NAME;

    public MovieDbHelper(Context context) {

        super(context, DATABASE_NAME, null, DATABASE_VERSION);

    }

    public void onCreate(SQLiteDatabase db) {

        db.execSQL(SQL_CREATE_MOVIES_DB);

    }

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

        // Drop old table and recreate when the version is incremented

        db.execSQL(SQL_DROP_MOVIES_DB);

        onCreate(db);

    }

    public void onDowngrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        // Provides us an opportunity to 'roll back' changes between version. We will 

        // simply drop and recreate the database all together.

        onUpgrade(db, oldVersion, newVersion);

    }

}

Not too bad so far right? With this class in place, we already have all the instrumentation we need to have Android create and open our database. Next we need to figure out how to add to, select from and otherwise modify the data in the database.

Making your database useful

Given our SQLiteOpenHelper class, we can now actually get a handle to the database within our activities code using:

MovieDbHelper mDbHelper = new MovieDbHelper(getContext());

To start with, let's create a private method that inserts a movie for us:

private long insertMovie(String title, String genre, int rating, String description) {

    SQLiteDatabase db = mDbHelper.getWritableDatabase();

    ContentValues values = new ContentValues();

    values.put(MovieEntry.COLUMN_TITLE, title);

    values.put(MovieEntry.COLUMN_GENRE, genre);

    values.put(MovieEntry.COLUMN_RATING, rating);

    values.put(MovieEntry.COLUMN_DESCRIPTION, description);

    long newRowId = db.insert(MovieEntry.TABLE_NAME, null, values);

    return newRowId;

}

Here we get a writable handle to our database, use the ContentValues class to package our column values and simply insert. The return value of db.insert(...) will determine what the _id value is for this new row or -1 if the insert failed.

Next we will, at some point, certainly need to query (or SELECT) data from the database. We will create a 'Movie' class to represent movies in the database as well as another private method for querying the database:

public class Movie {

    public final String title;

    public final String genre;

    public final int rating;

    public final String description;

    public Movie(Cursor data) {

        title = data.getString(data.getColumnIndexOrThrow(MovieEntry.COLUMN_TITLE));

        genre = data.getString(data.getColumnIndexOrThrow(MovieEntry.COLUMN_GENRE));

        rating = data.getInt(data.getColumnIndexOrThrow(MovieEntry.COLUMN_RATING));

        description = data.getString(data.getColumnIndexOrThrow(MovieEntry.COLUMN_DESCRIPTION));

    }

}

In this class, the constructor method takes a single Cursor argument which provides all of the our stored database information. From here, we can extrapolate which column represents which data field.

Managing your data

Now that we can insert data and query the existing records. We will need to implement the ability to delete records as well as update existing records. The paradigm for doing such is actually fairly similar to the insertion and querying functions.

To delete a record (or all records). We will use something like the following:

private boolean deleteMovie(long _id) {

    SQLiteDatabase db = mDbHelper.getWritableDatabase();    

    int c = db.insert(MovieEntry.TABLE_NAME, String.format(MovieEntry._ID + "= %d", _id), null);

    // The integer 'c' will represent how many rows have been deleted. 

    // It should be one in this case

    return (c == 1);

}

As you can probably tell, by modifying the second argument to db.insert(...) you could also specify something like:

MovieEntry.COLUMN_TITLE + "=" title

Keep in mind, you can have the SQLiteDatabase class automatically escale '?' arguments as follows:

int c = db.insert(MovieEntry.TABLE_NAME, MovieEntry.COLUMN_TITLE + "= ?", new String[] { title });

This helps protect unsanitized input from SQL injection attacks and is highly recommended unless you are explicitly specifying the attributes yourself.

This would allow you to delete all rows that match the specified 'title' column.

Now that we can delete entries, let's look into updating existing record. This is much like creating a new entry but we have another argument allowing us to specify which records should match. Check the documentation for update as well.

Here is an example:

private boolean deleteMovie(long _id, ContentValues newValues) {

    SQLiteDatabase db = mDbHelper.getWritableDatabase();    

    int c = db.update(MovieEntry.TABLE_NAME, newValues, String.format(MovieEntry._ID + "= %d", _id), null);

    // The integer 'c' will represent how many rows have been updated.
    // In this case it should only be one 

    return (c == 1);

}

As stated before, you should try to make use of the of the whereArgs argument and allow it to populate your '?' arguments provided to the whereClause argument when possible and certainly when from untrusted sources.

In Conclusion

In closing, I hope this helps better illustrate the somewhat unique way that Android applications handle working with SQLite databases. As I said before, I will provide another article in the future about some of the many amazing things ORM-based / ActiveRecord libraries such as those provided by ActiveAndroid provide. Please let me know what you think, and thank you for reading.

Further Reading

Android SQLite Database & Content Provider - Tutorial (By Lars Vogel) - Great overview of the fundamentals and basic implementations of database-backed content storage

Saving Data in SQL Databases (Android Developers Guide) - Nice overview with rich examples of the right way to persist data using SQLite databases