C0mput3rz & T3chno1ogy

In developing my first android application, I came across the "problem" of creating and working with multiple database tables. Initially I just created a second DbAdapter class, with all of the information for my second table. However, I was ending up with some cryptic runtime exceptions being thrown whenever trying to run a select statement on the table (which turned out to not exist).

Searching the internet, surprisingly, the best example I could find was putting all of the CRUD methods for every table within one huge DbAdapter class. Obviously this is not ideal, because your class could become thousands of lines long. So, after a bit of playing, I came up with another solution where each table has its own DbAdapter class which simply extends an AbstractDbAdapter. The code for the abstract class is below, which was initially based on the Notepad Tutorial. Some notes beforehand:

• The create statement(s) must all go in the abstract parent class, because the tables must all be created at the same time. Obviously this is not ideal (because then the parent must be updated each time you create a new child), but I think it's a small price to pay for being able to keep all of the CRUD methods separate. Note that in order to add a table to an existing app, you must uninstall it first.
• The create statements must be separated inside the parent class, because db.execSQL() can only execute one statement at a time.
• You'll notice I've also changed all private vars/methods to protected (though I'm not sure if it's necessary in every case).

So, the full code for my abstract class:

package com.pheide.trainose;

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

public abstract class AbstractDbAdapter {

    protected static final String TAG = "TrainOseDbAdapter";
    protected DatabaseHelper mDbHelper;
    protected SQLiteDatabase mDb;

    protected static final String TABLE_CREATE_ROUTES =
        "create table routes (_id integer primary key autoincrement, "
        + "source text not null, destination text not null);";
    protected static final String TABLE_CREATE_TIMETABLES =    
        "create table timetables (_id integer primary key autoincrement, "
    	+ "route_id integer, depart text not null, arrive text not null, "
    	+ "train text not null);";

    protected static final String DATABASE_NAME = "data";
    protected static final int DATABASE_VERSION = 2;

    protected final Context mCtx;

    protected static class DatabaseHelper extends SQLiteOpenHelper {

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

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(TABLE_CREATE_ROUTES);
            db.execSQL(TABLE_CREATE_TIMETABLES);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS routes");
            onCreate(db);
        }
    }

    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     */
    public AbstractDbAdapter(Context ctx) {
        this.mCtx = ctx;
    }

    /**
     * Open or create the routes database.
     * 
     * @return this
     * @throws SQLException if the database could be neither opened or created
     */
    public AbstractDbAdapter open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }

    public void close() {
        mDbHelper.close();
    }

}

Each child class will then simply extend this class, run the parent's constructor, and include all of the CRUD methods etc for that specific table. An example child class:

package com.pheide.trainose;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;

public class RoutesDbAdapter extends AbstractDbAdapter {

	public static final String KEY_SOURCE = "source";
	public static final String KEY_DESTINATION = "destination";
    public static final String KEY_ROWID = "_id";

    private static final String DATABASE_TABLE = "routes";

    /**
     * Constructor - takes the context to allow the database to be
     * opened/created
     * 
     * @param ctx the Context within which to work
     */
    public RoutesDbAdapter(Context ctx) {
    	super(ctx);
    }
    
    /**
     * Create a new route.
     * 
     * @param source
     * @param destination
     * @return rowId or -1 if failed
     */
    public long create(String source, String destination) {
    	ContentValues args = new ContentValues();
    	args.put(KEY_SOURCE,source);
    	args.put(KEY_DESTINATION,destination);
    	
    	return mDb.insert(DATABASE_TABLE, null,args);
    }
    

    /**
     * Delete the route with the given rowId
     * 
     * @param rowId
     * @return true if deleted, false otherwise
     */
    public boolean delete(long rowId) {
    	return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
    }

    /**
     * Return a Cursor over the list of all routes in the database
     * 
     * @return Cursor over all notes
     */
    public Cursor fetchAll() {
    	return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_SOURCE,
    			KEY_DESTINATION}, null, null, null, null, null);
    }

    /**
     * Return a Cursor positioned at the route that matches the given rowId
     * 
     * @param rowId id of route to retrieve
     * @return Cursor positioned to matching route, if found
     * @throws SQLException if route could not be found/retrieved
     */
    public Cursor fetch(long rowId) throws SQLException {
        Cursor mCursor =
            mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
                    KEY_SOURCE, KEY_DESTINATION}, KEY_ROWID + "=" + rowId, null,
                    null, null, null, null);
        if (mCursor != null) {
            mCursor.moveToFirst();
        }
        return mCursor;

    }