How To Update/delete With Elements From Two Different Tables Sqlite

I am working on a student grade submission program that accepts the following inputs: Student ID, Student First Name, Student Last Name, Class ID, Class Name, Grade Points, and Let

Solution 1:

You should never need to update multiple tables at once at least for a single change e.g. student's name has changed (student table would be changed) or say a grade changed (change the student's respective grade entry).

So I believe what would best suit are methods to update a Student's details (first or last name or both) and a method to alter the grades (again either or both).

As for deletion you could do this sequentially delete from the grades table and the from the student table or vice-versa.

As such I believe the following code includes methods deleteStudentInfo, changeStudentName, changeStudentFirstName, changeStudentLastName and changeStudentGrade and also includes example usage along with creating and populating the tables (you may wish to consider the revised schema):-

publicclassMainActivityextendsAppCompatActivity {

    publicstaticfinalStringDBNAME="study";
    publicstaticfinalStringSTUDENT_TABLE_NAME="STUDENT_TABLE";
    publicstaticfinalStringCOL_STUDENT_ID="studentid";
    publicstaticfinalStringCOL_STUDENT_FIRSTNAME="fname";
    publicstaticfinalStringCOL_STUDENT_LASTNAME="lname";

    publicstaticfinalStringCLASS_TABLE_NAME="CLASS_TABLE";
    publicstaticfinalStringCOL_CLASS_ID="classid";
    publicstaticfinalStringCOL_CLASS_NAME="classname";

    publicstaticfinalStringGRADE_TABLE_NAME="GRADE_TABLE";
    publicstaticfinalStringCOL_GRADE_POINTGRADE="pointgrade";
    publicstaticfinalStringCOL_GRADE_LETTERGRADE="lettergrade";
    publicstaticfinalStringBY_STUDENTID= COL_STUDENT_ID + "=?";
    publicstaticfinalStringBY_CLASSID= COL_CLASS_ID + "=?";



    @OverrideprotectedvoidonCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        SQLiteDatabasedb= openOrCreateDatabase(DBNAME,Context.MODE_PRIVATE,null);
        db.execSQL("CREATE TABLE IF NOT EXISTS " + STUDENT_TABLE_NAME + " (" +
                COL_STUDENT_ID + " TEXT PRIMARY KEY, " +
                COL_STUDENT_FIRSTNAME + " TEXT," +
                COL_STUDENT_LASTNAME + " TEXT)"
        );
        db.execSQL("CREATE TABLE IF NOT EXISTS " + CLASS_TABLE_NAME + "(" +
                 COL_CLASS_ID + " TEXT PRIMARY KEY," +
                 COL_CLASS_NAME + " TEXT UNIQUE " +
                ")"
        );
        db.execSQL("CREATE TABLE IF NOT EXISTS " + GRADE_TABLE_NAME + "(" +
                COL_STUDENT_ID + " TEXT, " +
                COL_CLASS_ID + " TEXT, " +
                COL_GRADE_POINTGRADE + " INTEGER, " +
                COL_GRADE_LETTERGRADE + " TEXT" +
                ")"
        );
        db.execSQL("INSERT OR IGNORE INTO " + STUDENT_TABLE_NAME +
                " VALUES" +
                "('00001','Fred','Smith')," +
                "('00010','Mary','Thomas')," +
                "('00910','Angela','Jones')"
        );
        db.execSQL("INSERT OR IGNORE INTO " + CLASS_TABLE_NAME +
                " VALUES" +
                "('001','English')," +
                "('101','Mathematics')," +
                "('201','Chemistry')"
        );
        db.execSQL("INSERT OR IGNORE INTO " + GRADE_TABLE_NAME +
                " VALUES" +
                "    ('00001','001',99,'A'), -- Fred Smith has 99 point grade as an A in English\n" +
                "    ('00001','101',25,'F'), -- Fred Smith has 25 point grade as an F on Mathematics\n" +
                "    ('00010','201',76,'B'), -- Angela Jones 76 a B in Chemistry\n" +
                "    ('00910','101',50,'C'), \n" +
                "    ('00910','201',63,'C'),\n" +
                "    ('00910','001',89,'A')\n" +
                ";"
        );

        changeStudentName(db,"00001","Joe","Bloggs");
        changeStudentFirstName(db,"00001","Harry");
        changeStudentLastName(db,"00001","Hoffmann");
        // e.g. won't change due to -1 (skip pointsgrade) and null (skip lettergrade)
        changeStudentGrade(db,"00001","001",-1,null);
        // Change both
        changeStudentGrade(db,"00001","001",25,"D");
        changeStudentGrade(db,"00001","001",27,null);

        // Ooops invalid student idif (deleteStudentInfo(db,"001")) {
            Log.d("DELETION","Student 001 deleted.");
        } else {
            Log.d("DELETION","Ooops Student 001 not deleted?????");
        }

        // Corrected Student IDif (deleteStudentInfo(db,"00001")) {
            Log.d("DELETION","Student 001 deleted.");
        } else {
            Log.d("DELETION","Ooops Student 001 not deleted?????");
        }
    }

    privatebooleandeleteStudentInfo(SQLiteDatabase db, String studentid) {

        Stringtag="STUDENT_DELETE";
        Stringstudent_table="STUDENT_TABLE";
        Stringgrade_table="GRADE_TABLE";

        longpre_delete_student_count= DatabaseUtils.queryNumEntries(db,student_table);
        longpre_delete_grade_count= DatabaseUtils.queryNumEntries(db,grade_table);

        Stringwhereclause="studentid =?";
        String[] whereargs = {studentid};

        db.delete(student_table,whereclause,whereargs);
        db.delete(grade_table,whereclause,whereargs);

        longpost_delete_student_count= DatabaseUtils.queryNumEntries(db,student_table);
        longpost_delete_grade_count= DatabaseUtils.queryNumEntries(db,grade_table);

        Log.d(
                tag,
                "Number of Students deleted from " +
                        student_table + " is " +
                        String.valueOf(
                                pre_delete_student_count - post_delete_student_count
                        ));
        Log.d(
                tag,
                "Number of Grades deleted from " + grade_table + " is " +
                        String.valueOf(
                                pre_delete_grade_count - post_delete_grade_count
                        )
        );
        if ((pre_delete_student_count + pre_delete_grade_count) != (post_delete_student_count + post_delete_grade_count)) {
            returntrue;
        }
        returnfalse;
    }

    /**
     * Flexible Student Name Change
     *
     * @param db            The SQliteDatabase
     * @param studentid     The studentid (String)
     * @param newfirstname  The new firstname, null or blank to leave as is
     * @param newlastname   the new lastname, null or blank to leave as is
     */privatevoidchangeStudentName(SQLiteDatabase db, String studentid, String newfirstname, String newlastname ) {

        //Anything to do? if not do nothingif ((newfirstname == null || newfirstname.length() < 1) && (newlastname == null || newlastname.length() < 1)) {
            return;
        }
        ContentValuescv=newContentValues();
        if (newfirstname != null && newfirstname.length() > 0) {
            cv.put(COL_STUDENT_FIRSTNAME,newfirstname);
        }
        if (newlastname != null && newlastname.length() > 0) {
            cv.put(COL_STUDENT_LASTNAME,newlastname);
        }
        // Overcautious checkif (cv.size() < 1) {
            return;
        }
        db.update(STUDENT_TABLE_NAME,cv,BY_STUDENTID,newString[]{studentid});
    }

    /**
     * Change a Student's First Name (invokes changeStudentName method)
     * @param db            The SQLiteDatabase
     * @param studentid     The student's id (String)
     * @param newfirstname  The new first name to apply
     */privatevoidchangeStudentFirstName(SQLiteDatabase db, String studentid, String newfirstname) {
        changeStudentName(db,studentid,newfirstname,null);
    }

    /**
     * Change a Student's Last Name (invokes changeStudentName method)
     * @param db
     * @param studentid
     * @param newlastname
     */privatevoidchangeStudentLastName(SQLiteDatabase db, String studentid, String newlastname) {
        changeStudentName(db,studentid,null,newlastname);
    }

    /**
     * Change a students grade (allowing just one (points/letter))
     * @param db
     * @param studentid
     * @param classid
     * @param newpointsgrade
     * @param newlettergrade
     */privatevoidchangeStudentGrade(SQLiteDatabase db, String studentid, String classid, int newpointsgrade, String newlettergrade) {
        // Anything to do? if not do nothingif (newpointsgrade < 0 && (newlettergrade == null || newlettergrade.length() < 1)) {
            return;
        }
        ContentValuescv=newContentValues();
        if (newpointsgrade >= 0) {
            cv.put(COL_GRADE_POINTGRADE,newpointsgrade);
        }
        if (newlettergrade != null && newlettergrade.length() > 0) {
            cv.put(COL_GRADE_LETTERGRADE,newlettergrade);
        }
        Stringwhereclause= COL_STUDENT_ID + "=? AND " + COL_CLASS_ID + "=?";
        String[] whereargs = newString[]{studentid,classid};
        db.update(GRADE_TABLE_NAME,cv,whereclause,whereargs);
    }
}