Should I Create A Class That Inherits SQLiteOpenHelper For Each Table In My Database?
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).
uses a single databaseHelper (DBHelper001) with everything embedded within the helper. The database is mydb001
uses a single databasehelper (DBHelper002) with the Table specific methods and constants in specific table orientated classes (class Table001 and class Table002).
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.
- Note that to overcome onCreate being called only once the onOpen method also attempts to create the respective table (
First the table specific classes (not used by the first permutation)
Table001.java
public class Table001 {
public static final String TBL_TABLE001 = "table001";
public static final String COL_TABLE001_ID = BaseColumns._ID;
public static final String COL_TABLE001_MYDATA = "mydata";
public static String getCrtSQL() {
return "CREATE TABLE IF NOT EXISTS " + TBL_TABLE001 + "(" +
COL_TABLE001_ID + " INTEGER PRIMARY KEY, " +
COL_TABLE001_MYDATA + " TEXT" +
")";
}
public static long insert(SQLiteDatabase db, String mydata) {
ContentValues cv = new ContentValues();
cv.put(COL_TABLE001_MYDATA,mydata);
return db.insert(TBL_TABLE001,null,cv);
}
public static Cursor getAll(SQLiteDatabase db) {
return db.query(TBL_TABLE001,null,null,null,null,null,null);
}
}
Table002.java
public class Table002 {
public static final String TBL_TABLE002 = "table002";
public static final String COL_TABLE002_ID = BaseColumns._ID;
public static final String COL_TABLE002_MYOTHERDATA = "myotherdata";
public static String getCrtSQL() {
return "CREATE TABLE IF NOT EXISTS " + TBL_TABLE002 + "(" +
COL_TABLE002_ID + " INTEGER PRIMARY KEY, " +
COL_TABLE002_MYOTHERDATA + " TEXT" +
")";
}
public static long insert(SQLiteDatabase db, String mydata) {
ContentValues cv = new ContentValues();
cv.put(COL_TABLE002_MYOTHERDATA,mydata);
return db.insert(TBL_TABLE002,null,cv);
}
public static Cursor getAll(SQLiteDatabase db) {
return db.query(TBL_TABLE002,null,null,null,null,null,null);
}
}
The four database helper classes
DBHelper001.java - (self-contained)
public class DBHelper001 extends SQLiteOpenHelper {
public static final String DBNAME = "mydb001";
public static final int DBVERSION = 1;
public static final String TBL_TABLE001 = "table001";
public static final String TBL_TABLE002 = "table002";
public static final String COL_TABLE001_ID = BaseColumns._ID;
public static final String COL_TABLE001_MYDATA = "mydata";
public static final String COL_TABLE002_ID = BaseColumns._ID;
public static final String COL_TABLE002_MYOTHERDATA = "myotherdata";
public DBHelper001(Context context) {
super(context, DBNAME, null, DBVERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
String crt_table001_sql = "CREATE TABLE IF NOT EXISTS " + TBL_TABLE001 + "(" +
COL_TABLE001_ID + " INTEGER PRIMARY KEY," +
COL_TABLE001_MYDATA + " TEXT" +
")";
String crt_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);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
public long insertIntoTable001(String mydata) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put(COL_TABLE001_MYDATA,mydata);
return db.insert(TBL_TABLE001,null,cv);
}
public long insertIntoTable002(String myotherdata) {
SQLiteDatabase db = this.getWritableDatabase();
ContentValues cv = new ContentValues();
cv.put(COL_TABLE002_MYOTHERDATA,myotherdata);
return db.insert(TBL_TABLE002,null,cv);
}
public Cursor getAllFromTable001() {
SQLiteDatabase db = this.getWritableDatabase();
return db.query(TBL_TABLE001,null,null,null,null,null,null);
}
public Cursor getAllFromTable002() {
SQLiteDatabase db = this.getWritableDatabase();
return db.query(TBL_TABLE002,null,null,null,null,null,null);
}
}
DBHelper002.java (table specific code elsewhere)
public class DBHelper002 extends SQLiteOpenHelper {
public static final String DBNAME = "mydb002";
public static final int DBVERSION = 1;
public DBHelper002(Context context) {
super(context, DBNAME, null, DBVERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(Table001.getCrtSQL());
db.execSQL(Table002.getCrtSQL());
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
}
DBHelperTable001.java (table001 specific helper)
public class DBHelperTable001 extends SQLiteOpenHelper {
public static final String DBNAME = "mydb003";
public static final int DBVERSION = 1;
public DBHelperTable001(Context context) {
super(context, DBNAME, null, DBVERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(Table001.getCrtSQL());
//NOTE Table002 won't get created as onCreate is only called once
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
@Override
public void onOpen(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)
public class DBHelperTable002 extends SQLiteOpenHelper {
public static final String DBNAME = "mydb003";
public static final int DBVERSION = 1;
public DBHelperTable002(Context context) {
super(context, DBNAME, null, DBVERSION);
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(Table002.getCrtSQL());
//NOTE Table001 won't get created as onCreate is only called once
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
@Override
public void onOpen(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
public class MainActivity extends AppCompatActivity {
DBHelper001 mDBHlpr1;
DBHelper002 mDBHlpr2;
DBHelperTable001 mTblDBHlpr1;
DBHelperTable002 mTblDBHlpr2;
Cursor mCsr;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
mDBHlpr1 = new DBHelper001(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 = new DBHelper002(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 = new DBHelperTable001(this);
Table001.insert(mTblDBHlpr1.getWritableDatabase(),"my data for table001 in mydb003");
mTblDBHlpr2 = new DBHelperTable002(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-06 11:27:18.453 11093-11093/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@12c4306e
03-06 11:27:18.453 11093-11093/? I/System.out: 0 {
03-06 11:27:18.453 11093-11093/? I/System.out: _id=1
03-06 11:27:18.453 11093-11093/? I/System.out: mydata=my data for table001 in mydb001
03-06 11:27:18.453 11093-11093/? I/System.out: }
03-06 11:27:18.453 11093-11093/? I/System.out: <<<<<
03-06 11:27:18.453 11093-11093/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@275e530f
03-06 11:27:18.453 11093-11093/? I/System.out: 0 {
03-06 11:27:18.453 11093-11093/? I/System.out: _id=1
03-06 11:27:18.453 11093-11093/? I/System.out: myotherdata=my other data for table002 in mydb001
03-06 11:27:18.453 11093-11093/? I/System.out: }
03-06 11:27:18.453 11093-11093/? I/System.out: <<<<<
03-06 11:27:18.472 11093-11093/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@1006e57a
03-06 11:27:18.472 11093-11093/? I/System.out: 0 {
03-06 11:27:18.472 11093-11093/? I/System.out: _id=1
03-06 11:27:18.472 11093-11093/? I/System.out: mydata=my data for table001 in mydb002
03-06 11:27:18.472 11093-11093/? I/System.out: }
03-06 11:27:18.472 11093-11093/? I/System.out: <<<<<
03-06 11:27:18.472 11093-11093/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@2337112b
03-06 11:27:18.473 11093-11093/? I/System.out: 0 {
03-06 11:27:18.473 11093-11093/? I/System.out: _id=1
03-06 11:27:18.473 11093-11093/? I/System.out: myotherdata=my other data for table002 in mydb002
03-06 11:27:18.473 11093-11093/? I/System.out: }
03-06 11:27:18.473 11093-11093/? I/System.out: <<<<<
03-06 11:27:18.473 11093-11093/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@37ef9988
03-06 11:27:18.473 11093-11093/? I/System.out: 0 {
03-06 11:27:18.473 11093-11093/? I/System.out: _id=1
03-06 11:27:18.473 11093-11093/? I/System.out: mydata=my data for table001 in mydb001
03-06 11:27:18.473 11093-11093/? I/System.out: }
03-06 11:27:18.473 11093-11093/? I/System.out: <<<<<
03-06 11:27:18.499 11093-11093/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@2b786c5d
03-06 11:27:18.500 11093-11093/? I/System.out: 0 {
03-06 11:27:18.500 11093-11093/? I/System.out: _id=1
03-06 11:27:18.500 11093-11093/? I/System.out: mydata=my data for table001 in mydb003
03-06 11:27:18.500 11093-11093/? I/System.out: }
03-06 11:27:18.500 11093-11093/? I/System.out: <<<<<
03-06 11:27:18.500 11093-11093/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@2038a9d2
03-06 11:27:18.500 11093-11093/? I/System.out: 0 {
03-06 11:27:18.501 11093-11093/? I/System.out: _id=1
03-06 11:27:18.501 11093-11093/? I/System.out: myotherdata=my data for table002 in mydb003
03-06 11:27:18.501 11093-11093/? I/System.out: }
03-06 11:27:18.501 11093-11093/? I/System.out: <<<<<
03-06 11:27:18.501 11093-11093/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@264a7aa3
03-06 11:27:18.501 11093-11093/? I/System.out: 0 {
03-06 11:27:18.501 11093-11093/? I/System.out: _id=1
03-06 11:27:18.501 11093-11093/? I/System.out: mydata=my data for table001 in mydb003
03-06 11:27:18.501 11093-11093/? I/System.out: }
03-06 11:27:18.502 11093-11093/? I/System.out: <<<<<
03-06 11:27:18.502 11093-11093/? I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@3c2611a0
03-06 11:27:18.502 11093-11093/? I/System.out: 0 {
03-06 11:27:18.502 11093-11093/? I/System.out: _id=1
03-06 11:27:18.502 11093-11093/? I/System.out: myotherdata=my data for table002 in mydb003
03-06 11:27:18.503 11093-11093/? I/System.out: }
03-06 11:27:18.503 11093-11093/? I/System.out: <<<<<
Post a Comment for "Should I Create A Class That Inherits SQLiteOpenHelper For Each Table In My Database?"