Multiple Table SQLite Db Adapter(S) in Android

Multiple Table SQLite DB Adapter(s) in Android?

Here is the solution I eventually ended up implementing. It's kind of a mash-up from info gained in the Commonsware books, and some stuff around the web that I wish I bookmarked cause I want to give credit:

For each type of data that I need to pull from the db, I create an "adapter" class (not subclassed from anything). These adapter classes hold all of the methods necessary for accessing the db for that piece of info. For example, if I had three tables in my db:

  1. Cars
  2. Boats
  3. Motorcycles

I would have three adapters that would look similar to the following(I'm only putting in one as a demo, but the idea is the same for each):

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 I have one of these classes "adapters" for each table.

When my app splash screen starts, I use the technique presented Android For Beginners: Creating multiple SQLite Tables for Android

So my main DBAdapter (which is responsible for creating all of my tables in a single 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 only gets called when the app first starts and its only responsibility is to create/upgrade the tables. All other access to the data is done through the individual "adapter" class. I've found that this works perfectly and does not create the versioning issues that I mentioned earlier.

Multiple Tables on Android DB - no such table

You have a few problems I noticed right away:

  1. You simply don't have a String to create a table named etitarifftable.
  2. You aren't executing your CREATE TABLE Strings.
  3. You are changing the Java code, but not telling SQLiteOpenHelper to update the SQL schema.
  4. Tables can only have one Primary Key

Simple solutions:

  1. Either create etitarifftable or use the appropriate table name in your query.

  2. Add the appropriate execSQL() statements to DbHelper#onCreate().

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_ETI_TABLE);
        db.execSQL(CREATE_DU_TABLE);
    }

  3. Increment DATABASE_VERSION which will call onUpgrade(). In its most basic form onUpgrade() should look like this:

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS etisalatdb");
        db.execSQL("DROP TABLE IF EXISTS dudatabase");
        onCreate(db);
    }

  4. Remove one of your Primary Key declarations from both tables:

    "CREATE TABLE etisalatdb (EtiTariffDB.KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + ...

Finally, I have a feeling there are more errors, but your LogCat will lead you right to them. (Also I'm not sure why you have two different adapters... )

Multiple SQLite Tables

Things to try:

  1. SimpleCursorAdapter requires a column named _id in its Cursor. If the Cursor does not contain a column named _id, an exception will be thrown.

    Change _id2 back to _id to fix this issue.

  2. Go to Settings --> Apps --> [your app] --> Clear data each time you run your (updated) app. This is important because you want to be 100% certain that you are working with a clean slate each time you run your app.

  3. Use a ListActivity instead of an Activity. This makes your life easier (don't have to deal with the ListView directly) and may lessen the probability of error. Make sure you call setListAdapter(adapter) in onCreate.

  4. Don't use startManagingCursor... it's deprecated as Android 3.0. You should use the CursorLoaders and the LoaderManager introduced in Android 3.0 (and supported back to Android 1.6 in the compatibility package).

  5. Don't create multiple WorkingAdapter instances (or whatever). This is just making your code messier and making it more likely that you leak your SQLiteDatabase by forgetting to close it. You can read more about this here:

    How to make my SQLiteDatabase a Singleton?

Best practices for working with multiple tables

My database adapter. An instance is always stored in MyApplication which inherites from Application. Just think about a second table where I defined the first one... currently this is just a short version, in reality this adapter handles 7 tables in the database.

public class MyDbAdapter {
    private static final String LOG_TAG = MyDbAdapter.class.getSimpleName();

    private SQLiteDatabase mDb;
    private static MyDatabaseManager mDbManager;

    public MyDbAdapter() {
        mDbManager = new MyDatabaseManager(MyApplication.getApplication());
        mDb = mDbManager.getWritableDatabase();
    }

    public static final class GameColumns implements BaseColumns {
        public static final String TABLE = "game";
        public static final String IMEI = "imei";
        public static final String LAST_UPDATE = "lastupdate";
        public static final String NICKNAME = "nickname";
    }

    public String getImei() {
        checkDbState();
        String retValue = "";
        Cursor c = mDb.rawQuery("SELECT imei FROM " + GameColumns.TABLE, null);
        if (c.moveToFirst()) {
            retValue = c.getString(c.getColumnIndex(GameColumns.IMEI));
        }
        c.close();
        return retValue;
    }

    public void setImei(String imei) {
        checkDbState();
        ContentValues cv = new ContentValues();
        cv.put(GameColumns.IMEI, imei);
        mDb.update(GameColumns.TABLE, cv, null, null);
    }

    public boolean isOpen() {
        return mDb != null && mDb.isOpen();
    }

    public void open() {
        mDbManager = new MyDatabaseManager(MyApplication.getApplication());
        if (!isOpen()) {
            mDb = mDbManager.getWritableDatabase();
        }
    }

    public void close() {
        if (isOpen()) {
            mDb.close();
            mDb = null;
            if (mDbManager != null) {
                mDbManager.close();
                mDbManager = null;
            }
        }
    }

    private void checkDbState() {
        if (mDb == null || !mDb.isOpen()) {
            throw new IllegalStateException("The database has not been opened");
        }
    }

    private static class MyDatabaseManager extends SQLiteOpenHelper {
        private static final String DATABASE_NAME = "dbname";
        private static final int DATABASE_VERSION = 7;

        private MyDatabaseManager(Context context) {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

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

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            Log.w(LOG_TAG, "Upgrading database from version " + oldVersion + " to " + newVersion + "!");
        }

        private void dropDatabase(SQLiteDatabase db) {
            db.execSQL("DROP TABLE IF EXISTS " + GameColumns.TABLE);
        }

        private void createGameTable(SQLiteDatabase db) {
            db.execSQL("CREATE TABLE " + GameColumns.TABLE + " ("
                    + GameColumns._ID + " INTEGER PRIMARY KEY,"
                    + GameColumns.IMEI + " TEXT,"
                    + GameColumns.LAST_UPDATE + " TEXT,"
                    + GameColumns.NICKNAME + " TEXT);");
            ContentValues cv = new ContentValues();
            cv.put(GameColumns.IMEI, "123456789012345");
            cv.put(GameColumns.LAST_UPDATE, 0);
            cv.put(GameColumns.NICKNAME, (String) null);
            db.insert(GameColumns.TABLE, null, cv);
        }
    }
}

android multi table adapters queries

Make various instances of SQLiteHelper sqLiteHelper1, sqLiteHelper2;

sqLiteHelper1 =  SQLiteHelper(context, MYDATABASE_NAME1, null,MYDATABASE_VERSION);
sqLiteHelper2 =  SQLiteHelper(context, MYDATABASE_NAME2, null,MYDATABASE_VERSION);
sqLiteDatabase1 = sqLiteHelper1.getWritableDatabase();
sqLiteDatabase2 = sqLiteHelper2.getWritableDatabase();

using sqLiteDatabase1, sqLiteDatabase2, access the data of different tables..

I hope this works :)

Confused regarding SQLiteOpenHelper and creating multiple tables

The following example code creates two tables. I also added code for creating and inserting data.

import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class MyDB {
    public static final String KEY_ROWID = "_id";
    public static final String KEY_FIRSTNAME = "ID";
    public static final String KEY_LASTNAME = "CS";
    public static final String KEY_DESIGNATION = "CN";
    public static final String KEY_DN = "DN";  

    private static final String TAG = "MyDB";       
    private static final String DATABASE_NAME = "test.db";    
    private static final int DATABASE_VERSION = 1;

    private static final String DATABASE_CREATE_ValidateUser_DriverInfo =
        "create table tabletest1 (_id integer primary key autoincrement, "
        + "ID text not null, CS text not null, CN text not null, DN text not null);";

    private static final String DATABASE_CREATE_ValidateUser_TripInfo =
        "create table tabletest2 (_id integer primary key autoincrement, "
        + "TI text not null, PU text not null, LN text not null, FN text not null, Origin varchar not null, De text not null);";

    private Context context;
    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;

    public MyDB(Context ctx) {
        this.context = ctx;
        DBHelper = new DatabaseHelper(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(DATABASE_CREATE_ValidateUser_DriverInfo);
                db.execSQL(DATABASE_CREATE_ValidateUser_TripInfo);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, 
                              int newVersion) {
            Log.w(TAG, "Upgrading database from version " + oldVersion 
                 + " to " + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS tabletest1");
            db.execSQL("DROP TABLE IF EXISTS tabletest2");
            onCreate(db);
        }
    }

    public MyDB open() throws SQLException {
        db = DBHelper.getWritableDatabase();
        return this;
    }

    //---closes the database---    
    public void close() {
        DBHelper.close();
    }

    public long insertTitle(ContentValues initialValues, String TableName) {
        return db.insert(TableName, null, initialValues);
    }
}

USe the following code to insert the data from your required activity. 

MyDB mmdb=new MyDB(getBaseContext());
mmdb.open();

initialValues = new ContentValues();
initialValues.put("ID", ID);
initialValues.put("CS", CS);
initialValues.put("CN", CN);
initialValues.put("DN", DN);

mmdb.insertTitle(initialValues, "tabletest1");  

mmdb.close();

Related Topics

Display Unity Scene as Sub View in Android Studio

How to Clear Textfield Focus When Closing the Keyboard and Prevent Two Back Presses Needed to Exit App in Jetpack Compose

How to Calculate Phone's Movement in the Vertical Direction from Rest

How to Add Code Obfuscation for My Android Application

Android Kotlin Java.Io.Filenotfoundexception: /Storage/Emulated/0/Number.Txt: Open Failed: Eacces (Permission Denied)

How to Detect When the Notification/System Bar Is Opened

Set Text to Integer Value

Could Not Launch Emulator in Android Studio

Checkbox Gets Unchecked on Scroll in a Custom Listview

Error:Unable to Resolve Dependency for ':App@Debugandroidtest/Compileclasspath': Could Not Resolve Junit:Junit:4.12

Pass Arraylist Data into Soap Web Service in Android

How to Create/Write File in the Root of the Android Device

What Are the Overheads of Using Autoincrement for SQLite on Android

Getting Null Device Id While Registering to Gcm

Moving or Copying Data from One Node to Another in Firebase Database

Out of Memory Error While Loading Bitmaps

Android Ble Bluetoothgatt.Writedescriptor() Return Sometimes False

Android - Ondatechangedlistener - How to Set This


Leave a reply



Submit