and
- Note that the the Marks_id column is not needed and that the primary key is a composite made up of the columns that reference the user and the course.
Additional
here's the above App amended to display the marks (well just one mark per course (course not shown for simplicity)).
The App will by default show Mary's marks, but a Button All USERS can be clicked to display all marks (again just the one mark per course).
First to cater for displaying the list the layout needs to have a ListView and for the ALL USERS a Button so the layout used (activity_main.xml) is:-
<?xml version="1.0" encoding="utf-8"?>
<LinearLayout
xmlns:android="http://schemas.android.com/apk/res/android"
xmlns:tools="http://schemas.android.com/tools"
android:orientation="vertical"
android:layout_width="match_parent"
android:layout_height="match_parent"
tools:context=".MainActivity">
<TextView
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="Hello World!"
>
</TextView>
<Button
android:id="@ id/all_users"
android:layout_width="wrap_content"
android:layout_height="wrap_content"
android:text="ALL USERS"
>
</Button>
<ListView
android:id="@ id/marks"
android:layout_width="match_parent"
android:layout_height="wrap_content"
android:background="@color/teal_200"
>
</ListView>
</LinearLayout>
To cater for extract the marks for either a single user or for all users then the getMarks method in DatabaseHelper could be:-
public Cursor getMarks(Long userId) {
/* block ADDED to allow all or a single users marks to be accessed */
String whereclause = null; /* by default ALL marks */
String[] whereargs = null; /* by default no arg */
/* If userId passed then change whereclause and whereargs accordingly */
if (userId!=null) {
whereclause = COL_1 "=?";
whereargs = new String[]{String.valueOf(userId)};
}
String tblparm = TABLE_NAME3
" JOIN " TABLE_NAME " ON " COL_17 "=" COL_1
" JOIN " TABLE_NAME2 " ON " COL_18 "=" COL_14;
return this.getWritableDatabase().query(
tblparm,
/*<<<<<CHANGED 2nd parameter as Cursor Adapters MUST have an _id column, and should be the Primary key */
new String[]{"*", "id AS " BaseColumns._ID}, /*<<<<< NEW as Cursor Adapters MUST have _id column, and should be the Primary key */
/*CHANGED FROM null,*/ /* all columns */
/* Thus this will retrieve all columns and it will add a new column _id that is the same value as the id column */
whereclause, /*<<<<< CHANGED no WHERE clause */
whereargs, /*<<<<< CHANGED no where args */
null, /* no GROUP BY clause */
null, /* no HAVING clause */
COL_1 /* OREDR BY userId */
);
}
The majority of the changes are to MainActivity to incorporate the ListView to display the respective list and also for the demo the ALL USERS Button.
The new MainActivity class:-
public class MainActivity extends AppCompatActivity {
DatabaseHelper dbHelper;
Cursor csr; //<<<<< MOVED UP To HERE >>>>>//
ListView marks; //<<<<< NEW >>>>>>//
Button allUsers;
SimpleCursorAdapter marksSCA; //<<<<< NEW >>>>>//
Long currentUserId = null; /* assume no user so ALL users listed */
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
dbHelper=DatabaseHelper.getInstance(this);
marks = this.findViewById(R.id.marks); /* instantiate ListView for Marks */
allUsers = this.findViewById(R.id.all_users);
setupAllUsersButton(); /* setup the ALL Users button */
/* Of course you would not do this in the real App you would add the data via appropriate activities */
long c1Id = dbHelper.insertRegisterCourse(null,"C1");
long c2Id = dbHelper.insertRegisterCourse(null,"C2");
long c3Id = dbHelper.insertRegisterCourse(null,"C3");
long c4Id = dbHelper.insertRegisterCourse(null,"C4");
long c5Id = dbHelper.insertRegisterCourse(null,"C5");
long maryId = dbHelper.insertRegisterUser(null,"Mary","password");
long tomId = dbHelper.insertRegisterUser(null,"Tom","password");
long janeId = dbHelper.insertRegisterUser(null,"Jane","password");
dbHelper.insertregisterMarks(maryId,c1Id,9.56,7.2,3.4,6.5,5.6);
dbHelper.insertregisterMarks(maryId,c3Id,3.7,3.8,3.9,4.1,3.8);
dbHelper.insertregisterMarks(maryId,c5Id,5.2,5.6,6.3,4.2,1.4);
dbHelper.insertregisterMarks(tomId,c4Id,6.6,5.5,4.4,3.3,2.2);
dbHelper.insertregisterMarks(tomId,c2Id,0.0,0.0,0.0,0.0,0.0);
dbHelper.insertregisterMarks(janeId,c1Id,1.1,1.1,1.1,1.1,1.1);
dbHelper.insertregisterMarks(janeId,c2Id,2.2,2.2,2.2,2.2,2.2);
dbHelper.insertregisterMarks(janeId,c3Id,3.3,3.3,3.3,3.3,3.3);
dbHelper.insertregisterMarks(janeId,c4Id,4.4,4.4,4.4,4.4,4.4);
dbHelper.insertregisterMarks(janeId,c5Id,5.5,5.5,5.5,5.5,5.5);
/* setup the ListView assuming that user is Mary */
currentUserId = maryId;
setOrFreshMarksList();
// for all then set currentUserId = null;
/* to detect need to add courses for a user as the user has no courses then
you could have something like the following */
if (csr.getCount()< 1) {
/* call the activity to allow the user to select the course */
}
}
void setupAllUsersButton(){
/* just in case (should not be needed) */
if (allUsers==null) {
allUsers = this.findViewById(R.id.all_users);
}
allUsers.setOnClickListener(new View.OnClickListener() {
@Override
public void onClick(View view) {
currentUserId=null;
setOrFreshMarksList();
}
});
}
void setOrFreshMarksList() {
csr = dbHelper.getMarks(currentUserId); /* get the latest data from the database */
if (marksSCA==null) {
marksSCA = new SimpleCursorAdapter(
this, /* the context */
android.R.layout.simple_list_item_2, /* The layout, in this case a stock supplied layout for each listed item */
csr, /* the Source data for the adapter and thus used in the ListView */
/* The columns to display - NOTE associated with the views */
new String[]{
DatabaseHelper.COL_2,
DatabaseHelper.COL_12
},
/* the respective view ids into which the data is placed */
/* SO
COL_2 is placed into the view in the layout that has the id named text1, and
COL_12 (final exam marks) is placed into the view in the layout with id text2
(i.e. the two TextViews in simple_list_item_2)
You would probably use a custom layout, which could have more columns
*/
new int[]{
android.R.id.text1,
android.R.id.text2
},
0
);
marks.setAdapter(marksSCA);
} else {
/* If not the first time then just refresh the ListView */
marksSCA.swapCursor(csr);
}
}
/* If returning from another activity Then just in case the data has changed, refresh the List */
@Override
protected void onRestart() {
super.onRestart();
setOrFreshMarksList();
}
/* Not really needed as Cursor would likely get garbage collected BUT just in case close the cursor */
/* Cursor should ALWAYS be closed when done with */
@Override
protected void onDestroy() {
super.onDestroy();
csr.close();
}
}
Results
when run (only designed to be run once) then:-
- i.e. the Marks for the 3 courses Mary is in are displayed
- note that just the one mark FinalExam is displayed for simplicity, i.e. using the stock Simple_List_2
If the ALL USERS buttons is clicked then:-
i.e. now all 10 courses are listed
Please refer to the comments in the code, they should explain a fair bit
CodePudding user response：
/For a logged in user to update marks for his subject/
public long updateregisterMarks(long userId, long courseId, Double iaMark, Double firstTermMark, Double secondTermMark, Double finalExamMark, Double labMark) {
ContentValues cv = new ContentValues();
newValues.put(COL_9, iaMark);
newValues.put(COL_10,firstTermMark);
newValues.put(COL_11,secondTermMark);
newValues.put(COL_12,finalExamMark);
newValues.put(COL_13,labMark);
newValues.put(COL_17,userId);
newValues.put(COL_18,courseId);
****return db.update(
SQLITE_TABLE_NAME3,
newValues,
STUDENT_ID " = ?",
new String[] {Long.toString(studentId)}****
I am not sure of the return db.update part.