Best practices for working with multiple tables

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?

android database android-sqlite

Edit Report

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


                        
                        
                            answered 2013-03-23T04:08:06.700

Best practices for working with multiple tables

1 Answer

Your Answer