In a previous post I mentioned how you can use GSON to parse JSON into Java models on Android, allowing you to communicate with a remote server and make use of the data it provides you.

The post was limited in that the example application would fetch the entire list of remote data every time the application started, putting stress on both the application and the server handling the request. It would be much more efficient to cache the data on the application side, and only request data that has actually been updated or newly created.

SQLite is a public domain database engine that is designed to be incredibly minimal and simplistic, with a tiny footprint, making it a perfect candidate for mobile devices. Luckily for us, Android comes equipped with the database out of the box, and a very handy library for interacting with it.

The Model

Taking the models from the previously mentioned post, we will be modelling a blog's Post object:

public class Post {

    private long _id;

    private String _title;
    private String _preview;
    private String _body;
    private String _url;

    private Date _date;

    //Various constructors, accessors, mutators, etc.
}

The Database Wrapper

As mentioned in the title, we will implement the SQLiteOpenHelper class in order to create and update our database on the fly. There are two important properties to this class, and two important methods:

public class DatabaseWrapper extends SQLiteOpenHelper {

    private static final String TAG = "DatabaseWrapper";

    private static final String DATABASE_NAME = "MyDatabase.db";
    private static final int DATABASE_VERSION = 1;

    public DatabaseWrapper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    /**
     * Called if the database named DATABASE_NAME doesn't exist in order to create it.
     */
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        Log.i(TAG, "Creating database [" + DATABASE_NAME + " v." + DATABASE_VERSION + "]...");
        //TODO: Create the Database
    }

    /**
     * Called when the DATABASE_VERSION is increased.
     */
    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
        Log.i(TAG, "Upgrading database ["+DATABASE_NAME+" v." + oldVersion+"] to ["+DATABASE_NAME+" v." + newVersion+"]...");
    }
}

There are two important properties in this class that are pretty self-explanatory:

• DATABASE_NAME: The name of the Database
• DATABASE_VERSION: The version of the Database

The method onCreate will be called if the database named DATABASE_NAME doesn't exist so that you can create it, and the method onUpgrade will be called if the DATABASE_VERSION has been increased since last time the database was accessed, allowing you to migrate to your newer database design or recreate the database altogether if you wish.

We will come back to these methods shortly.

Creating a Custom ORM

An Object-Relational Mapper (or ORM) is a technique for transferring data between types, and is something we will implement for our Post class. The reason for implementing the ORM is it makes maintaining SQL queries and column-to-property mapping much simpler. Our ORM will be very simple, and consist of just a few methods to fetch, insert, update and delete posts from the database.

The first step is to define our schema so that we can create our database:

public class PostORM {

    private static final String TAG = "PostORM";

    private static final String TABLE_NAME = "post";

    private static final String COMMA_SEP = ", ";

    private static final String COLUMN_ID_TYPE = "INTEGER PRIMARY KEY";
    private static final String COLUMN_ID = "id";

    private static final String COLUMN_TITLE_TYPE = "TEXT";
    private static final String COLUMN_TITLE = "title";

    private static final String COLUMN_PREVIEW_TYPE = "TEXT";
    private static final String COLUMN_PREVIEW = "preview";

    private static final String COLUMN_BODY_TYPE = "TEXT";
    private static final String COLUMN_BODY = "body";

    private static final String COLUMN_URL_TYPE = "TEXT";
    private static final String COLUMN_URL = "url";

    private static final String COLUMN_DATE_TYPE = "TEXT";
    private static final String COLUMN_DATE = "pubdate";


    public static final String SQL_CREATE_TABLE =
            "CREATE TABLE " + TABLE_NAME + " (" +
                COLUMN_ID + " " + COLUMN_ID_TYPE + COMMA_SEP +
                COLUMN_TITLE  + " " + COLUMN_TITLE_TYPE + COMMA_SEP +
                COLUMN_PREVIEW + " " + COLUMN_PREVIEW_TYPE + COMMA_SEP +
                COLUMN_BODY + " " + COLUMN_BODY_TYPE + COMMA_SEP +
                COLUMN_URL + " " + COLUMN_URL_TYPE + COMMA_SEP +
                COLUMN_DATE + " " + COLUMN_DATE_TYPE +
            ")";

    public static final String SQL_DROP_TABLE =
            "DROP TABLE IF EXISTS " + TABLE_NAME;
}

We defined the TABLE_NAME and a series of columns with associated data-types, as well as a CREATE TABLE SQL statement based on these columns, and a DROP TABLE statement. With these defined, we can go back to the DatabaseWrapper class and implement the onCreate and onUpgrade methods.

public class DatabaseWrapper extends SQLiteOpenHelper {

	...

    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        Log.i(TAG, "Creating database [" + DATABASE_NAME + " v." + DATABASE_VERSION + "]...");

        sqLiteDatabase.execSQL(PostORM.SQL_CREATE_TABLE);
        sqLiteDatabase.close();
    }

    @Override
    public void onUpgrade(SQLiteDatabase sqLiteDatabase, int oldVersion, int newVersion) {
        Log.i(TAG, "Upgrading database ["+DATABASE_NAME+" v." + oldVersion+"] to ["+DATABASE_NAME+" v." + newVersion+"]...");

        sqLiteDatabase.execSQL(PostORM.SQL_DROP_TABLE);
        onCreate(sqLiteDatabase);
    }
}

When creating the table we simply execute the CREATE TABLE statement that we wrote earlier, however when upgrading the data we drop the table and recreate it. The reason I chose to do it this way instead of actually migrating to a new database design is because writing the appropriate SQL to update through all versions between the current version of the database and the version you are trying to migrate to can be very tedious. For a client side cache that is used simply for reducing network use, it didn't seem worth it. If you'd prefer to manage all that data migration, that is entirely up to you.

Anyways, now that the Database is in place, we can start actually inserting data. Here's how that looks for our Post model:

public class PostORM {

	...

    public static void insertPost(Context context, Post post) {
        DatabaseWrapper databaseWrapper = new DatabaseWrapper(context);
        SQLiteDatabase database = databaseWrapper.getWritableDatabase();

        ContentValues values = postToContentValues(post);
        long postId = database.insert(PostORM.TABLE_NAME, "null", values);
        Log.i(TAG, "Inserted new Post with ID: " + postId);

        database.close();
    }

    /**
     * Packs a Post object into a ContentValues map for use with SQL inserts.
     */
    private static ContentValues postToContentValues(Post post) {
        ContentValues values = new ContentValues();
        values.put(PostORM.COLUMN_ID, post.getId());
        values.put(PostORM.COLUMN_TITLE, post.getTitle());
        values.put(PostORM.COLUMN_PREVIEW, post.getPreview());
        values.put(PostORM.COLUMN_BODY, post.getBody());
        values.put(PostORM.COLUMN_URL, post.getUrl());
        values.put(PostORM.COLUMN_DATE, _dateFormat.format(post.getDate()));

        return values;
    }
}

First we get a hold of the SQLiteDatabase in write mode through our DatabaseWrapper. Once the database is available to us, we pack the Post object into a ContentValues map with the appropriate column names used as the key for each property. After that, we are free to insert the Post into the database. Using the PostORM class to insert a Post into the database is now simple:

Post post = new Post();
...
PostORM.insertPost(context, post);

With inserts in place, let's fetch a list of all Posts in the database:

public class PostORM {

	...

    private static final SimpleDateFormat _dateFormat = new SimpleDateFormat("MMMM d, yyyy", Locale.ENGLISH);

    public static List<Post> getPosts(Context context) {
        DatabaseWrapper databaseWrapper = new DatabaseWrapper(context);
        SQLiteDatabase database = databaseWrapper.getReadableDatabase();

        Cursor cursor = database.rawQuery("SELECT * FROM " + PostORM.TABLE_NAME, null);

        Log.i(TAG, "Loaded " + cursor.getCount() + " Posts...");
        List<Post> postList = new ArrayList<Post>();

        if(cursor.getCount() > 0) {
            cursor.moveToFirst();
            while (!cursor.isAfterLast()) {
                Post post = cursorToPost(cursor);
                postList.add(post);
                cursor.moveToNext();
            }
            Log.i(TAG, "Posts loaded successfully.");
        }

        database.close();

        return postList;
    }

    /**
     * Populates a Post object with data from a Cursor
     * @param cursor
     * @return
     */
    private static Post cursorToPost(Cursor cursor) {
        Post post = new Post();
        post.setId(cursor.getLong(cursor.getColumnIndex(COLUMN_ID)));
        post.setTitle(cursor.getString(cursor.getColumnIndex(COLUMN_TITLE)));
        post.setPreview(cursor.getString(cursor.getColumnIndex(COLUMN_PREVIEW)));
        post.setBody(cursor.getString(cursor.getColumnIndex(COLUMN_BODY)));
        post.setUrl(cursor.getString(cursor.getColumnIndex(COLUMN_URL)));

        String date = cursor.getString(cursor.getColumnIndex(COLUMN_DATE));
        try {
            post.setDate(_dateFormat.parse(date));
        } catch (ParseException ex) {
            Log.e(TAG, "Failed to parse date " + date + " for Post " + post.getId());
            post.setDate(null);
        } 

        return post;
    }
}

Similar to inserting a Post, the first step is to get a handle on the database, except this time we get it in read mode. Once available, we query the database for all posts. SELECT queries return a Cursor object which allows you to iterate through it and retrieve values from each column it contains. In the cursorToPost method we create a Post object from the values stored in the Cursor by retrieving properties based on the index of their applicable column. This method will also be handy in the future if you implement other queries such as finding a post by it's id or finding all posts between a certain data range, for example.

By now you should be well on your way to implementing a client-side cache in your Android application. Let me know in the comments if you would like to see examples of UPDATE and DELETE statements. As always, the source is available on Github as part of a larger project I'm working on.