20

Is it better to have a single big SQLiteOpenHelper subclass that defines onCreate and onUpgrade methods for every table in the database, or is better to have many SQLiteOpenHelper subclasses, one for each table?

Is there a best practice? Or are both acceptable, but with different good and bad side effects?

Chris
  • 5,876
  • 3
  • 43
  • 69
Matteo Bononi 'peorthyr'
  • 2,170
  • 8
  • 46
  • 95

3 Answers3

24

You should have a single SQLiteOpenHelper class for all the tables. Check this link.

MysticMagicϡ
  • 28,593
  • 16
  • 73
  • 124
  • ok, thanks, do you know the sideeffect of having one for each table? Or is only to have a single file to edit? – Matteo Bononi 'peorthyr' Dec 14 '12 at 11:27
  • 4
    Thanks! I read an article here http://www.vogella.com/articles/AndroidSQLite/article.html#sqliteoverview_sqliteopenhelper which recommends to have a per table approach, which in my mind, absolutely doesn't make sense, and your link confirms this. – Devolus Jun 25 '13 at 18:14
  • I would really take into cosideration this comment talking about the explanation in that blog http://stackoverflow.com/questions/23743778/android-sqliteopenhelper-different-class-for-every-table#comment36502078_23743888 – I.G. Pascual Sep 29 '16 at 10:51
  • What is the cost if we maintain multiple databases for independent tables? I am assuming all independent tables can be moved to a different database – Uma Sankar Jul 04 '18 at 14:30
  • This is the best approach, why? Speaking of myself, I have noticed that when calling two tables concurrently either to update or get rows, I was having issues of missing data or not writing data to tables. Likely, I discovered this answer which solved the problem for me. – Odai A. Ali Jun 29 '22 at 20:36
5

Just for the sake of a different approach:

You can always overried on the onOpen(..) method have it called your onCreate(..) . Be sure to use the "CREATE TABLE IF NOT EXISTS..." statement rather than "CREATE TABLE"

    @Override
public void onOpen(SQLiteDatabase db) {
    onCreate(db);
}

@Override
public void onCreate(SQLiteDatabase db) {
    String CREATE_FRIENDS_TABLE = "CREATE TABLE IF NOT EXISTS ...";
    db.execSQL(CREATE_FRIENDS_TABLE);
}

You do that with every class that extends from SQLiteOpenHelper

Chayemor
  • 3,577
  • 4
  • 31
  • 54
2

@TheReader is right. I prefer a single SQLiteOpenHelper for all tables, here is what i do: pass a List of "table creation" sqls to the Constructor of the SQLiteOpenHelper subClass, then in the onCreate function iterate the list to create each table. so my SQLiteOpenHelper subclass looks sth like this:

public ModelReaderDbHelper(Context context, List<String> createSQLs, List<String> deleteSQLs){
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
        this.TABLE_CREATION_SQLS = createSQLs;
        this.TABLE_DELETE_SQLS = deleteSQLs;
    }
    @Override
    public void onCreate(SQLiteDatabase sqLiteDatabase) {
        for(String oneCreation : TABLE_CREATION_SQLS){
            sqLiteDatabase.execSQL(oneCreation);
        }
    }

But that comes another problem: after adding a new table, and install the new version of the app with an existing old one installed, the new table won't be created, because the existence of the old database will prevent the onCreate function from being called. So user has to uninstall the app first, and install the app completely. The DATABASE_VERSION helps, it seem android will not execute the onCreate function if and only if the a existin database with the same name and the same DATABASE_VERSION

telmo
  • 153
  • 8