Best practices for working with multiple tables

Question

I use a database with multiple tables in my application. I have an XML parser which needs to write data to two tables while parsing. I created two database adapters for both tables, but now I have a problem. When I'm working with one table, it's easy:

FirstDBAdapter firstTable = new FirstDBAdapter(mycontext);
firstTable.open(); // open and close it every time I need to insert something
                   // may be hundreds of times while parsing
                   // it opens not a table but whole DB     
firstTable.insertItem(Item);        
firstTable.close(); 

Since it's a SAX parser, in my opinion (maybe I'm wrong), this will be even better:

FirstDBAdapter firstTable = new FirstDBAdapter(mycontext);

@Override
public void startDocument() throws SAXException 
{
    firstTable.open(); // open and close only once
}

...
firstTable.insertItem(Item);
...

@Override
public void endDocument() throws SAXException 
{
    firstTable.close();
}

But how do I do it if I need to insert data to the second table? For example, if I have the second adapter, which I think will be a bad idea:

FirstDBAdapter firstTable = new FirstDBAdapter(mycontext);
SecondDBAdapter secondTable = new SecondDBAdapter(mycontext);

@Override
public void startDocument() throws SAXException 
{
    firstTable.open();
    secondTable.open(); 
}

Any thoughts on how to achieve this?

Answer 1

I've had success with creating an abstract base class with the database name/create statement and other shared info, and then extending it for every table. This way, I can keep all of my CRUD methods separate (which I much prefer). The only downside is that the DATABASE_CREATE statement(s) must reside in the parent class, and must include all of the tables, because new tables can't be added afterwards, but in my opinion that's a small price to pay to keep the CRUD methods for each table separate.

Doing this was fairly simple, but here are some notes:

• The create statement in the parent class must be broken up for each table, because db.execSQL cannot execute more than one statement.
• I changed all private vars/methods to protected, just in case.
• If you are adding tables to an existing application (not sure if this is specific to emulator), the application must be uninstalled and then reinstalled.

Here is the code for my abstract parent class, which was based on the Notepad Tutorial. The children simply extend this, calling the super's constructor (feel free to use this):

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);
        }
    }

    public AbstractDbAdapter(Context ctx) {
        this.mCtx = ctx;
    }

    public AbstractDbAdapter open() throws SQLException {
        mDbHelper = new DatabaseHelper(mCtx);
        mDb = mDbHelper.getWritableDatabase();
        return this;
    }

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

}

A slightly more detailed explanation is available here: http://pheide.com/page/11/tab/24#post13