Android: can I use one SQLiteOpenHelper class for multiple database files?

Question

My app uses two databases (separate files). To handle these databases I have created two Helper classes which extend SQLiteOpenHelper, one for each database.

I am now going to add a third database and wonder whether I need to create yet another Helper class (and if I used a 4th and a 5th database would I need even more Helper classes), or can I use the same Helper class for multiple databases?

The problem that I see with trying to use just one Helper class is that I can't see how to pass the name of the individual database files to the Helper. At present the name of the database is hard-coded as a Static field of each of the Helper classes, but if I had only one Helper class I would need to be able to pass the different names in to the Constructor when creating the separate Helper objects; the problem is that the SQLiteOpenHelper Constructor seems to be called by Android with just one parameter: the Context.

Answer 1

Use a single Database Access Object attached to the ApplicationContext to handle the SqliteAssetHelper implementation, use getInstance() rather than using the DAO constructor for each connection, make sure you open and close the connection once in your apps life cycle and you can query the database as much as you like during the apps life cycle, also it avoids memory leaks.

    /**
         * Private constructor to avoid object creation from outside classes.
         *
         */
        private Databa

seAccess() {

        this.masterOpenHelper = DatabaseOpenHelper.getInstance( "master.db");
        this.dictionaryOpenHelper = DatabaseOpenHelper.getInstance( "dictionary.db");
        isAlreadyOpen = false;
    }

    /**
     * Return a singleton instance of DatabaseAccess.
     *
     * @return the instance of DabaseAccess
     */
    public static DatabaseAccess getInstance() {


        if (instance == null) {
            instance = new DatabaseAccess();
            instance.open();
        }

        return instance;
    }

/**
 * Open the database connection.
 */
public void open() {

    this.masterDatabase = masterOpenHelper.getWritableDatabase();
    Log.i(TAG, "master db is opened " + masterDatabase.isOpen());

    this.dictionaryDatabase = dictionaryOpenHelper.getWritableDatabase();
    Log.i(TAG, "dictionary db is opened " + dictionaryDatabase.isOpen());

    isAlreadyOpen = true;
}

/**
 * Close the database connection.
 */
public void close() {

    if (masterDatabase != null) {
        this.masterDatabase.close();
        Log.i(TAG, "masterdb closed " + !this.masterDatabase.isOpen());
    }

    if(dictionaryDatabase != null) {
        this.dictionaryDatabase.close();
        Log.i(TAG, "dictionary closed " + !this.dictionaryDatabase.isOpen());
    }

    instance = null;
}

SqliteAssetHelper object

public class DatabaseOpenHelper extends SQLiteAssetHelper {
    private static final String TAG = "DatabaseOpenHelper";
    public static String DATABASE_NAME;
    private static final int DATABASE_VERSION = 1;
    private static DatabaseOpenHelper masterDb, dictionaryDb;

    private DatabaseOpenHelper(String name) {
        super(MyApp.mContext.get(), name, null, DATABASE_VERSION); // mContext is a WeakReference object
        DATABASE_NAME = name; 
    }

    public static synchronized DatabaseOpenHelper getInstance(String name) {

        switch (name){

            case "master.db":

                if(masterDb == null)
                    masterDb = new DatabaseOpenHelper(name);

                return  masterDb;

            case "dictionary.db":

                if(dictionaryDb == null)
                    dictionaryDb = new DatabaseOpenHelper(name);

                return dictionaryDb;

            default:
                Log.i(TAG, "database not found " + name);
                return null;
        }
    }

Answer 2

Of course, you can. It is just a matter of your Helper class design. You can just pass the name of DB to your Helper class constructor (along with required Context instance) instead of hardcoding:

public class DBOpenHelper extends SQLiteOpenHelper {

    public DBOpenHelper(Context context, String dbName, int dbVersion) {
        super(context, dbName, null, dbVersion);
    }
...
}

Answer 3

You need an abstract class that implements the upgrade process described here. Then you extend this abstract class for each of your tables. In your abstract class you must store you tables in a way(list, hardcoded) so when the onUpgrade fires you iterate over the table items and for each table item you do the described steps. They will be self upgraded, keeping all their existing details. Please note that the onUpgrade event fires only once per database, that's why you need to iterate over all your tables to do the upgrade of all of them. You maintain only 1 version number over all the database.

• beginTransaction
• run a table creation with if not exists (we are doing an upgrade, so the table might not exists yet, it will fail alter and drop)
• put in a list the existing columns List<String> columns = DBUtils.GetColumns(db, TableName);
• backup table (ALTER table " + TableName + " RENAME TO 'temp_" + TableName)
• create new table (the newest table creation schema)
• get the intersection with the new columns, this time columns taken from the upgraded table (columns.retainAll(DBUtils.GetColumns(db, TableName));)
• restore data (String cols = StringUtils.join(columns, ","); db.execSQL(String.format( "INSERT INTO %s (%s) SELECT %s from temp_%s", TableName, cols, cols, TableName)); )
• remove backup table (DROP table 'temp_" + TableName)
• setTransactionSuccessful

(This doesn't handle table downgrade, if you rename a column, you don't get the existing data transfered as the column names do not match).

.

public static List<String> GetColumns(SQLiteDatabase db, String tableName) {
    List<String> ar = null;
    Cursor c = null;
    try {
        c = db.rawQuery("select * from " + tableName + " limit 1", null);
        if (c != null) {
            ar = new ArrayList<String>(Arrays.asList(c.getColumnNames()));
        }
    } catch (Exception e) {
        Log.v(tableName, e.getMessage(), e);
        e.printStackTrace();
    } finally {
        if (c != null)
            c.close();
    }
    return ar;
}

public static String join(List<String> list, String delim) {
    StringBuilder buf = new StringBuilder();
    int num = list.size();
    for (int i = 0; i < num; i++) {
        if (i != 0)
            buf.append(delim);
        buf.append((String) list.get(i));
    }
    return buf.toString();
}