Android Sqlite multiple cursor for multiple table

Question

I have a SQLite database with multiple table however some table are linked and some are not but what i want is to read from one table and write to another table concurrently ... so my question is here that can i have two different cursor pointing to respective table or is there any other way ... please let me know ... suggestion are appreciated

Answer 1

Have a look at the AsyncQueryHandler class. It will help you have your ContentProvider make asynchronous/concurrent queries

Answer 2

You only need cursors to read data from the tables, not write. Set up methods in your db to write the info you need to the proper table, and methods in your activity to call up cursors and get the data from them and you're all set.

db class

public class TestDB {
    // *******************************************************************
    // DB info
    // *******************************************************************
    private static final String TEST_DATABASE_NAME = "TestDB";
    private static final int TEST_DATABASE_VERSION = 3;

    // *******************************************************************
    // list table
    // *******************************************************************
    public static final String FIRST_TABLE = "first";
    public static final String FIRST_ROWID = "_id";
    public static final String FIRST_NAME = "first_name";

    private static final String CREATE_FIRST_TABLE = "create table "
            + LIST_TABLE + " (_id integer primary key autoincrement,"
            + "first_name text not null unique);";

    // *******************************************************************
    // category table
    // *******************************************************************
    public static final String SECOND_TABLE = "second";
    public static final String SECOND_ROWID = "_id";
    public static final String SECOND_NAME = "second_name";

    private static final String CREATE_SECOND_TABLE = "create table "
            + CATEGORY_TABLE + " (_id integer primary key autoincrement,"
            + "second_name text not null unique);";
    // *******************************************************************
    // control variables
    // *******************************************************************
    private DBHelper mDBHelper;
    private SQLiteDatabase mDb;
    private final Context mCtx;

    private static class DBHelper extends SQLiteOpenHelper {
        DBHelper(Context context) {
            super(context, TEST_DATABASE_NAME, null,
                    TEST_DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) {
            db.execSQL(CREATE_FIRST_TABLE);
            db.execSQL(CREATE_SECOND_TABLE);
        }

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

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

    public GroceryDB open() throws SQLException {
        mDBHelper = new DBHelper(mCtx);
        mDb = mDBHelper.getWritableDatabase();
        return this;
    }

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

    // *******************************************************************
    // Record creation methods
    // *******************************************************************
    public long createFirst(String name) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(FIRST_NAME, name);
        return mDb.insertWithOnConflict(FIRST_TABLE, null, initialValues,
                SQLiteDatabase.CONFLICT_IGNORE);
    }

    public long createSecond(String name) {
        ContentValues initialValues = new ContentValues();
        initialValues.put(Second_NAME, name);
        return mDb.insertWithOnConflict(CATEGORY_TABLE, null, initialValues,
                SQLiteDatabase.CONFLICT_IGNORE);
    }

    // *******************************************************************
    // Fetch all records methods
    // *******************************************************************

    public Cursor fetchAllFirst() {
        return mDb.query(FIRST_TABLE, new String[] { FIRST_ROWID, FIRST_NAME },
                null, null, null, null, null);
    }

    public Cursor fetchAllSecond() {
        return mDb.query(SECOND_TABLE, new String[] { SECOND_ROWID,
                SECOND_NAME }, null, null, null, null, null);
    }
}

Then, say you want to copy the first item from FIRST_TABLE to SECOND_TABLE, in your activity class you can do something like this:

private Cursor firstCursor;
private TestDB mDbHelper;

mDbHelper = new TestDB(getActivity());
mDbHelper.open();

firstCursor = mDbHelper.fetchAllFirst();
getActivity().startManagingCursor(itemCursor);
firstCursor.moveToFirst;
mDbHelper.createSecond(firstCursor.getString(1));
mDbHelper.close();

I've left out several things (like checking make sure your returned cursor isn't null, displaying the contents of the cursor to the user to choose what gets copied over, etc) but this should demonstrate the basics of one way to copy from one table to another.