Problems with storing multiple tables in one SQLitedatabase

Question

I was following this example Multiple Table SQLite DB Adapter(s) in Android? on storing multiple tables in one database.

I've created 4 DBAdapters(DBAdapter, CarsDBAdapter, BoatsDBAdapter and CyclesDBAdapter). But in one of DBAdapter, I've created 3 tables in the db adapter. Then I've created 3 non-DBadaper classes for the other 3 db adapters. THe tables are not inserted into the database plus I'm not sure which DBAdapter i should declare in the non dbadapter classes.

I need help. I would appreciate any help provided.

Below are the codes I've typed.

DBAdapter.java

public class DBAdapter
{
    public static final String DATABASE_NAME = "stuffIOwn";
    public static final int DATABASE_VERSION = 2;

    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$

    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$

    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$

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

    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         
        }     
    }//end DatabaseHelper class

    public DBAdapter open() throws SQLException      
    {         
    this.db = this.DBHelper.getWritableDatabase();         
    return this;     
    }      
    /**      
     * close the db 
     *return type: void      
     */    
    public void close()      
    {        
        this.DBHelper.close();     
    }

}

CarsDBAdapter.java

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)
        {           
        }
    }// end DatabaseHelper class

    public CarsDBAdapter(Context ctx)
    {
        this.mCtx = ctx;
    }

    public CarsDBAdapter open() throws SQLException
    {
        this.mDbHelper = new DatabaseHelper(this.mCtx);
        this.mDb = this.mDbHelper.getWritableDatabase();
        return this;
    }

    public void close()
    {
        this.mDbHelper.close();
    }

    public long insertCars(String name, String model, String year)
    {
        ContentValues initialValues = new ContentValues();
        initialValues.put(NAME, name);
        initialValues.put(MODEL, model);
        initialValues.put(YEAR, year);
        return mDb.insert(DATABASE_TABLE, null, initialValues);
    }

    public long create(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); 
    }

    public boolean deleteCar(long rowId) 
    {          
        return this.mDb.delete(DATABASE_TABLE, ROW_ID + "=" + rowId, null) > 0; //$NON-NLS-1$     
    }

     public Cursor getAllCars() 
     {          
         return this.mDb.query(DATABASE_TABLE, new String[] { ROW_ID, NAME, MODEL, YEAR }, null, null, null, null, null);       
     }

     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;
     }

     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;  
     }

}//end CarsDBAdapter class

did the same for BoatsDBAdapter and CyclesDBAdapter classes but the insert, update, delete methods names are different. These are the non DBAdapter classes

Cars.java

public class Cars extends Activity
{
    final Context context = this;

     /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) 
    {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.cars);

        CarsDBAdapter carsDB = new CarsDBAdapter(this);

        //DBAdapter dbA = new DBAdapter(this);

        carsDB.open();
        long id;
        id = carsDB.insertCars("Mercedes", "MERCDS", "2000");
        id = carsDB.insertCars("BMW", "BMWTO", "1999");
        carsDB.close();

        carsDB.open();

        Cursor c = carsDB.getAllCars();
        if (c.moveToFirst())
        {
            do
            {
                DisplayContact(c);
            } while (c.moveToNext());
        }

        carsDB.close();

        Button btnMenu = (Button) findViewById(R.id.btnMenu);
        btnMenu.setOnClickListener(new View.OnClickListener()
            {

                @Override
                public void onClick(View v)
                {
                    Intent menuIntent = new Intent(context, VehiclesMenu.class);
                    startActivity(menuIntent);

                }
            });
    }//end onCreate()

    public void DisplayContact(Cursor c)
    {
        Toast.makeText(
                this,
                "id: " + c.getString(0) + "\n" + "Name: " + c.getString(1)
                        + "\n" + "Model:  " + c.getString(2) + "\n" + "Year: " + c.getString(3),  Toast.LENGTH_LONG)
                .show();
    }
}

I did the same for the Boats.java and Cycles.java except that I declare BoatsDBAdapter in Boats.java and CyclesDBAdapter in Cycles.java

In Boats.java

BoatsDBAdapter boatsDB = new BoatsDBAdapter(this);

        boatsDB.open();
        long id;
        id = boatsDB.insertBoats("Jet-ski", "ERTYU", "2002");
        id = boatsDB.insertBoats("Motor-boat", "MBCTY", "2003");
        boatsDB.close();

        boatsDB.open();

        Cursor c = boatsDB.getAllBoats();
        if (c.moveToFirst())
        {
            do
            {
                DisplayContact(c);
            } while (c.moveToNext());
        }

        boatsDB.close();

and in Cycles.java

CyclesDBAdapter cyclesDB = new CyclesDBAdapter(this);

        cyclesDB.open();
        long id;
        id = cyclesDB.insertCycles("Mountain-bike", "ADCFG", "2004");
        id = cyclesDB.insertCycles("Dirt-bike", "VBNGH", "2006");
        cyclesDB.close();

        cyclesDB.open();
        Cursor c = cyclesDB.getAllCycles();
        if (c.moveToFirst())
        {
            do
            {
                DisplayContact(c);
            } while (c.moveToNext());
        }
        cyclesDB.close();

UPDATE! I've made some updates to my codes. I've added the insert codes into the CarsDBAdapter. But the the insert codes are not in the example which is in the link above. I've removed the BoatsDBAdapter, CyclesDBAdapter, Boats and Cycles classes as the codes are similar to CarsDBAdapter and Cars classes.

Answer 1

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 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.