Best practices for working with multiple tables

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

My database adapter. An instance is always stored in MyApplication which inherites from Application. Just think about a second table where I defined the first one... currently this is just a short version, in reality this adapter handles 7 tables in the database.

public class MyDbAdapter {    private static final String LOG_TAG = MyDbAdapter.class.getSimpleName();    private SQLiteDatabase mDb;    private static MyDatabaseManager mDbManager;    public MyDbAdapter() {        mDbManager = new MyDatabaseManager(MyApplication.getApplication());        mDb = mDbManager.getWritableDatabase();    }    public static final class GameColumns implements BaseColumns {        public static final String TABLE = "game";        public static final String IMEI = "imei";        public static final String LAST_UPDATE = "lastupdate";        public static final String NICKNAME = "nickname";    }    public String getImei() {        checkDbState();        String retValue = "";        Cursor c = mDb.rawQuery("SELECT imei FROM " + GameColumns.TABLE, null);        if (c.moveToFirst()) {            retValue = c.getString(c.getColumnIndex(GameColumns.IMEI));        }        c.close();        return retValue;    }    public void setImei(String imei) {        checkDbState();        ContentValues cv = new ContentValues();        cv.put(GameColumns.IMEI, imei);        mDb.update(GameColumns.TABLE, cv, null, null);    }    public boolean isOpen() {        return mDb != null && mDb.isOpen();    }    public void open() {        mDbManager = new MyDatabaseManager(MyApplication.getApplication());        if (!isOpen()) {            mDb = mDbManager.getWritableDatabase();        }    }    public void close() {        if (isOpen()) {            mDb.close();            mDb = null;            if (mDbManager != null) {                mDbManager.close();                mDbManager = null;            }        }    }    private void checkDbState() {        if (mDb == null || !mDb.isOpen()) {            throw new IllegalStateException("The database has not been opened");        }    }    private static class MyDatabaseManager extends SQLiteOpenHelper {        private static final String DATABASE_NAME = "dbname";        private static final int DATABASE_VERSION = 7;        private MyDatabaseManager(Context context) {            super(context, DATABASE_NAME, null, DATABASE_VERSION);        }        @Override        public void onCreate(SQLiteDatabase db) {            createGameTable(db);        }        @Override        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {            Log.w(LOG_TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + "!");        }        private void dropDatabase(SQLiteDatabase db) {            db.execSQL("DROP TABLE IF EXISTS " + GameColumns.TABLE);        }        private void createGameTable(SQLiteDatabase db) {            db.execSQL("CREATE TABLE " + GameColumns.TABLE + " ("                    + GameColumns._ID + " INTEGER PRIMARY KEY,"                    + GameColumns.IMEI + " TEXT,"                    + GameColumns.LAST_UPDATE + " TEXT,"                    + GameColumns.NICKNAME + " TEXT);");            ContentValues cv = new ContentValues();            cv.put(GameColumns.IMEI, "123456789012345");            cv.put(GameColumns.LAST_UPDATE, 0);            cv.put(GameColumns.NICKNAME, (String) null);            db.insert(GameColumns.TABLE, null, cv);        }    }}

phoxicle's solution is a great starting point, but per Kevin Galligan's notes on Android's SQLite serialization, this implementation isn't thread safe and will fail silently when multiple database connections (e.g. from different threads) try to write the database:

If you try to write to the database from actual distinct connections at the same time, one will fail. It will not wait till the first is done and then write. It will simply not write your change. Worse, if you don’t call the right version of insert/update on the SQLiteDatabase, you won’t get an exception. You’ll just get a message in your LogCat, and that will be it.

So, multiple threads? Use one helper.

Here's a modified implementation of phoxicle's database adapter that uses a static SQLiteOpenHelper instance and is thus limited to a single database connection:

public class DBBaseAdapter {    private static final String TAG = "DBBaseAdapter";    protected static final String DATABASE_NAME = "db.sqlite";    protected static final int DATABASE_VERSION = 1;    protected Context mContext;    protected static DatabaseHelper mDbHelper;    private static final String TABLE_CREATE_FOO =         "create table foo (_id integer primary key autoincrement, " +        "bar text not null)");    public DBBaseAdapter(Context context) {        mContext = context.getApplicationContext();    }    public SQLiteDatabase openDb() {        if (mDbHelper == null) {            mDbHelper = new DatabaseHelper(mContext);        }        return mDbHelper.getWritableDatabase();    }    public void closeDb() {        mDbHelper.close();    }    protected static class DatabaseHelper extends SQLiteOpenHelper {        public DatabaseHelper(Context context) {            super(context, DATABASE_NAME, null, DATABASE_VERSION);        }        @Override        public void onCreate(SQLiteDatabase db) {            db.execSQL(TABLE_CREATE_FOO);        }        @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);        }    }}

Extend DBBaseAdapter for each table to implement your CRUD methods:

public class DBFooTable extends DBBaseAdapter {    public DBFooTable(Context context) {        super(context);    }    public void getBar() {        SQLiteDatabase db = openDb();        // ...        closeDb();}