Which SQLite design pattern is currently recommended?

Question

I was thinking of implementing a singleton design pattern for a database that my app has rather than going through the costly operation of making and destroying instances of my database.

However, a little while back I'd read that there were a couple of options for Android and SQLite (but I can't remember what, and I can't find the link) and so I was wondering, what design patterns are recommended for these databases, and what are their pros/cons?

Thanks, Liam

Answer 1

I use this aproach. I use these singleton classes SQLiteHelper, SelectionBuilder.

public class SQLiteHelper extends SQLiteOpenHelper {

public static final int DATABASE_VERSION = 1;
public static final String DATABASE_NAME = "test.db";

public static final String PRIMARY_KEY = " PRIMARY KEY";
public static final String TYPE_TEXT = " TEXT";
public static final String TYPE_INTEGER = " INTEGER";
public static final String TYPE_REAL = " REAL";
public static final String COMMA_SEP = ",";

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

@Override
public void onCreate(SQLiteDatabase db) {
    db.execSQL(Category.SQL_CREATE_TABLE);
    db.execSQL(Article.SQL_CREATE_TABLE);
    db.execSQL(IntroImage.SQL_CREATE_TABLE);
    db.execSQL(Asset.SQL_CREATE_TABLE);
}

@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
    db.execSQL(Category.SQL_DROP_TABLE);
    db.execSQL(Article.SQL_DROP_TABLE);
    db.execSQL(IntroImage.SQL_DROP_TABLE);
    db.execSQL(Asset.SQL_DROP_TABLE);
}

}

SQLiteOpenHelper is singleton for db creation and upgrade. SelectionBuilder as its name says is helper for building selection clauses.

public class SelectionBuilder {
private static final String TAG = SelectionBuilder.class.getSimpleName();

private String mTable = null;
private Map<String, String> mProjectionMap = Maps.newHashMap();
private StringBuilder mSelection = new StringBuilder();
private ArrayList<String> mSelectionArgs = Lists.newArrayList();

/**
 * Reset any internal state, allowing this builder to be recycled.
 */
public SelectionBuilder reset() {
    mTable = null;
    mSelection.setLength(0);
    mSelectionArgs.clear();
    return this;
}

/**
 * Append the given selection clause to the internal state. Each clause is
 * surrounded with parenthesis and combined using {@code AND}.
 */
public SelectionBuilder where(String selection, String... selectionArgs) {
    if (TextUtils.isEmpty(selection)) {
        if (selectionArgs != null && selectionArgs.length > 0) {
            throw new IllegalArgumentException(
                    "Valid selection required when including arguments=");
        }

        // Shortcut when clause is empty
        return this;
    }

    if (mSelection.length() > 0) {
        mSelection.append(" AND ");
    }

    mSelection.append("(").append(selection).append(")");
    if (selectionArgs != null) {
        Collections.addAll(mSelectionArgs, selectionArgs);
    }

    return this;
}

public SelectionBuilder table(String table) {
    mTable = table;
    return this;
}

private void assertTable() {
    if (mTable == null) {
        throw new IllegalStateException("Table not specified");
    }
}

public SelectionBuilder mapToTable(String column, String table) {
    mProjectionMap.put(column, table + "." + column);
    return this;
}

public SelectionBuilder map(String fromColumn, String toClause) {
    mProjectionMap.put(fromColumn, toClause + " AS " + fromColumn);
    return this;
}

/**
 * Return selection string for current internal state.
 *
 * @see #getSelectionArgs()
 */
public String getSelection() {
    return mSelection.toString();
}

/**
 * Return selection arguments for current internal state.
 *
 * @see #getSelection()
 */
public String[] getSelectionArgs() {
    return mSelectionArgs.toArray(new String[mSelectionArgs.size()]);
}

private void mapColumns(String[] columns) {
    for (int i = 0; i < columns.length; i++) {
        final String target = mProjectionMap.get(columns[i]);
        if (target != null) {
            columns[i] = target;
        }
    }
}

@Override
public String toString() {
    return "SelectionBuilder[table=" + mTable + ", selection=" + getSelection()
            + ", selectionArgs=" + Arrays.toString(getSelectionArgs()) + "]";
}

/**
 * Execute query using the current internal state as {@code WHERE} clause.
 */
public Cursor query(SQLiteDatabase db, String[] columns, String orderBy) {
    return query(db, columns, null, null, orderBy, null);
}

/**
 * Execute query using the current internal state as {@code WHERE} clause.
 */
public Cursor query(SQLiteDatabase db, String[] columns, String groupBy,
                    String having, String orderBy, String limit) {
    assertTable();
    if (columns != null) mapColumns(columns);
    Log.v(TAG, "query(columns=" + Arrays.toString(columns) + ") " + this);
    return db.query(mTable, columns, getSelection(), getSelectionArgs(), groupBy, having,
            orderBy, limit);
}

/**
 * Execute distinct query using the current internal state as {@code WHERE} clause.
 */
public Cursor queryDistinct(SQLiteDatabase db, String[] columns, String orderBy) {
    return queryDistinct(db, columns, null, null, orderBy, null);
}
/**
 * Execute distinct query using the current internal state as {@code WHERE} clause.
 */
public Cursor queryDistinct(SQLiteDatabase db, String[] columns, String groupBy,
                    String having, String orderBy, String limit) {
    assertTable();
    if (columns != null) mapColumns(columns);
    Log.v(TAG, "queryDistinct(columns=" + Arrays.toString(columns) + ") " + this);
    return db.query(true, mTable, columns, getSelection(), getSelectionArgs(), groupBy, having,
            orderBy, limit);
}

/**
 * Execute update using the current internal state as {@code WHERE} clause.
 */
public int update(SQLiteDatabase db, ContentValues values) {
    assertTable();
    Log.v(TAG, "update() " + this);
    return db.update(mTable, values, getSelection(), getSelectionArgs());
}

/**
 * Execute delete using the current internal state as {@code WHERE} clause.
 */
public int delete(SQLiteDatabase db) {
    assertTable();
    Log.v(TAG, "delete() " + this);
    return db.delete(mTable, getSelection(), getSelectionArgs());
}
}

So these two helpers you can combine with ContentProvider for your models that you create and where you define your query, insert, update and delete methods using these helpers. Also for me best aproach is to use Google Sync Framework with Loaders.

http://developer.android.com/training/sync-adapters/index.html