Pat
Pat

Reputation: 31

Handling multiple tables in SQLite on Android - why so complicated?

I'm desperately trying to set up a database with multiple tables which looks like:

nutrition.db:
Ingredient(ID, name, kcal); 
Meal(ID, name, ingredientId1, ingredientId2);
MealInstance(ID, mealId, date, amountOfIngr1, amountOfIngr2) ...

I stumbled across miscellaneous approaches - for instance expanding a Provider class like the one from the notepad tutorial (extends ContentProvider) by using tons of switch-cases or alternatively building up an entire CRUD-interface based on .execSQL yourself. However all of this seems absurdly complex to me for such an easy (and common?) task, which is why I believe I overlooked something. For some odd reason all tutorials that I've checked only use a single DB and a single table.

I'd really appreciate a tutorial recommendation or some hint.

Upvotes: 3

Views: 4592

Answers (2)

Michael
Michael

Reputation: 648

To create an Android app that maintains more than one SQLite table, here is one example:

public class MyDatabase {

    private static final String DATABASE_NAME = "my_database.db";
    private static final int DATABASE_VERSION = 1;

    public static final String TABLE_ONE = "tableOne";
    public static final String TABLE_TWO = "tableTwo";
    public static final String TABLE_THREE = "tableThree";

    public MyDatabase(Context _context)
    {
        mDbHelper = new MyDbOpenHelper(_context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    //add code to declare your columns for each table
    //add methods for opening/closing database, etc...

    private static class MyDbOpenHelper extends SQLiteOpenHelper {

    //SETUP THE NORMAL SQLite COMMANDS THAT YOU USE TO CREATE A TABLE
    private static final String CREATE_TABLE_ONE = "create table if not exists " + TABLE_ONE...;
    private static final String CREATE_TABLE_TWO = "create table if not exists " + TABLE_TWO...;
    private static final String CREATE_TABLE_THREE = "create table if not exists " + TABLE_THREE...;

        public StatsDbOpenHelper(Context context, String name, CursorFactory factory, int version)
        {
            super(context, name, factory, version);
        }

        @Override
        public void onCreate(SQLiteDatabase _db)
        {
            //ALL OF YOUR TABLES ARE CREATED HERE WHEN YOUR DATABASE IS FIRST CREATED
            _db.execSQL(CREATE_TABLE_ONE);
            _db.execSQL(CREATE_TABLE_TWO);
            _db.execSQL(CREATE_TABLE_THREE);
        }     
    }
}  

Upvotes: 0

J.G.Sebring
J.G.Sebring

Reputation: 5964

I recommend having a look at google's app ioshed.

Source code for the database: ioshed - database source code

Upvotes: 2

Related Questions