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.

Dressel

Hey man! I am doing something very similar to u, but in my case i need 2 different dbHelper classes. Thing is i am getting an error while trying to do so. java.lang.IllegalStateException: Helper class was class X but is trying to be reset to class Y. did u ever face this issue? – Amberambergris

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

Ocko

Silly me! I wasn't reading my Helper object code creation code correctly. As you say, it's very simple. – Dressel

Hi, I would like to ask a question (hope its not too simple/obvious) which is how do you iterate through the helper class to create all the databases ? Did you loop through the helper class using a for loop for the number of databases you want to create and pass in all the database names ? – Diverticulitis

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

Alongside

I'm afraid this is too complicated for me! Perhaps I should stick with multiple Helper classes for multiple databases. – Dressel

You have all the complicated code fragments there. You just have to wrap in a for cycle and wrap the transaction stuff around. There are more complicated things than this. – Alongside

Answer 3

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

Fulgurous