public class DatabaseHelper extends SQLiteOpenHelper {
public static final String DATABASE_NAME = "register.db";
public static final String TABLE_NAME = "registeruser";
public static final String COL_1 = "ID";
public static final String COL_2 = "username";
public static final String COL_3 = "password";
public static final String TABLE_NAME2 = "registercourse";
public static final String COL_14 = "Course_ID";
public static final String COL_15 = "Course_StudentID";
public static final String COL_4 = "course1";
public static final String COL_5 = "course2";
public static final String COL_6 = "course3";
public static final String COL_7 = "course4";
public static final String COL_8 = "course5";
public static final String TABLE_NAME3 = "registermarks";
public static final String COL_16 = "Marks_ID";
public static final String COL_17 = "Marks_StudentID";
public static final String COL_18 = "Marks_CourseID";
public static final String COL_9 = "IA_Marks";
public static final String COL_10 = "First_Term_Marks";
public static final String COL_11 = "Second_Term_Marks";
public static final String COL_12 = "Final_Exam_Marks";
public static final String COL_13 = "Lab_Marks";
public DatabaseHelper(Context context){
super(context, DATABASE_NAME, null, 1);
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
sqLiteDatabase.execSQL("CREATE TABLE registeruser ("
"ID INTEGER PRIMARY KEY AUTOINCREMENT, "
"username TEXT, "
"password TEXT"
")");
sqLiteDatabase.execSQL("CREATE TABLE registercourse ("
"Course_ID INTEGER PRIMARY KEY AUTOINCREMENT, "
"course1 TEXT, "
"course2 TEXT, "
"course3 TEXT, "
"course4 TEXT, "
"course5 TEXT, "
"CONSTRAINT fk_registeruser FOREIGN KEY (" COL_15 ")"
" REFERENCES " TABLE_NAME "(" COL_1 ")"
");");
sqLiteDatabase.execSQL("CREATE TABLE registermarks ("
"Marks_ID INTEGER PRIMARY KEY AUTOINCREMENT, "
"IA_Marks REAL, "
"First_Term_Marks REAL, "
"Second_Term_Marks REAL, "
"Final_Exam_Marks REAL, "
"Lab_Marks REAL, "
"CONSTRAINT fk_registeruser FOREIGN KEY (" COL_17 ")"
" REFERENCES " TABLE_NAME "(" COL_1 "), "
"CONSTRAINT fk_registercourse FOREIGN KEY (" COL_18 ")"
" REFERENCES " TABLE_NAME2 "(" COL_14 ")"
");");
For a student marks calculator Android app each student has around 5 subjects and each subject 5 categories of marks. I am reading about RAWQUERY and JOIN. How to fetch a student's subjects and corresponding marks categories?
CodePudding user response：
Perhaps consider this Demo.
The modified DatabaseHelper class with some methods to insert data and a method to extract all of the inserted data combining the marks, with the user and the course (the use of JOINS).
- Note the modifications
- a course per row rather than 5 courses all in 1
- ALL use of table entities (names) use the constants at all time
- courses aren't children of user (see example for how this works)
- other changes as typically commented
Instead of rawQuery the convenience query method has been used see the getMarks method.
DatabaseHelper
public class DatabaseHelper extends SQLiteOpenHelper {
public static final String DATABASE_NAME = "register.db";
public static final String TABLE_NAME = "registeruser";
public static final String COL_1 = "ID";
public static final String COL_2 = "username";
public static final String COL_3 = "password";
public static final String TABLE_NAME2 = "registercourse";
public static final String COL_14 = "Course_ID";
//public static final String COL_15 = "Course_StudentID";
public static final String COL_4 = "course1";
//public static final String COL_5 = "course2";
//public static final String COL_6 = "course3";
//public static final String COL_7 = "course4";
//public static final String COL_8 = "course5";
public static final String TABLE_NAME3 = "registermarks";
public static final String COL_16 = "Marks_ID";
public static final String COL_17 = "Marks_StudentID";
public static final String COL_18 = "Marks_CourseID";
public static final String COL_9 = "IA_Marks";
public static final String COL_10 = "First_Term_Marks";
public static final String COL_11 = "Second_Term_Marks";
public static final String COL_12 = "Final_Exam_Marks";
public static final String COL_13 = "Lab_Marks";
private DatabaseHelper(Context context) {
super(context, DATABASE_NAME, null, 1);
}
private static volatile DatabaseHelper INSTANCE;
public static DatabaseHelper getInstance(Context context) {
if(INSTANCE==null) INSTANCE=new DatabaseHelper(context);
return INSTANCE;
}
@Override
public void onCreate(SQLiteDatabase sqLiteDatabase) {
sqLiteDatabase.execSQL("CREATE TABLE registeruser ("
COL_1 " INTEGER PRIMARY KEY /*AUTOINCREMENT*/, "
COL_2 " TEXT, "
COL_3 " TEXT"
")");
sqLiteDatabase.execSQL("CREATE TABLE registercourse ("
COL_14 " INTEGER PRIMARY KEY /*AUTOINCREMENT*/, "
COL_4 " TEXT/*,*/ "
//COL_5 " TEXT, "
//COL_6 " TEXT, "
//COL_7 " TEXT, "
//COL_8 " TEXT, "
//COL_15 " INTEGER, "
/*" CONSTRAINT fk_registeruser FOREIGN KEY (" COL_15 ")"
" REFERENCES " TABLE_NAME "(" COL_1 ")"
*/
");");
sqLiteDatabase.execSQL("CREATE TABLE registermarks ("
//COL_16 " INTEGER PRIMARY KEY /*AUTOINCREMENT*/, "
COL_9 " REAL, "
COL_10 " REAL, "
COL_11 " REAL, "
COL_12 " REAL, "
COL_13 " REAL, "
COL_17 " INTEGER "
" REFERENCES " TABLE_NAME "(" COL_1 "), "
COL_18 " INTEGER "
" REFERENCES " TABLE_NAME2 "(" COL_14 "),"
" PRIMARY KEY(" COL_17 "," COL_18 ")"
");");
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
}
public long insertRegisterUser(Long id, String username, String password) {
ContentValues cv = new ContentValues();
if (id!=null) {
cv.put(COL_1,id);
}
cv.put(COL_2,username);
cv.put(COL_3,password);
return this.getWritableDatabase().insert(TABLE_NAME,null,cv);
}
public long insertRegisterCourse(Long id, String course1/*,String course2, String course3, String course4, String course5, long userId*/) {
ContentValues cv = new ContentValues();
if (id!=null) cv.put(COL_14,id);
cv.put(COL_4,course1);
return this.getWritableDatabase().insert(TABLE_NAME2,null,cv);
}
public long insertregisterMarks(long userId, long courseId,/*Long id,*/Double iaMark, Double firstTermMark, Double secondTermMark, Double finalExamMark, Double labMark) {
ContentValues cv = new ContentValues();
//if (id!=null) cv.put(COL_16,id);
cv.put(COL_9, iaMark);
cv.put(COL_10,firstTermMark);
cv.put(COL_11,secondTermMark);
cv.put(COL_12,finalExamMark);
cv.put(COL_13,labMark);
cv.put(COL_17,userId);
cv.put(COL_18,courseId);
return this.getWritableDatabase().insert(TABLE_NAME3,null,cv);
}
public Cursor getMarks() {
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,
null, /* all columns */
null, /* no WHERE clause */
null, /* no where args */
null, /* no GROUP BY clause */
null, /* no HAVING clause */
COL_1 /* OREDR BY userId */
);
}
}
and as a demo MainActivity (only designed to be run once as a DEMO)
public class MainActivity extends AppCompatActivity {
DatabaseHelper dbHelper;
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
dbHelper=DatabaseHelper.getInstance(this);
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);
Cursor csr = dbHelper.getMarks();
DatabaseUtils.dumpCursor(csr);
csr.close();
}
}
Results
2022-09-13 15:03:53.854 I/System.out: >>>>> Dumping cursor android.database.sqlite.SQLiteCursor@cf2ef01
2022-09-13 15:03:53.854 I/System.out: 0 {
2022-09-13 15:03:53.854 I/System.out: IA_Marks=9.56
2022-09-13 15:03:53.854 I/System.out: First_Term_Marks=7.2
2022-09-13 15:03:53.855 I/System.out: Second_Term_Marks=3.4
2022-09-13 15:03:53.855 I/System.out: Final_Exam_Marks=6.5
2022-09-13 15:03:53.855 I/System.out: Lab_Marks=5.6
2022-09-13 15:03:53.855 I/System.out: Marks_StudentID=1
2022-09-13 15:03:53.855 I/System.out: Marks_CourseID=1
2022-09-13 15:03:53.855 I/System.out: ID=1
2022-09-13 15:03:53.855 I/System.out: username=Mary
2022-09-13 15:03:53.855 I/System.out: password=password
2022-09-13 15:03:53.855 I/System.out: Course_ID=1
2022-09-13 15:03:53.855 I/System.out: course1=C1
2022-09-13 15:03:53.855 I/System.out: }
2022-09-13 15:03:53.855 I/System.out: 1 {
2022-09-13 15:03:53.855 I/System.out: IA_Marks=3.7
2022-09-13 15:03:53.855 I/System.out: First_Term_Marks=3.8
2022-09-13 15:03:53.855 I/System.out: Second_Term_Marks=3.9
2022-09-13 15:03:53.856 I/System.out: Final_Exam_Marks=4.1
2022-09-13 15:03:53.856 I/System.out: Lab_Marks=3.8
2022-09-13 15:03:53.856 I/System.out: Marks_StudentID=1
2022-09-13 15:03:53.856 I/System.out: Marks_CourseID=3
2022-09-13 15:03:53.856 I/System.out: ID=1
2022-09-13 15:03:53.856 I/System.out: username=Mary
2022-09-13 15:03:53.856 I/System.out: password=password
2022-09-13 15:03:53.856 I/System.out: Course_ID=3
2022-09-13 15:03:53.856 I/System.out: course1=C3
2022-09-13 15:03:53.856 I/System.out: }
2022-09-13 15:03:53.857 I/System.out: 2 {
2022-09-13 15:03:53.857 I/System.out: IA_Marks=5.2
2022-09-13 15:03:53.857 I/System.out: First_Term_Marks=5.6
2022-09-13 15:03:53.857 I/System.out: Second_Term_Marks=6.3
2022-09-13 15:03:53.857 I/System.out: Final_Exam_Marks=4.2
2022-09-13 15:03:53.857 I/System.out: Lab_Marks=1.4
2022-09-13 15:03:53.857 I/System.out: Marks_StudentID=1
2022-09-13 15:03:53.857 I/System.out: Marks_CourseID=5
2022-09-13 15:03:53.857 I/System.out: ID=1
2022-09-13 15:03:53.857 I/System.out: username=Mary
2022-09-13 15:03:53.857 I/System.out: password=password
2022-09-13 15:03:53.857 I/System.out: Course_ID=5
2022-09-13 15:03:53.857 I/System.out: course1=C5
2022-09-13 15:03:53.857 I/System.out: }
2022-09-13 15:03:53.857 I/System.out: 3 {
2022-09-13 15:03:53.858 I/System.out: IA_Marks=6.6
2022-09-13 15:03:53.858 I/System.out: First_Term_Marks=5.5
2022-09-13 15:03:53.858 I/System.out: Second_Term_Marks=4.4
2022-09-13 15:03:53.858 I/System.out: Final_Exam_Marks=3.3
2022-09-13 15:03:53.858 I/System.out: Lab_Marks=2.2
2022-09-13 15:03:53.858 I/System.out: Marks_StudentID=2
2022-09-13 15:03:53.858 I/System.out: Marks_CourseID=4
2022-09-13 15:03:53.858 I/System.out: ID=2
2022-09-13 15:03:53.858 I/System.out: username=Tom
2022-09-13 15:03:53.858 I/System.out: password=password
2022-09-13 15:03:53.858 I/System.out: Course_ID=4
2022-09-13 15:03:53.859 I/System.out: course1=C4
2022-09-13 15:03:53.859 I/System.out: }
2022-09-13 15:03:53.859 I/System.out: 4 {
2022-09-13 15:03:53.859 I/System.out: IA_Marks=0
2022-09-13 15:03:53.859 I/System.out: First_Term_Marks=0
2022-09-13 15:03:53.859 I/System.out: Second_Term_Marks=0
2022-09-13 15:03:53.859 I/System.out: Final_Exam_Marks=0
2022-09-13 15:03:53.859 I/System.out: Lab_Marks=0
2022-09-13 15:03:53.859 I/System.out: Marks_StudentID=2
2022-09-13 15:03:53.859 I/System.out: Marks_CourseID=2
2022-09-13 15:03:53.860 I/System.out: ID=2
2022-09-13 15:03:53.860 I/System.out: username=Tom
2022-09-13 15:03:53.860 I/System.out: password=password
2022-09-13 15:03:53.860 I/System.out: Course_ID=2
2022-09-13 15:03:53.860 I/System.out: course1=C2
2022-09-13 15:03:53.860 I/System.out: }
2022-09-13 15:03:53.860 I/System.out: 5 {
2022-09-13 15:03:53.860 I/System.out: IA_Marks=1.1
2022-09-13 15:03:53.860 I/System.out: First_Term_Marks=1.1
2022-09-13 15:03:53.860 I/System.out: Second_Term_Marks=1.1
2022-09-13 15:03:53.860 I/System.out: Final_Exam_Marks=1.1
2022-09-13 15:03:53.860 I/System.out: Lab_Marks=1.1
2022-09-13 15:03:53.861 I/System.out: Marks_StudentID=3
2022-09-13 15:03:53.861 I/System.out: Marks_CourseID=1
2022-09-13 15:03:53.861 I/System.out: ID=3
2022-09-13 15:03:53.861 I/System.out: username=Jane
2022-09-13 15:03:53.861 I/System.out: password=password
2022-09-13 15:03:53.861 I/System.out: Course_ID=1
2022-09-13 15:03:53.861 I/System.out: course1=C1
2022-09-13 15:03:53.861 I/System.out: }
2022-09-13 15:03:53.861 I/System.out: 6 {
2022-09-13 15:03:53.861 I/System.out: IA_Marks=2.2
2022-09-13 15:03:53.861 I/System.out: First_Term_Marks=2.2
2022-09-13 15:03:53.861 I/System.out: Second_Term_Marks=2.2
2022-09-13 15:03:53.861 I/System.out: Final_Exam_Marks=2.2
2022-09-13 15:03:53.861 I/System.out: Lab_Marks=2.2
2022-09-13 15:03:53.861 I/System.out: Marks_StudentID=3
2022-09-13 15:03:53.861 I/System.out: Marks_CourseID=2
2022-09-13 15:03:53.862 I/System.out: ID=3
2022-09-13 15:03:53.862 I/System.out: username=Jane
2022-09-13 15:03:53.862 I/System.out: password=password
2022-09-13 15:03:53.862 I/System.out: Course_ID=2
2022-09-13 15:03:53.862 I/System.out: course1=C2
2022-09-13 15:03:53.862 I/System.out: }
2022-09-13 15:03:53.862 I/System.out: 7 {
2022-09-13 15:03:53.862 I/System.out: IA_Marks=3.3
2022-09-13 15:03:53.862 I/System.out: First_Term_Marks=3.3
2022-09-13 15:03:53.862 I/System.out: Second_Term_Marks=3.3
2022-09-13 15:03:53.862 I/System.out: Final_Exam_Marks=3.3
2022-09-13 15:03:53.862 I/System.out: Lab_Marks=3.3
2022-09-13 15:03:53.862 I/System.out: Marks_StudentID=3
2022-09-13 15:03:53.862 I/System.out: Marks_CourseID=3
2022-09-13 15:03:53.862 I/System.out: ID=3
2022-09-13 15:03:53.862 I/System.out: username=Jane
2022-09-13 15:03:53.862 I/System.out: password=password
2022-09-13 15:03:53.862 I/System.out: Course_ID=3
2022-09-13 15:03:53.862 I/System.out: course1=C3
2022-09-13 15:03:53.862 I/System.out: }
2022-09-13 15:03:53.862 I/System.out: 8 {
2022-09-13 15:03:53.862 I/System.out: IA_Marks=4.4
2022-09-13 15:03:53.862 I/System.out: First_Term_Marks=4.4
2022-09-13 15:03:53.862 I/System.out: Second_Term_Marks=4.4
2022-09-13 15:03:53.863 I/System.out: Final_Exam_Marks=4.4
2022-09-13 15:03:53.863 I/System.out: Lab_Marks=4.4
2022-09-13 15:03:53.863 I/System.out: Marks_StudentID=3
2022-09-13 15:03:53.863 I/System.out: Marks_CourseID=4
2022-09-13 15:03:53.863 I/System.out: ID=3
2022-09-13 15:03:53.863 I/System.out: username=Jane
2022-09-13 15:03:53.863 I/System.out: password=password
2022-09-13 15:03:53.863 I/System.out: Course_ID=4
2022-09-13 15:03:53.863 I/System.out: course1=C4
2022-09-13 15:03:53.863 I/System.out: }
2022-09-13 15:03:53.863 I/System.out: 9 {
2022-09-13 15:03:53.863 I/System.out: IA_Marks=5.5
2022-09-13 15:03:53.863 I/System.out: First_Term_Marks=5.5
2022-09-13 15:03:53.863 I/System.out: Second_Term_Marks=5.5
2022-09-13 15:03:53.863 I/System.out: Final_Exam_Marks=5.5
2022-09-13 15:03:53.863 I/System.out: Lab_Marks=5.5
2022-09-13 15:03:53.863 I/System.out: Marks_StudentID=3
2022-09-13 15:03:53.863 I/System.out: Marks_CourseID=5
2022-09-13 15:03:53.863 I/System.out: ID=3
2022-09-13 15:03:53.863 I/System.out: username=Jane
2022-09-13 15:03:53.863 I/System.out: password=password
2022-09-13 15:03:53.863 I/System.out: Course_ID=5
2022-09-13 15:03:53.863 I/System.out: course1=C5
2022-09-13 15:03:53.863 I/System.out: }
2022-09-13 15:03:53.863 I/System.out: <<<<<
As can be seen the permutations of user and course are all output. That is:-
- Mary has Marks for 3 courses (c1, C3 and C5)
- Tom has marks for 2 courses (C4 and C2)
- Jane has marks for all 5 courses
i.e. the marks indicates the course being undertaken.
Please note the changes made to the helper
using App Inspection in Android Studio the database is as follows:-
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.