Best practices of working with multiple sqlite DB tables in Android

Question

Hi guys, in my android app I use a DB with multiple tables, I have an XML parser which needs to write data to 2 tables while parsing. I created 2 DBadapters for 2 tables, but now I have a problem.

When working with one table it's easy, I can do:

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

Or as it a SAX parser, in my opinion (may be I'm wrong) even better will be:

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 can I do it if I need to insert data to second table? e.g. if I have second adapter, I think that this will be wrong:

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

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

Any thoughts will be very appresiated

thanks

Answer 1

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

Answer 2

Ok, I will stick to using one DB handler for all tables, it's not so bad at the end of the day))

Answer 3

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