samedi 31 octobre 2015

Show Data from Multiple Tables with connected id in SQLite

I have two tables in my SQLite database, first users and second jobs, where a user can have multiple jobs to do

Data in users table

id      name
1        Me
2       You

where id generated through : id integer primary key autoincrement

Data in jobs table

id     jobname     userid
1       JobA          2
2       JobB          2
3       JobC          1

where id generated through : id integer primary key autoincrement

where user_id fetched from users table

Now, I would like to loop through all the users and jobs table one by one, if user has some job to do then need to show Toast

JobC assigned to user1

DatabaseHandler.java:

public class DatabaseHandler extends SQLiteOpenHelper {

..................

public DatabaseHandler(Context context) {

   super(context, DATABASE_NAME, null, DATABASE_VERSION);

}

@Override
public void onCreate(SQLiteDatabase db) {
    // TODO Auto-generated method stub

    db.execSQL("CREATE TABLE " + TABLE_USERS + 
              "(id integer primary key autoincrement," +
              " name text);");

        db.execSQL("CREATE TABLE " + TABLE_JOBS + 
              "(id integer primary key autoincrement," +
              " jobname text" +
              " userid long);");        
}

// Insert data into users table
public long InsertUsers(String name) {

     try {
        SQLiteDatabase db;
        db = this.getWritableDatabase(); // Write Data

        ContentValues Val = new ContentValues();
        Val.put("name", name); 

        long rows = db.insert(TABLE_USERS, null, Val);

        db.close();
        return rows; // return rows inserted.

     } catch (Exception e) {
        return -1;
     }
}

    // Insert data into jobs table
public long InsertJobs(String jobname, long userid) {

     try {
        SQLiteDatabase db;
        db = this.getWritableDatabase(); // Write Data

        ContentValues Val = new ContentValues();
        Val.put("jobname", jobname); 
        Val.put("userid", userid); 

        long rows = db.insert(TABLE_JOBS, null, Val);

        db.close();
        return rows; // return rows inserted.

     } catch (Exception e) {
        return -1;
     }
}   

public String[] getUsersId() {

    String selectQuery = "SELECT id FROM " + TABLE_USERS;
          SQLiteDatabase db = this.getReadableDatabase();

    Cursor cursor = db.rawQuery(selectQuery, null);
             String[] data = null;

    if (cursor.moveToFirst()) {
        do {

            int id_row=cursor.getInt(cursor.getColumnIndex("id"));
                Log.d("TAG","id is ::"+id_row);             

        } while (cursor.moveToNext());
    }

    db.close();
    return data;
}

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

}

And in MainActivity.java:

    DatabaseHandler dh;

@Override
protected void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.activity_main);

            dh = new DatabaseHandler(MainActivity.this);

            buttonGetData.setOnClickListener(new OnClickListener() {

            @Override
            public void onClick(View v) {
                // TODO Auto-generated method stub
                dh.getUsersId();
                }
            });

   }

Aucun commentaire:

Enregistrer un commentaire