Android Application Part 6 - SQLLite Database

This post is part 6 of a series.  Click here to go to part 5.


In my previous post, I walked through the scaffolding for the ListService, which will query a REST Web service and sync data locally.  Of course, the code was just the implementation to allow the service to run in a new Thread.  Today I am going to start implementing the storage part of the application.  Android has a a SQLLite. Database.  The OS makes it available through a service.  It even comes packaged with a SQLLiteOpenHelper class.  The implementation for this one is rather easy.  Here I've modified the sample presented on the Android Developer Site.  First I'll show you the code, then I'll talk through what its doing.   

package com.arciszewski.family.shopping.db;

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

public class FamilyListOpenHelper extends SQLiteOpenHelper {

private static final int DATABASE_VERSION = 1;
private static final String FAMILY_LIST_TABLE_NAME = "familyList.db";
private static final String LIST_TABLE_CREATE = "CREATE TABLE LIST (ID INTEGER PRIMARY KEY AUTOINCREMENT, SERVER_ID INTEGER, NAME TEXT, DESCRIPTION TEXT);";
private static final String ITEM_TABLE_CREATE = "CREATE TABLE ITEM (ID INTEGER PRIMARY KEY AUTOINCREMENT, SERVER_ID INTEGER, NAME TEXT, LIST_ID INTEGER NOT NULL, NEEDED BOOLEAN, UPDATED_DATE TEXT, UPDATED_BY TEXT);";

public FamilyListOpenHelper(Context context) {
this(context, null);
}

public FamilyListOpenHelper(Context context, CursorFactory factory) {
super(context, FAMILY_LIST_TABLE_NAME, factory, DATABASE_VERSION);;
}

@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(LIST_TABLE_CREATE);
db.execSQL(ITEM_TABLE_CREATE);
}

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

}
}


So what is this code doing? First, I've defined which version of the DB this is:


DATABASE_VERSION = 1;


I like this feature of Android. They have built-in support for upgrading. You'll notice, I have not implemented the onUpgrade method, that is because I have no need for it yet. But when I come out with version 2.0, I will have the opportunity to perform upgrade operations.


Next, I define the table names and create SQL for the List and Item tables.


private static final String LIST_TABLE_CREATE = "CREATE TABLE LIST (ID INTEGER PRIMARY KEY AUTOINCREMENT, SERVER_ID INTEGER, NAME TEXT, DESCRIPTION TEXT);"; private static final String ITEM_TABLE_CREATE = "CREATE TABLE ITEM (ID INTEGER PRIMARY KEY AUTOINCREMENT, SERVER_ID INTEGER, NAME TEXT, LIST_ID INTEGER NOT NULL, NEEDED BOOLEAN, UPDATED_DATE TEXT, UPDATED_BY TEXT);";


Finally, I've implemented a constructor that will always open the Family List Database and return it to the caller. That's it. And that is pretty easy my friends. Of course, I'm not making this available as a service to other applications, which is slightly more complex.


Next, I'll implement some querying and saving.