Skip to content Skip to sidebar Skip to footer
Home / Android / Android Database / Android Sqlite / Sqlite

Should I Create A Class That Inherits Sqliteopenhelper For Each Table In My Database?

Post a Comment
I need to create an android app database , but I was wondering if I should create one class that inherits SQLiteOpenHelper for each table that exists in my database ? Or should I c

Solution 1:

You only need one database helper for all tables in a database and thus you'd use the one onCreate method for the creation of the tables. Noting that if you had multiple database helpers that the onCreate (and onUpdate method) would only be called once by the first helper that opened the database and thus in addition to having multiple helpers being inefficient it could be more complicated to have multiple database helpers.

More specifically onCreate is only automatically called when the database doesn't exist. By the time onCreate is called the database itself has been created.

onUpdate is called only when upon opening the database, the version number passed to the call (via the super call) is greater than the version number stored in the database file. The version number stored in the file, at this time, is then updated to reflect the newer version. Therefore subsequent calls will not then invoke the onUpgrade method.

Whether or not you split the methods and identifiers, such as the column/table names for individual tables is a choice you could make. Some may consider it awkward to split others may think it is clearer.

Example

The following code is an example of 3 permutations (and also databases) that all utilises 2 tables, namely table001 (columns _id and mydata) and table001 (column names _id and myotherdata).

  1. uses a single databaseHelper (DBHelper001) with everything embedded within the helper. The database is mydb001

  2. uses a single databasehelper (DBHelper002) with the Table specific methods and constants in specific table orientated classes (class Table001 and class Table002).

  3. uses two separate databaseshelpers (DBHelperTable001 and DBHelperTable002) and for simplicity of code utilise the Table001 and Table002 classes.

    • Note that to overcome onCreate being called only once the onOpen method also attempts to create the respective table (CREATE TABLE IF NOT EXISTS ...... being important in this case to avert a failure when the table does in fact exist).
    • Note this is just one inefficiency of having multiple helpers.

First the table specific classes (not used by the first permutation)

Table001.java

publicclassTable001 {

    publicstaticfinalStringTBL_TABLE001="table001";
    publicstaticfinalStringCOL_TABLE001_ID= BaseColumns._ID;
    publicstaticfinalStringCOL_TABLE001_MYDATA="mydata";

    publicstatic String getCrtSQL() {
        return"CREATE TABLE IF NOT EXISTS " + TBL_TABLE001 + "(" +
                COL_TABLE001_ID + " INTEGER PRIMARY KEY, " +
                COL_TABLE001_MYDATA + " TEXT" +
                ")";
    }

    publicstaticlonginsert(SQLiteDatabase db, String mydata) {
        ContentValuescv=newContentValues();
        cv.put(COL_TABLE001_MYDATA,mydata);
        return db.insert(TBL_TABLE001,null,cv);
    }

    publicstatic Cursor getAll(SQLiteDatabase db) {
        return db.query(TBL_TABLE001,null,null,null,null,null,null);
    }
}

Table002.java

publicclassTable002 {

    publicstaticfinalStringTBL_TABLE002="table002";
    publicstaticfinalStringCOL_TABLE002_ID= BaseColumns._ID;
    publicstaticfinalStringCOL_TABLE002_MYOTHERDATA="myotherdata";

    publicstatic String getCrtSQL() {
        return"CREATE TABLE IF NOT EXISTS " + TBL_TABLE002 + "(" +
                COL_TABLE002_ID + " INTEGER PRIMARY KEY, " +
                COL_TABLE002_MYOTHERDATA + " TEXT" +
                ")";
    }

    publicstaticlonginsert(SQLiteDatabase db, String mydata) {
        ContentValuescv=newContentValues();
        cv.put(COL_TABLE002_MYOTHERDATA,mydata);
        return db.insert(TBL_TABLE002,null,cv);
    }

    publicstatic Cursor getAll(SQLiteDatabase db) {
        return db.query(TBL_TABLE002,null,null,null,null,null,null);
    }
}

The four database helper classes

DBHelper001.java - (self-contained)

publicclassDBHelper001extendsSQLiteOpenHelper {

    publicstaticfinalStringDBNAME="mydb001";
    publicstaticfinalintDBVERSION=1;

    publicstaticfinalStringTBL_TABLE001="table001";
    publicstaticfinalStringTBL_TABLE002="table002";
    publicstaticfinalStringCOL_TABLE001_ID= BaseColumns._ID;
    publicstaticfinalStringCOL_TABLE001_MYDATA="mydata";
    publicstaticfinalStringCOL_TABLE002_ID= BaseColumns._ID;
    publicstaticfinalStringCOL_TABLE002_MYOTHERDATA="myotherdata";

    publicDBHelper001(Context context) {
        super(context, DBNAME, null, DBVERSION);
    }

    @OverridepublicvoidonCreate(SQLiteDatabase db) {
        Stringcrt_table001_sql="CREATE TABLE IF NOT EXISTS " + TBL_TABLE001 + "(" +
                COL_TABLE001_ID + " INTEGER PRIMARY KEY," +
                COL_TABLE001_MYDATA + " TEXT" +
                ")";
        Stringcrt_table002_sql="CREATE TABLE IF NOT EXISTS " + TBL_TABLE002 + "(" +
                COL_TABLE002_ID + " INTEGER PRIMARY KEY," +
                COL_TABLE002_MYOTHERDATA + " TEXT" +
                ")";
        db.execSQL(crt_table001_sql);
        db.execSQL(crt_table002_sql);
    }

    @OverridepublicvoidonUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    publiclonginsertIntoTable001(String mydata) {
        SQLiteDatabasedb=this.getWritableDatabase();
        ContentValuescv=newContentValues();
        cv.put(COL_TABLE001_MYDATA,mydata);
        return db.insert(TBL_TABLE001,null,cv);
    }

    publiclonginsertIntoTable002(String myotherdata) {
        SQLiteDatabasedb=this.getWritableDatabase();
        ContentValuescv=newContentValues();
        cv.put(COL_TABLE002_MYOTHERDATA,myotherdata);
        return db.insert(TBL_TABLE002,null,cv);
    }

    public Cursor getAllFromTable001() {
        SQLiteDatabasedb=this.getWritableDatabase();
        return db.query(TBL_TABLE001,null,null,null,null,null,null);
    }

    public Cursor getAllFromTable002() {
        SQLiteDatabasedb=this.getWritableDatabase();
        return db.query(TBL_TABLE002,null,null,null,null,null,null);
    }
}

DBHelper002.java (table specific code elsewhere)

publicclassDBHelper002extendsSQLiteOpenHelper {

    publicstaticfinalStringDBNAME="mydb002";
    publicstaticfinalintDBVERSION=1;

    publicDBHelper002(Context context) {
        super(context, DBNAME, null, DBVERSION);
    }

    @OverridepublicvoidonCreate(SQLiteDatabase db) {
        db.execSQL(Table001.getCrtSQL());
        db.execSQL(Table002.getCrtSQL());
    }

    @OverridepublicvoidonUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }
}

DBHelperTable001.java (table001 specific helper)

publicclassDBHelperTable001extendsSQLiteOpenHelper {

    publicstaticfinalStringDBNAME="mydb003";
    publicstaticfinalintDBVERSION=1;

    publicDBHelperTable001(Context context) {
        super(context, DBNAME, null, DBVERSION);
    }

    @OverridepublicvoidonCreate(SQLiteDatabase db) {
        db.execSQL(Table001.getCrtSQL());
        //NOTE Table002 won't get created as onCreate is only called once
    }

    @OverridepublicvoidonUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    @OverridepublicvoidonOpen(SQLiteDatabase db) {
        super.onOpen(db);
        db.execSQL(Table001.getCrtSQL());
    }
}
  • Note the onOpen method is utilised to circumvent onCreate only being invoked once for the lifetime of the database.
    • invoking the execSQL is an example of an inefficiency of this methodology.

DBHelperTable002.java (table002 specific helper)

publicclassDBHelperTable002extendsSQLiteOpenHelper {

    publicstaticfinalStringDBNAME="mydb003";
    publicstaticfinalintDBVERSION=1;

    publicDBHelperTable002(Context context) {
        super(context, DBNAME, null, DBVERSION);
    }

    @OverridepublicvoidonCreate(SQLiteDatabase db) {
        db.execSQL(Table002.getCrtSQL());
        //NOTE Table001 won't get created as onCreate is only called once
    }

    @OverridepublicvoidonUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

    }

    @OverridepublicvoidonOpen(SQLiteDatabase db) {
        super.onOpen(db);
        db.execSQL(Table002.getCrtSQL());
    }
}

Tying them all together

The following activity utilises (MainActivity.java) utilises all 3 permutations. For each a row is added to each table and then all data from each table is extracted into a cursor which is then dumped (output to the log).

Note that for the table specific helpers each helper is used to extract rows (showing the redundancy aspect).

MainActivity.java

publicclassMainActivityextendsAppCompatActivity {

    DBHelper001 mDBHlpr1;
    DBHelper002 mDBHlpr2;
    DBHelperTable001 mTblDBHlpr1;
    DBHelperTable002 mTblDBHlpr2;
    Cursor mCsr;

    @OverrideprotectedvoidonCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        mDBHlpr1 = newDBHelper001(this);
        mDBHlpr1.insertIntoTable001("my data for table001 in mydb001");
        mDBHlpr1.insertIntoTable002("my other data for table002 in mydb001");
        mCsr = mDBHlpr1.getAllFromTable001();
        DatabaseUtils.dumpCursor(mCsr);
        mCsr = mDBHlpr1.getAllFromTable002();
        DatabaseUtils.dumpCursor(mCsr);


        mDBHlpr2 = newDBHelper002(this);
        Table001.insert(mDBHlpr2.getWritableDatabase(),"my data for table001 in mydb002");
        Table002.insert(mDBHlpr2.getWritableDatabase(),"my other data for table002 in mydb002");
        mCsr = Table001.getAll(mDBHlpr2.getWritableDatabase());
        DatabaseUtils.dumpCursor(mCsr);
        mCsr = Table002.getAll(mDBHlpr2.getWritableDatabase());
        DatabaseUtils.dumpCursor(mCsr);

        //Oooops???? wouldn't normally do this
        mCsr = Table001.getAll(mDBHlpr1.getWritableDatabase()); //?????????? from other database!!!
        DatabaseUtils.dumpCursor(mCsr);

        mTblDBHlpr1 = newDBHelperTable001(this);
        Table001.insert(mTblDBHlpr1.getWritableDatabase(),"my data for table001 in mydb003");
        mTblDBHlpr2 = newDBHelperTable002(this);
        Table002.insert(mTblDBHlpr2.getWritableDatabase(),"my data for table002 in mydb003");
        mCsr = Table001.getAll(mTblDBHlpr1.getWritableDatabase());
        DatabaseUtils.dumpCursor(mCsr);
        mCsr = Table002.getAll(mTblDBHlpr1.getWritableDatabase()); //???????????? but OK
        DatabaseUtils.dumpCursor(mCsr);
        mCsr = Table001.getAll(mTblDBHlpr2.getWritableDatabase());
        DatabaseUtils.dumpCursor(mCsr);
        mCsr = Table002.getAll(mTblDBHlpr2.getWritableDatabase()); //??????????? but OK
        DatabaseUtils.dumpCursor(mCsr);
    }
}

Result

The following is the result from the first run (note running multiple times without uninstalling the App will result in 2 new rows being added) :-

03-0611:27:18.45311093-11093/?I/System.out:>>>>>Dumpingcursorandroid.database.sqlite.SQLiteCursor@12c4306e03-0611:27:18.45311093-11093/?I/System.out:0 {
03-0611:27:18.45311093-11093/?I/System.out:_id=103-0611:27:18.45311093-11093/?I/System.out:mydata=mydatafortable001inmydb00103-0611:27:18.45311093-11093/?I/System.out: }
03-0611:27:18.45311093-11093/?I/System.out:<<<<<03-0611:27:18.45311093-11093/?I/System.out:>>>>>Dumpingcursorandroid.database.sqlite.SQLiteCursor@275e530f03-0611:27:18.45311093-11093/?I/System.out:0 {
03-0611:27:18.45311093-11093/?I/System.out:_id=103-0611:27:18.45311093-11093/?I/System.out:myotherdata=myotherdatafortable002inmydb00103-0611:27:18.45311093-11093/?I/System.out: }
03-0611:27:18.45311093-11093/?I/System.out:<<<<<03-0611:27:18.47211093-11093/?I/System.out:>>>>>Dumpingcursorandroid.database.sqlite.SQLiteCursor@1006e57a03-0611:27:18.47211093-11093/?I/System.out:0 {
03-0611:27:18.47211093-11093/?I/System.out:_id=103-0611:27:18.47211093-11093/?I/System.out:mydata=mydatafortable001inmydb00203-0611:27:18.47211093-11093/?I/System.out: }
03-0611:27:18.47211093-11093/?I/System.out:<<<<<03-0611:27:18.47211093-11093/?I/System.out:>>>>>Dumpingcursorandroid.database.sqlite.SQLiteCursor@2337112b03-0611:27:18.47311093-11093/?I/System.out:0 {
03-0611:27:18.47311093-11093/?I/System.out:_id=103-0611:27:18.47311093-11093/?I/System.out:myotherdata=myotherdatafortable002inmydb00203-0611:27:18.47311093-11093/?I/System.out: }
03-0611:27:18.47311093-11093/?I/System.out:<<<<<03-0611:27:18.47311093-11093/?I/System.out:>>>>>Dumpingcursorandroid.database.sqlite.SQLiteCursor@37ef998803-0611:27:18.47311093-11093/?I/System.out:0 {
03-0611:27:18.47311093-11093/?I/System.out:_id=103-0611:27:18.47311093-11093/?I/System.out:mydata=mydatafortable001inmydb00103-0611:27:18.47311093-11093/?I/System.out: }
03-0611:27:18.47311093-11093/?I/System.out:<<<<<03-0611:27:18.49911093-11093/?I/System.out:>>>>>Dumpingcursorandroid.database.sqlite.SQLiteCursor@2b786c5d03-0611:27:18.50011093-11093/?I/System.out:0 {
03-0611:27:18.50011093-11093/?I/System.out:_id=103-0611:27:18.50011093-11093/?I/System.out:mydata=mydatafortable001inmydb00303-0611:27:18.50011093-11093/?I/System.out: }
03-0611:27:18.50011093-11093/?I/System.out:<<<<<03-0611:27:18.50011093-11093/?I/System.out:>>>>>Dumpingcursorandroid.database.sqlite.SQLiteCursor@2038a9d203-0611:27:18.50011093-11093/?I/System.out:0 {
03-0611:27:18.50111093-11093/?I/System.out:_id=103-0611:27:18.50111093-11093/?I/System.out:myotherdata=mydatafortable002inmydb00303-0611:27:18.50111093-11093/?I/System.out: }
03-0611:27:18.50111093-11093/?I/System.out:<<<<<03-0611:27:18.50111093-11093/?I/System.out:>>>>>Dumpingcursorandroid.database.sqlite.SQLiteCursor@264a7aa303-0611:27:18.50111093-11093/?I/System.out:0 {
03-0611:27:18.50111093-11093/?I/System.out:_id=103-0611:27:18.50111093-11093/?I/System.out:mydata=mydatafortable001inmydb00303-0611:27:18.50111093-11093/?I/System.out: }
03-0611:27:18.50211093-11093/?I/System.out:<<<<<03-0611:27:18.50211093-11093/?I/System.out:>>>>>Dumpingcursorandroid.database.sqlite.SQLiteCursor@3c2611a003-0611:27:18.50211093-11093/?I/System.out:0 {
03-0611:27:18.50211093-11093/?I/System.out:_id=103-0611:27:18.50211093-11093/?I/System.out:myotherdata=mydatafortable002inmydb00303-0611:27:18.50311093-11093/?I/System.out: }
03-0611:27:18.50311093-11093/?I/System.out:<<<<<

You may like these posts

Post a Comment for "Should I Create A Class That Inherits Sqliteopenhelper For Each Table In My Database?"