Multiple SQLite Tables

Question

I have been trying for almost a week now to create an SQLite database with more than one table, but with no success. I have searched for hours and looked at all the threads on the topic here, and I have no idea what I'm doing wrong.

Here's a code I got from the internet which was for one table, and I just added another one, and it doesn't work anymore.

Adapter:

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteDatabase.CursorFactory;

public class WorkingAdapter {

    public static final String WORKINGDATABASE_NAME = "WORKING_DATABASE";
    public static final String WORKINGDATABASE_TABLE = "WORKING_TABLE";
    public static final int MYDATABASE_VERSION = 1;
    public static final String KEY_ID = "_id";
    public static final String KEY_CONTENT = "Content";

    //create table MY_DATABASE (ID integer primary key, Content text not null);
    private static final String SCRIPT_CREATE_WORKING_DATABASE =
        "create table " + WORKINGDATABASE_TABLE + " ("
        + KEY_ID + " integer primary key autoincrement, "
        + KEY_CONTENT + " text not null);";

    public static final String WORKINGDATABASE_TABLE2 = "MY_TABLE2";
    public static final String KEY_ID2 = "_id2";
    public static final String KEY_CONTENT2 = "Content2";

    //create table MY_DATABASE (ID integer primary key, Content text not null);
    private static final String SCRIPT_CREATE_WORKING_DATABASE2 =
        "create table " + WORKINGDATABASE_TABLE2 + " ("
        + KEY_ID2 + " integer primary key autoincrement, "
        + KEY_CONTENT2 + " text not null);";

    private SQLiteHelper sqLiteHelper;
    private SQLiteDatabase sqLiteDatabase;

    private Context context;

    public WorkingAdapter(Context c){
        context = c;
    }

    public WorkingAdapter openToRead() throws android.database.SQLException {
        sqLiteHelper = new SQLiteHelper(context, WORKINGDATABASE_NAME, null, MYDATABASE_VERSION);
        sqLiteDatabase = sqLiteHelper.getReadableDatabase();
        return this;    
    }

    public WorkingAdapter openToWrite() throws android.database.SQLException {
        sqLiteHelper = new SQLiteHelper(context, WORKINGDATABASE_NAME, null, MYDATABASE_VERSION);
        sqLiteDatabase = sqLiteHelper.getWritableDatabase();
        return this;    
    }

    public void close(){
        sqLiteHelper.close();
    }

    public long insert(String content){

        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_CONTENT, content);
        return sqLiteDatabase.insert(WORKINGDATABASE_TABLE, null, contentValues);
    }

public long insert2(String content){

        ContentValues contentValues = new ContentValues();
        contentValues.put(KEY_CONTENT2, content);
        return sqLiteDatabase.insert(WORKINGDATABASE_TABLE2, null, contentValues);
    }

    public int deleteAll(){
        return sqLiteDatabase.delete(WORKINGDATABASE_TABLE, null, null);
    }

    public Cursor queueAll(){
        String[] columns = new String[]{KEY_ID, KEY_CONTENT};
        Cursor cursor = sqLiteDatabase.query(WORKINGDATABASE_TABLE, columns, 
                null, null, null, null, null);

        return cursor;
    }

    public Cursor queueAll2(){
        String[] columns = new String[]{KEY_ID2, KEY_CONTENT2};
        Cursor cursor = sqLiteDatabase.query(WORKINGDATABASE_TABLE2, columns, 
                null, null, null, null, null);

        return cursor;
    }

    public class SQLiteHelper extends SQLiteOpenHelper {

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

        @Override
        public void onCreate(SQLiteDatabase db) {
            // TODO Auto-generated method stub
            db.execSQL(SCRIPT_CREATE_WORKING_DATABASE);
            db.execSQL(SCRIPT_CREATE_WORKING_DATABASE2);

        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
            // TODO Auto-generated method stub
            db.execSQL("DROP TABLE IF EXISTS " + WORKINGDATABASE_TABLE);
            db.execSQL("DROP TABLE IF EXISTS " + WORKINGDATABASE_TABLE2);
            onCreate(db);
        }

    }

}

Activity:

import android.app.Activity;
import android.database.Cursor;
import android.os.Bundle;
import android.widget.ListView;
import android.widget.SimpleCursorAdapter;

public class AndroidSQLite extends Activity {

    private WorkingAdapter myWorkingAdapter;

    /** Called when the activity is first created. */
    @Override
    public void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.main);
        ListView listContent = (ListView)findViewById(R.id.contentlist);

        /*
         *  Create/Open a SQLite database
         *  and fill with dummy content
         *  and close it
         */
        myWorkingAdapter = new WorkingAdapter(this);
        myWorkingAdapter.openToWrite();
        myWorkingAdapter.deleteAll();

        myWorkingAdapter.insert("A for Apply");
        myWorkingAdapter.insert("B for Boy");
        myWorkingAdapter.insert("C for Cat");
        myWorkingAdapter.insert("D for Dog");
        myWorkingAdapter.insert("E for Egg");
        myWorkingAdapter.insert("F for Fish");
        myWorkingAdapter.insert("G for Girl");
        myWorkingAdapter.insert("H for Hand");
        myWorkingAdapter.insert("I for Ice-scream");
        myWorkingAdapter.insert("J for Jet");
        myWorkingAdapter.insert("K for Kite");
        myWorkingAdapter.insert("L for Lamp");
        myWorkingAdapter.insert("M for Man");
        myWorkingAdapter.insert("N for Nose");
        myWorkingAdapter.insert("O for Orange");
        myWorkingAdapter.insert("P for Pen");
        myWorkingAdapter.insert("Q for Queen");
        myWorkingAdapter.insert("R for Rain");
        myWorkingAdapter.insert("S for Sugar");
        myWorkingAdapter.insert("T for Tree");
        myWorkingAdapter.insert("U for Umbrella");
        myWorkingAdapter.insert("V for Van");
        myWorkingAdapter.insert("W for Water");
        myWorkingAdapter.insert("X for X'mas");
        myWorkingAdapter.insert("Y for Yellow");
        myWorkingAdapter.insert("Z for Zoo");

        myWorkingAdapter.insert2("W FOR WORKING");

        myWorkingAdapter.close();

        /*
         *  Open the same SQLite database
         *  and read all it's content.
         */
        myWorkingAdapter = new WorkingAdapter(this);
        myWorkingAdapter.openToRead();

        Cursor cursor = myWorkingAdapter.queueAll2();
        startManagingCursor(cursor);

        String[] from = new String[]{WorkingAdapter.KEY_CONTENT};
        int[] to = new int[]{R.id.text};

        SimpleCursorAdapter cursorAdapter =
            new SimpleCursorAdapter(this, R.layout.row, cursor, from, to);

        listContent.setAdapter(cursorAdapter);

        myWorkingAdapter.close();


    }
}

I have endlessly looked for exaples for SQLite databases with more than one table, and non of them worked, and did exactly what I read in all the pages I found on the topic, can you please tell me what am I doing wrong?

Thanks :)

Answer 1

change

public static final int MYDATABASE_VERSION = 1;

to

public static final int MYDATABASE_VERSION = 2;

and every time you change your database change incrementing it will trigger onUpgrade decrementing it will trigger onDowngrade (if you need to do that and you of course override it)

you can read more on the constructor from android developer resources

Answer 2

_id is a special column in an SQLLite table. It is the primary key of every table and if I remember correctly it will be created automatically if you don't define it. I suspect you changing it to _id2 somehow causing problem. So change it back:

public static final String KEY_ID2 = "_id";

And try like that.

Also note that (as it is now)your app will try to create the tables each time it runs. So if it creates 1st table at first run but fails to create 2nd table and you fix the error and run your app again, this time it will fail at creating 1st table because it is already created. So to avoid these problems make sure you make clean installation & handle database versioning properly.

Answer 3

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?

Answer 4

What Caner said, also you don't need KEY_CONTENT2 as 'Content2', column name is unique within a table, so naming it 'Content' is enough.

Also, have you tried clearing the app database? Goto Settings->Applications->Your App-> Clear data. Your database may have been created at version 1 with old version, but you didn't specify version 2, so the method onUpgrade() will not be called. So either clear the app database, or bump the db version to 2.