DB adapter for multiple SQLite tables for Android?

Question

I read the Android SQLite Android platform tutorial which referred to creating a database adapter class to create and access a database table. When working with SQLite databases with multiple tables, is it best to create another adapter class for each table or create one DB adapter class for the entire Android application?

My application uses multiple tables, and I was hoping I shouldn't have one massive adapter class. the problem is that I have a nested subclass of SQLiteOpenHelper for the NotePad example in each adapter. When the first table opens, everything works fine. When I try to access the second area (due to another action), my application crashes.

At first I thought the crash was caused by a version check problem, but both adapters now have the same database version and still crash.

Here is an example of one of the DB adapters for the table. Other adapters correspond to the same format with different implementations.

public class InfoDBAdapter { public static final String ROW_ID = "_id"; public static final String NAME = "name"; private static final String TAG = "InfoDbAdapter"; private static final String DATABASE_NAME = "myappdb"; private static final String DATABASE_TABLE = "usersinfo"; private static final int DATABASE_VERSION = 1; private static final String DATABASE_CREATE = "create table usersinfo (_id integer primary key autoincrement, " + NAME + " TEXT," + ");"; private DatabaseHelper mDbHelper; private SQLiteDatabase mDb; private final Context mCtx; private static class DatabaseHelper extends SQLiteOpenHelper { DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(DATABASE_CREATE); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { Log.w(TAG, "Upgrading database from version " + oldVersion + " to " //$NON-NLS-1$//$NON-NLS-2$ + newVersion + ", which will destroy all old data"); //$NON-NLS-1$ //db.execSQL("DROP TABLE IF EXISTS usersinfo"); //$NON-NLS-1$ onCreate(db); } } public InfoDBAdapter(Context ctx) { this.mCtx = ctx; } public InfoDBAdapter open() throws SQLException { this.mDbHelper = new DatabaseHelper(this.mCtx); this.mDb = this.mDbHelper.getWritableDatabase(); return this; } /** * close return type: void */ public void close() { this.mDbHelper.close(); } public long createUser(String name) { ContentValues initialValues = new ContentValues(); initialValues.put(NAME, name); return this.mDb.insert(DATABASE_TABLE, null, initialValues); } public boolean deleteUser(long rowId) { return this.mDb.delete(DATABASE_TABLE, ROW_ID + "=" + rowId, null) > 0; //$NON-NLS-1$ } public Cursor fetchAllUsers() { return this.mDb.query(DATABASE_TABLE, new String[] { ROW_ID, NAME}, null, null, null, null, null); } public Cursor fetchUser(long rowId) throws SQLException { Cursor mCursor = this.mDb.query(true, DATABASE_TABLE, new String[] { ROW_ID, NAME}, ROW_ID + "=" + rowId, null, //$NON-NLS-1$ null, null, null, null); if (mCursor != null) { mCursor.moveToFirst(); } return mCursor; } public boolean updateUser(long rowId, String name) { ContentValues args = new ContentValues(); args.put(NAME, name); return this.mDb .update(DATABASE_TABLE, args, ROW_ID + "=" + rowId, null) > 0; //$NON-NLS-1$ } } 

When the first adapter, in this case userinfo, is available, everything works as expected. Let's say I have another adapter for information about a friend that follows the same structure as above, when another action accesses it, it seems to me that the nested subclass of SQLiteOpenHelper will try to create the database again. Obviously, something is wrong, because in this case my application crashes.

So, standard practice in Android to create one mammoth db adapter instead of separate adapters for a table?

Answer 1

Here is the solution that I eventually completed. This is a kind of mess from the information received in the Commonsware books, and some things on the Internet that I would like to bookmark, I want to pay tribute to:

For each type of data I need to extract from db, I create an adapter class (not subclassed by anything). These adapter classes contain all the methods needed to access db for this piece of information. For example, if I had three tables in my db:

• Cars
• Boats
• Motorcycles

I would have three adapters that look the same as the following (I just put one as a demo, but the idea is the same for everyone):

 public class CarsDBAdapter { public static final String ROW_ID = "_id"; public static final String NAME = "name"; public static final String MODEL = "model"; public static final String YEAR = "year"; private static final String DATABASE_TABLE = "cars"; private DatabaseHelper mDbHelper; private SQLiteDatabase mDb; private final Context mCtx; private static class DatabaseHelper extends SQLiteOpenHelper { DatabaseHelper(Context context) { super(context, DBAdapter.DATABASE_NAME, null, DBAdapter.DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { } } /** * Constructor - takes the context to allow the database to be * opened/created * * @param ctx * the Context within which to work */ public CarsDBAdapter(Context ctx) { this.mCtx = ctx; } /** * Open the cars database. If it cannot be opened, try to create a new * instance of the database. If it cannot be created, throw an exception to * signal the failure * * @return this (self reference, allowing this to be chained in an * initialization call) * @throws SQLException * if the database could be neither opened or created */ public CarsDBAdapter open() throws SQLException { this.mDbHelper = new DatabaseHelper(this.mCtx); this.mDb = this.mDbHelper.getWritableDatabase(); return this; } /** * close return type: void */ public void close() { this.mDbHelper.close(); } /** * Create a new car. If the car is successfully created return the new * rowId for that car, otherwise return a -1 to indicate failure. * * @param name * @param model * @param year * @return rowId or -1 if failed */ public long createCar(String name, String model, String year){ ContentValues initialValues = new ContentValues(); initialValues.put(NAME, name); initialValues.put(MODEL, model); initialValues.put(YEAR, year); return this.mDb.insert(DATABASE_TABLE, null, initialValues); } /** * Delete the car with the given rowId * * @param rowId * @return true if deleted, false otherwise */ public boolean deleteCar(long rowId) { return this.mDb.delete(DATABASE_TABLE, ROW_ID + "=" + rowId, null) > 0; //$NON-NLS-1$ } /** * Return a Cursor over the list of all cars in the database * * @return Cursor over all cars */ public Cursor getAllCars() { return this.mDb.query(DATABASE_TABLE, new String[] { ROW_ID, NAME, MODEL, YEAR }, null, null, null, null, null); } /** * Return a Cursor positioned at the car that matches the given rowId * @param rowId * @return Cursor positioned to matching car, if found * @throws SQLException if car could not be found/retrieved */ public Cursor getCar(long rowId) throws SQLException { Cursor mCursor = this.mDb.query(true, DATABASE_TABLE, new String[] { ROW_ID, NAME, MODEL, YEAR}, ROW_ID + "=" + rowId, null, null, null, null, null); if (mCursor != null) { mCursor.moveToFirst(); } return mCursor; } /** * Update the car. * * @param rowId * @param name * @param model * @param year * @return true if the note was successfully updated, false otherwise */ public boolean updateCar(long rowId, String name, String model, String year){ ContentValues args = new ContentValues(); args.put(NAME, name); args.put(MODEL, model); args.put(YEAR, year); return this.mDb.update(DATABASE_TABLE, args, ROW_ID + "=" + rowId, null) >0; } } 

So, if you imagine that I have one of these classes “adapters” for each table.

When the application splash screen starts, I use the presented Android For Beginners technique : creating multiple SQLite tables for Android

So, my main DBAdapter (which is responsible for creating all my tables in one db) looks like this:

 public class DBAdapter { public static final String DATABASE_NAME = "stuffIOwn"; //$NON-NLS-1$ public static final int DATABASE_VERSION = 1; private static final String CREATE_TABLE_CARS = "create table cars (_id integer primary key autoincrement, " //$NON-NLS-1$ + CarsDBAdapter.NAME+ " TEXT," //$NON-NLS-1$ + CarsDBAdapter.MODEL+ " TEXT," //$NON-NLS-1$ + CarsDBAdapter.YEAR+ " TEXT" + ");"; //$NON-NLS-1$ //$NON-NLS-2$ private static final String CREATE_TABLE_BOATS = "create table boats (_id integer primary key autoincrement, " //$NON-NLS-1$ +BoatsDBAdapter.NAME+" TEXT," //$NON-NLS-1$ +BoatsDBAdapter.MODEL+" TEXT," //$NON-NLS-1$ +BoatsDBAdapter.YEAR+" TEXT"+ ");"; //$NON-NLS-1$ //$NON-NLS-2$ private static final String CREATE_TABLE_CYCLES = "create table cycles (_id integer primary key autoincrement, " //$NON-NLS-1$ +CyclesDBAdapter.NAME+" TEXT," //$NON-NLS-1$ +CyclesDBAdapter.MODEL+" TEXT," //$NON-NLS-1$ +CyclesDBAdapter.YEAR+" TEXT"+ ");"; //$NON-NLS-1$ //$NON-NLS-2$ private final Context context; private DatabaseHelper DBHelper; private SQLiteDatabase db; /** * Constructor * @param ctx */ public DBAdapter(Context ctx) { this.context = ctx; this.DBHelper = new DatabaseHelper(this.context); } private static class DatabaseHelper extends SQLiteOpenHelper { DatabaseHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(CREATE_TABLE_CARS); db.execSQL(CREATE_TABLE_BOATS); db.execSQL(CREATE_TABLE_CYCLES); } @Override public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) { // Adding any table mods to this guy here } } /** * open the db * @return this * @throws SQLException * return type: DBAdapter */ public DBAdapter open() throws SQLException { this.db = this.DBHelper.getWritableDatabase(); return this; } /** * close the db * return type: void */ public void close() { this.DBHelper.close(); } } 

The DBAdapter class is called only at the first start of the application and its sole responsibility is to create / update tables. All other data access is through a separate “adapter”. I found that this works fine and does not create problems with the versions I mentioned earlier.

Hope this helps.

Answer 2

If I had the same problem, tried many solutions, finnaly I made an abstract method that builds the database structure and has an extended class for table classes.

This is my database constructor class and is abstract:

 public abstract class dbAdapter { public static String DATABASE_NAME = ""; public static final int DATABASE_VERSION = 1; public static final String DATABASE_TABLE1 = "ContactName"; public static final String DATABASE_TABLE2 = "PhoneNumber"; public static DbHelper ourHelper; public static Context ourContext; public static SQLiteDatabase ourDatabase; boolean ourConstructorBool = false; boolean ourDB = false; public static final String ContactNameTable = "CREATE TABLE "+DATABASE_TABLE1+" (" + ContactNameAdapter.KEY_ROWID+" INTEGER PRIMARY KEY AUTOINCREMENT, " + ContactNameAdapter.KEY_NAME+" TEXT, " + ContactNameAdapter.KEY_BIRTH_DATE+" TEXT);"; public static final String PhoneNumberTable = "CREATE TABLE "+DATABASE_TABLE2+" (" + PhoneNumberAdapter.KEY_NUMBER+" TEXT , " + PhoneNumberAdapter.KEY_DESCRIPTION+" TEXT, " + PhoneNumberAdapter.KEY_CONTACTID+" TEXT, " + "FOREIGN KEY(" + PhoneNumberAdapter.KEY_CONTACTID +") REFERENCES " + (ContactNameAdapter.DATABASE_TABLE)+"("+ContactNameAdapter.KEY_ROWID+") ON DELETE CASCADE"+ ");"; static class DbHelper extends SQLiteOpenHelper{ public DbHelper(Context context) { super(context, DATABASE_NAME, null, DATABASE_VERSION); } @Override public void onCreate(SQLiteDatabase db) { db.execSQL(ContactNameTable); db.execSQL(PhoneNumberTable); } @Override public void onUpgrade(SQLiteDatabase db, int arg1, int arg2) { db.execSQL("DROP TABLE IF EXISTS " + ContactNameAdapter.DATABASE_TABLE); db.execSQL("DROP TABLE IF EXISTS " + PhoneNumberAdapter.DATABASE_TABLE); onCreate(db); } } public dbAdapter(Activity a){ if(!ourConstructorBool == true){ ourContext = a; DATABASE_NAME = a.getString(Asaf.com.contactsEX.R.string.DB_NAME); ourConstructorBool = true; } } public dbAdapter open() throws SQLException{ if(!ourDB == true){ ourHelper = new DbHelper(ourContext); ourDB = true; } ourDatabase = ourHelper.getWritableDatabase(); return this; } public void close(){ if(ourDatabase.isOpen()) ourHelper.close(); } } 

And this is one of my table classes, the rest of the classes are implemented the same way, just add as many as you want:

 public class PhoneNumberAdapter extends dbAdapter{ public static final String KEY_NUMBER = "PhoneNumber"; public static final String KEY_DESCRIPTION = "Description"; public static final String KEY_CONTACTID = "ContactName_id"; public static final String DATABASE_TABLE = "PhoneNumber"; public PhoneNumberAdapter(Activity a){ super(a); } public long createEntry(String number, String description,long id){ // TODO Auto-generated method stub ContentValues cv = new ContentValues(); cv.put(KEY_NUMBER, number); cv.put(KEY_DESCRIPTION, description); cv.put(KEY_CONTACTID, id); return ourDatabase.insert(DATABASE_TABLE, null,cv); } } 

Hope I helped.