Our login and Register User interface are working. Login button on login screen has intent activity for Studentactivity which shoudl show his subjects and final marks on list view. If first time we want to use button register course. This registercourse button is connecting intent to register course activity. My question is can we use this single interface to input course and its corresponding different associated types of marks thought Course and Marks are two different tables. How we do this? How can we make Userid or Student iD of logged user automatically get into marks table while entering marks through this interface and also course id into marks table. UserID from registeruser table and Courseid from COURSETABLE ARE COMPOSITE KEYS INTO MARKS TABLE (This was suggested by some nice person to me). I am new to database and connecting its components to interface. With this project I want to learn about this. Please help me in this by suggesting some piece of code for .java of these activities. Also find my database helper code which is ready till now.
Our Database helper code:
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_4 = "course_ID";
public static final String COL_5 = "course";
public static final String TABLE_NAME3 = "registermarks";
public static final String COL_6 = "Marks_StudentID";
public static final String COL_7 = "Marks_CourseID";
public static final String COL_8 = "Unit1_Marks";
public static final String COL_9 = "Unit2_Marks";
public static final String COL_10 = "Unit3_Marks";
public static final String COL_11 = "Unit4_Marks";
public static final String COL_12 = "Unit5_Marks";
public static final String COL_13 = "Unit6_Marks";
public static final String COL_14 = "IA_Marks";
public static final String COL_15 = "Average_Marks";
public static final String COL_16 = "IBGrade";
public static final String COl_17 = "PredictedGrade";
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, course TEXT)");
sqLiteDatabase.execSQL("CREATE TABLE registermarks (Unit1_Marks DOUBLE, " +
"Unit2_Marks DOUBLE, " +
"Unit3_Marks DOUBLE, " +
"Unit4_Marks DOUBLE, " +
"Unit5_Marks DOUBLE, " +
"Unit6_Marks DOUBLE, " +
"IA_Marks INTEGER, " +
"Average_Marks INTEGER, " +
"IBGrade INTEGER, " +
"PredictedGrade INTEGER, " +
"CONSTRAINT fk_registeruser FOREIGN KEY (" + COL_6 + ")" +
" REFERENCES " + TABLE_NAME + "(" + COL_1 + "), " +
"CONSTRAINT fk_registercourse FOREIGN KEY (" + COL_7 + ")" +
" REFERENCES " + TABLE_NAME2 + "(" + COL_4 + ")" +
")");
}
@Override
public void onUpgrade(SQLiteDatabase sqLiteDatabase, int i, int i1) {
sqLiteDatabase.execSQL(" DROP TABLE IF EXISTS " + TABLE_NAME);
sqLiteDatabase.execSQL(" DROP TABLE IF EXISTS " + TABLE_NAME2);
sqLiteDatabase.execSQL(" DROP TABLE IF EXISTS " + TABLE_NAME3);
onCreate(sqLiteDatabase);
}
public long addUser (String user, String password){
SQLiteDatabase db = this.getWritableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put("username", user);
contentValues.put("password", password);
long res = db.insert("registeruser", null, contentValues);
db.close();
return res;
}
public long addCourse (Long course_ID, String course){
ContentValues cv = new ContentValues();
if (course_ID != null) cv.put(COL_4, course_ID);
cv.put(COL_5, course);
return this.getWritableDatabase().insert(TABLE_NAME2, null, cv);
}
public long addMarks (long Marks_StudentID, long Marks_CourseID, Double Unit1_Marks,
Double Unit2_Marks,
Double Unit3_Marks,
Double Unit4_Marks,
Double Unit5_Marks,
Double Unit6_Marks,
Integer IA_Marks,
Integer Average_Marks,
Integer IBGrade,
Integer PredictedGrade){
ContentValues cv = new ContentValues();
cv.put(COL_8, Unit1_Marks);
cv.put(COL_9, Unit2_Marks);
cv.put(COL_10, Unit3_Marks);
cv.put(COL_11, Unit4_Marks);
cv.put(COL_12, Unit5_Marks);
cv.put(COL_13, Unit6_Marks);
cv.put(COL_14, IA_Marks);
cv.put(COL_15, Average_Marks);
cv.put(COL_16, IBGrade);
cv.put(COl_17, PredictedGrade);
cv.put(COL_6, Marks_StudentID);
cv.put(COL_7, Marks_CourseID);
return this.getWritableDatabase().insert(TABLE_NAME3, null, cv);
}
public boolean checkUser(String username, String password){
String[] columns = {COL_1};
SQLiteDatabase db = getReadableDatabase();
String selection = COL_2 + "=?" + " and " + COL_3 + "=?";
String[] selectionArgs = {username, password};
Cursor cursor = db.query(TABLE_NAME, columns, selection, selectionArgs, null, null, null);
int count = cursor.getCount();
cursor.close();
db.close();
if (count > 0)
return true;
else
return false;
}
public Cursor getMarks (Long ID){
String whereclause = null;
String[] whereargs = null;
if (ID != null){
whereclause = COL_1 + "=?";
whereargs = new String[]{String.valueOf(ID)};
}
String tblparm = TABLE_NAME3 +
" JOIN " + TABLE_NAME + " ON " + COL_6 + "=" + COL_1 +
" JOIN " + TABLE_NAME2 + " ON " + COL_7 + "=" + COL_4;
return this.getWritableDatabase().query(tblparm,
new String[]{"*", "id AS " + BaseColumns._ID},
whereclause,
whereargs,
null,
null,
COL_1
);
}
}