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

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 2

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