Sunday, September 27, 2009

ActiveRecord Data Model for Android

This month I was researching ways to implement data access for my Android project. I found an interesting tutorial posted by Java Padawan available here. The author presents what is a "sort-of" ActiveRecord data model for Android. I really liked the idea and implemented my own version for use in my project. Today's article covers most of what was presented in the author's original tutorial plus a few other items that I felt were missing.

The Database Adapter Class

My implementation of the database adapter class is virtually indistinguishable from the original. All I did here was change the name of the class. The adapter is implemented as a singleton with a private constructor. The class manages the creation of the database as well as any upgrades between versions. The Owner class import will be covered in a later section. The class is required by adapter so it can create the table used by the Owner class. Database adapter class source (
package com.zunisoft.critters.db;

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

import com.zunisoft.critters.db.model.Owner;

public class DatabaseAdapter extends SQLiteOpenHelper {
    // Database adapter instance
    private static DatabaseAdapter dbAdapter;

    // Database object
    private static SQLiteDatabase db;

    // Database filename
    private static final String DATABASE_NAME = "critters.db";

    // Database version
    private static final int DATABASE_VERSION = 1;

    private DatabaseAdapter(Context context, String name,
            CursorFactory factory, int version) {
        super(context, name, factory, version);

    public void onCreate(SQLiteDatabase db) {

    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        Log.i(getClass().getSimpleName(), "Upgrading database from version "
                + oldVersion + " to " + newVersion
                + ", which will destroy all old data");
        // db.execSQL("DROP TABLE IF EXISTS " + Owner.SQL_TABLE_NAME);

    private static void initialize(Context context) {
        if (dbAdapter == null) {
            dbAdapter = new DatabaseAdapter(context, DATABASE_NAME, null,
            db = dbAdapter.getWritableDatabase();

    public static final DatabaseAdapter getInstance(Context context) {
        return dbAdapter;

    public SQLiteDatabase getDatabase() {
        return db;

    public void close() {
        if (dbAdapter != null) {
            dbAdapter = null;

The ActiveRecord Class

Java Padawan's ActiveRecord pattern was implemented through an interface. The original interface covered all the necessary CRUD operations plus a few others. In addition, my application also required data to be validated before being persisted to the database. I wanted to make a number of base validation methods available to all models that implemented ActiveRecord. An interface wasn't going to work. It appeared that implementing ActiveRecord as an abstract class was the way to go.

All of the original CRUD functions are abstract with the addition of a number of implemented validation functions. The class also includes an errors collection that makes it easy to retrieve any validation messages for use in the application's UI. The getErrors() method returns a HashMap where the key holds the database column and the value holds the corresponding error message. I also have to pass a reference to the application's resources. This enables the retrieval of any strings representing validation error messages.

ActiveRecord class source (
package com.zunisoft.critters.db;

import java.util.HashMap;
import java.util.regex.Pattern;

import android.content.res.Resources;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

public abstract class ActiveRecord {
    /** Database object */
    protected SQLiteDatabase db;
    /** Errors collection - database column name|error message */
    protected HashMap<string, string> errors = new HashMap();
    /** Application resources object */
    protected Resources res;
    public ActiveRecord(Resources res) {
        this.res = res;
    abstract public long save();

    abstract public boolean delete();

    abstract public void load(Activity activity);

    abstract public Cursor retrieveAll();
    public void setSQLiteDatabase(SQLiteDatabase db) {
        this.db = db;
    public HashMap<string, string> getErrors() {
        return errors;
    public boolean validate() {
        return true;
    protected boolean validatePresenceOf(String value, String colName,
            String msg) {
        boolean validated = true;
        if (value.length() == 0) {
            errors.put(colName, msg);
            validated = false;
        return validated;
    protected boolean validateFormatOf(String value, String regex,
            String colName, String msg) {
        boolean validated = true;
        if (!Pattern.matches(regex, value)) {
            errors.put(colName, msg);
            validated = false;
        return validated;
    protected boolean validateLengthOf(String value, int min, int max,
            String colName, String msg) throws InvalidParameterException {
        boolean minValidated = true;
        boolean maxValidated = true;

        // Check for invalid length parameters
        if (min < 0 || max < 0 || min > max)
            throw new InvalidParameterException();
        // Check minimum length
        if (value.length() < min) {
            minValidated = false;
        // Check maximum length
        if (value.length() > max) {
            maxValidated = false;
        // Check the results and return
        if (minValidated && maxValidated) {
            return true;
        } else {
            errors.put(colName, msg);
            return false;

Implementation Example

Below is a data model implementation example from my own project. The validate() method checks a number of fields for formatting and mandatory values. The save() method calls the validate() method, performing any validation before that actual save takes place. One can also note where the application's resources are being used to define the validation error messages.
Owner class source, most field getters and setters removed for brevity (
package com.zunisoft.critters.db.model;

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

import com.zunisoft.critters.R;
import com.zunisoft.critters.db.ActiveRecord;

public class Owner extends ActiveRecord {
    // Database table columns
    public static final String COL_ROW_ID = "_id";  
    public static final String COL_TITLE = "title";
    public static final String COL_LAST_NAME = "last_name";
    public static final String COL_FIRST_NAME = "first_name";
    public static final String COL_ADDRESS = "address";
    public static final String COL_CITY = "city";
    public static final String COL_STATE = "state";
    public static final String COL_POSTAL_CODE = "postal_code";
    public static final String COL_DAY_PHONE = "daytime_phone";
    public static final String COL_EVENING_PHONE = "evening_phone";
    public static final String COL_MOBILE_PHONE = "mobile_phone";
    public static final String COL_EMAIL_ADDRESS = "email_address";
    public static final String COL_PREFERRED_CONTACT_BY = "preferred_contact_by";

    /** Database table */
    public static final String SQL_TABLE_NAME = "owner";
    /** Database table DDL create statement */
    public static final String SQL_CREATE_TABLE = "CREATE TABLE "
        + SQL_TABLE_NAME + " ("
        + COL_ROW_ID + " integer primary key autoincrement, "
        + COL_TITLE + " text null, "
        + COL_LAST_NAME + " text not null, "
        + COL_FIRST_NAME + " text not null, "
        + COL_ADDRESS + " text not null, "
        + COL_CITY + " text not null, "
        + COL_STATE + " text not null, "
        + COL_POSTAL_CODE + " text not null, "
        + COL_DAY_PHONE + " text not null, "
        + COL_EVENING_PHONE + " text null, "
        + COL_MOBILE_PHONE + " text null, "
        + COL_EMAIL_ADDRESS + " text null, "
        + COL_PREFERRED_CONTACT_BY + " integer not null default 0 "
        + "); ";

    /** Contact preference types */
    public enum ContactPreference {

    // Data members
    private int id;
    private String title;
    private String lastName;
    private String firstName;
    private String address;
    private String city;
    private String state;
    private String postalCode;
    private String dayPhone;
    private String eveningPhone;
    private String mobilePhone;
    private String emailAddress;
    private ContactPreference preferredContactBy;
    public Owner(Resources res) {
    public void load(Activity activity) {
        Cursor cursor = db.query(true, SQL_TABLE_NAME,
                new String[] { COL_ROW_ID, COL_TITLE, COL_LAST_NAME,
                        COL_FIRST_NAME, COL_ADDRESS, COL_CITY,
                        COL_STATE, COL_POSTAL_CODE, COL_DAY_PHONE,
                        COL_PREFERRED_CONTACT_BY }, COL_ROW_ID + "=" + id,
                null, null, null, null, null);
        if (cursor != null) {

    public Cursor retrieveAll() {
        return db.query(SQL_TABLE_NAME, new String[] { COL_ROW_ID, COL_TITLE,
                COL_PREFERRED_CONTACT_BY }, null, null,
                null, null, null);

    public long save() {
        long retval;

        if (this.validate()) {
            ContentValues values = new ContentValues();
            values.put(COL_TITLE, title);
            values.put(COL_LAST_NAME, lastName);
            values.put(COL_FIRST_NAME, firstName);
            values.put(COL_ADDRESS, address);
            values.put(COL_CITY, city);
            values.put(COL_STATE, state);
            values.put(COL_POSTAL_CODE, postalCode);
            values.put(COL_DAY_PHONE, dayPhone);
            values.put(COL_EVENING_PHONE, eveningPhone);
            values.put(COL_MOBILE_PHONE, mobilePhone);
            values.put(COL_EMAIL_ADDRESS, emailAddress);
            values.put(COL_PREFERRED_CONTACT_BY, preferredContactBy.ordinal());

            if (id <= 0) {
                retval = db.insert(SQL_TABLE_NAME, null, values);
            } else {
                retval = db.update(SQL_TABLE_NAME, values, COL_ROW_ID + "="
                        + id, null);
        } else {
            retval = -1;
        return retval;

    public boolean delete() {
        return db.delete(SQL_TABLE_NAME, COL_ROW_ID + "=" + id,
                null) > 0;

    public boolean validate() {
        boolean validated = true;
        // First name - required
        if (!this.validatePresenceOf(lastName, COL_LAST_NAME, res
            validated = false;
        // Last name - required
        if (!this.validatePresenceOf(firstName, COL_FIRST_NAME, res
            validated = false;
        // Address - required
        if (!this.validatePresenceOf(address, COL_ADDRESS, res
            validated = false;
        // City - required
        if (!this.validatePresenceOf(city, COL_CITY, res
            validated = false;
        // Postal code - required with format
        if (this.validatePresenceOf(postalCode, COL_POSTAL_CODE, res
                .getString(R.string.validation_required_field))) {
            if (!this.validateFormatOf(postalCode, res
                    .getString(R.string.regex_postal_code), COL_POSTAL_CODE,
                validated = false;
        } else {
            validated = false;
        // Day phone - required with format
        if (this.validatePresenceOf(dayPhone, COL_DAY_PHONE, res
                .getString(R.string.validation_required_field))) {
            if (!this.validateFormatOf(dayPhone, res
                    .getString(R.string.regex_phone), COL_DAY_PHONE,
                validated = false;
        } else {
            validated = false;
        // Evening phone - format
        if (eveningPhone.length() > 0) {
            if (!this.validateFormatOf(eveningPhone, res
                    .getString(R.string.regex_phone), COL_EVENING_PHONE,
                validated = false;
        // Mobile phone - format
        if (mobilePhone.length() > 0) {
            if (!this.validateFormatOf(mobilePhone, res
                    .getString(R.string.regex_phone), COL_MOBILE_PHONE,
                validated = false;
        // E-mail address - format
        if (emailAddress.length() > 0) {
            if (!this.validateFormatOf(emailAddress, res
                    .getString(R.string.regex_email), COL_EMAIL_ADDRESS,
                validated = false;
        return validated;
    public int getId() {
        return id;

    public void setId(int id) { = id;

    // Place holder for removed getters and setters

    public ContactPreference getPreferredContactBy() {
        return preferredContactBy;

    public void setPreferredContactBy(ContactPreference contactPreference) {
        this.preferredContactBy = contactPreference;


I did not include a sample project to tie everything together as I felt that the original article did a good job in that respect. My extensions are by no means complete. One could go on and add more validation methods such as numericalityOf and uniquenessOf as well as model associations. I would like to thank Java Padawan for the original tutorial as it was a great help to me in defining a data model for my own application.