Android SQLite - Joining two tables and render the result as list
I have two tables in sqlite:
projects
---id
---projectname
outlets
---id
---pid
---outletname
---status
I want to render the projectname and outletname where status satisfy a condition. I want to render the resultant output as a list. I am using the following codes:
private void showProjectStatus() {
sqLiteDatabase = sqLiteHelper.getWritableDatabase();
cursor = sqLiteDatabase.rawQuery("select projectName, OutletName from projects inner join outlets on outlets.pid = projects.id where outlets.Status = 1"+"", null);
//cursor = sqLiteDatabase.rawQuery("SELECT * FROM '"+SQLiteHelper.TABLE_NAME1+"' where Status = 1"+"", null);
The above cursor works perfectly.
ID_Array.clear();
ProjectName_Array.clear();
OutletName_Array.clear();
if (cursor != null && cursor.getCount() > 0) {
if (cursor.moveToFirst()) {
do {
ID_Array.add(cursor.getString(cursor.getColumnIndex(SQLiteHelper.Table1_Column_ID)));
OutletName_Array.add(cursor.getString(cursor.getColumnIndex(SQLiteHelper.Table1_Column_PID)));
ProjectName_Array.add(cursor.getString(cursor.getColumnIndex(SQLiteHelper.Table1_Column_OutletName)));
I need help in rendering the above. How do I render the values returned by the cursor directly as list instead of referring to actual columns in the tables.
} while (cursor.moveToNext());
}
} else {
Toast.makeText( this, "No data to display!", Toast.LENGTH_SHORT ).show();
}
I need help in rendering the above. How do I render the values returned by the cursor directly as list instead of referring to actual columns in the tables.
In short you can't using the standard API (and it would be as if not more difficult using an alternative as instead of a Cursor you would have a result set). So a Cursor is what you get results from.
Looking at your code, you might be getting confused as you are trying to get non-existent values from the Cursor.
By using SELECT projectName, OutletName FROM ..... you would get a Cursor like (2 columns) :-
You are saying that you want 2 columns in the Cursor who's names are projectName and OutletName. Trying to use cursor.getColumnIndex(SQLiteHelper.Table1_Column_ID) will result in -1 being returned (column name not found in the Cursor) and there is no column with an offset of -1 in the Cursor.
Likewise for the PID.
If you need the ID andthe PID columns in addition to the outletName and projectName columns then change the query to :-
cursor = sqLiteDatabase.rawQuery("select projects.id,pid,projectName, OutletName from projects inner join outlets on outlets.pid = projects.id where outlets.Status = 1"+"", null).
In which case the Cursor would be be like (4 columns) :-
Assuming that you want all four columns, exach in seperate Arrays then perhaps consider :-
private void showProjectStatus() {
ID_Array.clear();
ProjectName_Array.clear();
OutletName_Array.clear();
sqLiteDatabase = sqLiteHelper.getWritableDatabase();
Cursor cursor = sqliteDatabase.query(
"projects inner join outlets on outlets.pid = projects.id",
new String[]{
"projects." + SQLiteHelper.Table1_Column_ID,
SQLiteHelper.Table1_Column_PID,
"projectName", //<<<<< defined value not used as unknown
SQLiteHelper.Table1_Column_OutletName
},
"outlets.Status=1",
null,null,null,null
);
while (cursor.moveToNext()) {
ID_Array.add(cursor.getString(cursor.getColumnIndex(SQLiteHelper.Table1_Column_ID));
ProjectName_Array.add(cursor.getString(cursor.getColumnIndex("projectName"));
OutletName_Array.add(cursor.getString(cursor.getColumnIndex(SQLiteHelper.Table1_Column_OutletName));
}
cursor.close()
}
- Note SQLiteHelper.Table1_Column_ProjectName or SQLiteHelper.Table1_Column_projectName or whatever the value may be defined as has not been used.
- It should be noted that The Cursor getColumnIndex has/had a bug in that it is not case dependant. As such column names should be excatly the same as those used in the query.
- There is no use checking a Cursor returned from any of the SQLiteDatabase methods for null. A valid Cursor will always be returned. If there are no rows, the Cursor's count will be 0 and any attempt to move to a position other than before the first row (-1) will return false (hence how the while loop works).
- It is generally recommended to use the query convenience method rather than the rawQuery method (however either can be used in the above).
Note The above code is in-principle code; it has not been run or tested and may therefore contain some errors.
However as you appear to be wanting to drive a ListView then if you use a subclass of CursorAdapter, much of the work can be done by the adapter on your behalf.
The SimpleCursorAdapter, such an adapter, is pretty flexible allowing layouts to display multiple columns. One of the distinct advantages of CursorAdapters is that ItemSelection (Click LongClick) events pass the id to the method (for non CursorAdapters the value passed is the position as a long). To do this the Cursor MUST have a column specifially name _id (Base.Columns._ID) and that column should be an alias of the rowid (i.e. the column in the table is defined using INTEGER PRIMARY KEY (optionally with AUTOINCREMENT)).
SimpleCursorAdapter Example
SQLiteHelper.java
public class SQLiteHelper extends SQLiteOpenHelper {
public static final String DBNAME = "mydb";
public static final int DBVERSION = 1;
public static final String TABLE_NAME1 = "projects";
public static final String TABLE_NAME2 = "outlets";
public static final String TABLE1_COLUMN_ID = BaseColumns._ID;
public static final String TABLE1_COLUMN_PROJECTNAME = "projectname";
public static final String TABLE2_COLUMN_ID = BaseColumns._ID;
public static final String TABLE2_COLUMN_PID = "pid";
public static final String TABLE2_COLUMN_OUTLETNAME = "outletname";
public static final String TABLE2_COLUMN_STATUS = "status";
public SQLiteHelper(Context context) {
super(context, DBNAME, null, DBVERSION);
}
@Override
public void onConfigure(SQLiteDatabase db) {
super.onConfigure(db);
}
@Override
public void onCreate(SQLiteDatabase db) {
String createProjectsTableSQL =
"CREATE TABLE IF NOT EXISTS " + TABLE_NAME1 +
"(" +
TABLE1_COLUMN_ID + " INTEGER PRIMARY KEY," +
TABLE1_COLUMN_PROJECTNAME + " TEXT" +
")";
String createOutletsTableSQL =
"CREATE TABLE IF NOT EXISTS " + TABLE_NAME2 +
"(" +
TABLE2_COLUMN_ID + " INTEGER PRIMARY KEY," +
TABLE2_COLUMN_PID + " INTEGER REFERENCES " + TABLE_NAME1 + "(" +
TABLE1_COLUMN_ID +
") ON DELETE CASCADE ON UPDATE CASCADE, " +
TABLE2_COLUMN_OUTLETNAME + " TEXT," +
TABLE2_COLUMN_STATUS + " INTEGER" +
")";
db.execSQL(createProjectsTableSQL);
db.execSQL(createOutletsTableSQL);
}
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
}
public long addProject(String projectName) {
ContentValues cv = new ContentValues();
cv.put(TABLE1_COLUMN_PROJECTNAME,projectName);
return this.getWritableDatabase().insert(TABLE_NAME1,null,cv);
}
public long addOutlet(String outletName, long projectId, boolean status) {
ContentValues cv = new ContentValues();
cv.put(TABLE2_COLUMN_OUTLETNAME,outletName);
cv.put(TABLE2_COLUMN_PID,projectId);
cv.put(TABLE2_COLUMN_STATUS,status);
return this.getWritableDatabase().insert(TABLE_NAME2,null,cv);
}
public Cursor getProjectsWithOutletsWithStatusOn() {
return this.getWritableDatabase().query(
TABLE_NAME1 + " INNER JOIN "
+ TABLE_NAME2 + " ON " +
TABLE_NAME1 + "." + TABLE1_COLUMN_ID + "=" +
TABLE_NAME2 + "." + TABLE2_COLUMN_PID,
new String[]{
TABLE_NAME1 + "." + TABLE1_COLUMN_ID,
TABLE2_COLUMN_PID,
TABLE1_COLUMN_PROJECTNAME,
TABLE2_COLUMN_OUTLETNAME,
TABLE2_COLUMN_STATUS //? optional as will always be 1
},
TABLE2_COLUMN_STATUS + "=1",
null,null,null,null
);
}
}
MainActivity.java
public class MainActivity extends AppCompatActivity {
SQLiteHelper mHelper;
Cursor mCursor;
SimpleCursorAdapter mAdapter;
ListView mListView;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
mListView = this.findViewById(R.id.listview);
mHelper = new SQLiteHelper(this);
addSomeDataIfNone();
manageListView();
}
@Override
protected void onDestroy() {
super.onDestroy();
mCursor.close(); // close the Cursor (more important for invoked activities)
}
@Override
protected void onResume() {
super.onResume();
manageListView(); // refresh Listview when resuming activity as data may have changed
}
private void addSomeDataIfNone() {
if (DatabaseUtils.queryNumEntries(mHelper.getWritableDatabase(),SQLiteHelper.TABLE_NAME1) < 1) {
long idp1 = mHelper.addProject("Project001");
mHelper.addOutlet("OutletP1001",idp1,false);
mHelper.addOutlet("OutletP1002",idp1,true);
mHelper.addOutlet("Outletp1003",idp1,true);
long idp2 = mHelper.addProject("Prject002");
mHelper.addOutlet("OutletP2001",idp2,true);
mHelper.addOutlet("OutletP2002",idp2,false);
}
}
private void manageListView() {
mCursor = mHelper.getProjectsWithOutletsWithStatusOn();
if (mAdapter == null) {
mAdapter = new SimpleCursorAdapter(
this,
android.R.layout.simple_list_item_2,
mCursor,
new String[]{
SQLiteHelper.TABLE1_COLUMN_PROJECTNAME,
SQLiteHelper.TABLE2_COLUMN_OUTLETNAME
},
new int[]{
android.R.id.text1,
android.R.id.text2
},
0
);
mListView.setAdapter(mAdapter);
mListView.setOnItemClickListener(new AdapterView.OnItemClickListener() {
@Override
public void onItemClick(AdapterView<?> parent, View view, int position, long id) {
Toast.makeText(view.getContext(),
"You clicked " +
"Project = " + mCursor.getString(mCursor.getColumnIndex(SQLiteHelper.TABLE1_COLUMN_PROJECTNAME)) +
" Outlet = " + mCursor.getString(mCursor.getColumnIndex(SQLiteHelper.TABLE2_COLUMN_OUTLETNAME)) +
" Project ID = " + mCursor.getString(mCursor.getColumnIndex(SQLiteHelper.TABLE1_COLUMN_ID)) +
" Outlet Status = " + (mCursor.getInt(mCursor.getColumnIndex(SQLiteHelper.TABLE2_COLUMN_STATUS))> 0)
,
Toast.LENGTH_SHORT).show();
}
});
} else {
mAdapter.swapCursor(mCursor);
}
}
}
Result
