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?

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

Solution 2

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

Solution 3

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

Solution 4

I'm a bit late maybe but i always open my database, not my table. So this form me as no sense.

    firstTable.open();
    secondTable.open(); 

Rather do this.

    dataBase.getWritableDatabase();

then if you want to update juste chose the table:

public int updateTotal (int id, Jours jour){
    ContentValues values = new ContentValues();

    values.put(COL_TOTAL,Total );

    //update the table you want
    return bdd.update(TABLE_NAME, values, COL_JOUR + " = " + id, null);
}

And that's all. Hope it can help other people

View more solutions

Comments

[Burjua]

Ok, thanks for sharing, I had considered to use one adapter but after that I thought that it can be quite messy. Just to make sure I understand your approach correctly, Do you hold all your db methods in one this class? and for example in your MyDatabaseManager you dropDatabase similar to this: private void dropDatabase(SQLiteDatabase db) { db.execSQL("DROP TABLE IF EXISTS " + GameColumns.TABLE); db.execSQL("DROP TABLE IF EXISTS " + OtherColumns.TABLE); ... } ?

[Burjua]

I don't want to say that I don't like it or something, just trying to understand.

[WarrenFaith]

Thats right. My Adapter is currently more than 1000 lines long, but most of that is because I fill some tables with dynamic content if the database is empty. To prevent the "messy" feeling, you should have a specific naming rule: e.g. findTABLENAMEById(int id) You also could try to use more generic methods: e.g. findById(String tableName, int id) that can shorten your code and the number of methods you use

[groo]

I will give this approach a try. Also can't support such huge confusing classes. I am going to an approach where I will have a Sigle MetaDataClass with all static structures in it and than also simplifying even a bit more the inner classes from your post.

[CatShoes]

I'm not known for making clean code, but I certainly like the way your adapter is structured. Thank you very much!

[Benoit Duffez]

It turns out that you can absolutely create tables in onUpgrade. Just to save everybody's time, I just created an empty test app that creates one table and fills it. Then in another run, increase the DB version, create another table and fill it. In the end, both db adapters derived from AbstractDbAdapter can be accessed at the same time and the two tables were not created by the same APK version. SO +1, great implementation.

[tony gil]

t'as pas publiqué ton helper class pour qu'on puisse comprendre exactement ce que tu proposes.