如何使用两个不同表中的元素更新/删除SQLite [英] How to Update/Delete with elements from two different tables SQLite
问题描述
我正在研究一个学生成绩提交程序，该程序接受以下输入:学生ID，学生名字，学生姓氏，班级ID，班级名称，成绩点和字母成绩.为了防止数据冗余，我创建了三个不同的表.但是，当尝试修改"或删除"具有来自不同表的两个值的条目时，我遇到了问题. (例如，删除WHERE student.id等于STUDENT_TABLE中的student.id，而WHERE class.id等于CLASS_TABLE中的class.id)
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 Letter Grade. In order to prevent data redundancy I have created three different tables. However when attempting to "modify" or "delete" an entry with two values from distinct tables I am running into a problem. (For instance delete WHERE student.id is equal to the student.id in STUDENT_TABLE and WHERE class.id is equal to the class.id in the CLASS_TABLE)
话虽如此，这是我当前用于修改学生班级的修改代码.我需要进行修改，以将student.id和class.id关联在一起(位于两个不同的表中)
With that being said, here is my current Modify code for Modifying a Student Class. I need to modify in a way in which the student.id and class.id are associated (Which are Located in two different Tables)
modifyclass.setOnClickListener(new OnClickListener() {
@Override
public void onClick(View v) {
// TODO Auto-generated method stub
if(studentid.getText().toString().trim().length()==0 || classid.getText().toString().trim().length()==0 || classname.getText().toString().trim().length()==0)
{
showMessage("Error", "Please enter Student & Class ID to update class. \n\nAll other Field Entries will be ignored");
return;
}
Cursor c=db.rawQuery("SELECT * FROM CLASS_TABLE WHERE classid='"+classid.getText()+"'", null);
if(c.moveToFirst())
{
db.execSQL("UPDATE CLASS_TABLE SET classid='"+classid.getText()+"',classname='"+classname.getText()+"' WHERE studentid='"+studentid.getText()+"' AND classid='"+classid.getText()+"'");
showMessage("Success", "Class Record Modified");
}
else
{
showMessage("Error", "Invalid First and Last name or Class ID");
}
clearText();
}
});
这是我的表格供参考:
db=openOrCreateDatabase("STUDENTGRADES", Context.MODE_PRIVATE, null);
db.execSQL("CREATE TABLE IF NOT EXISTS STUDENT_TABLE (studentid TEXT, fname TEXT, lname TEXT)");
db.execSQL("CREATE TABLE IF NOT EXISTS CLASS_TABLE(classid TEXT PRIMARY KEY, classname TEXT UNIQUE)");
db.execSQL("CREATE TABLE IF NOT EXISTS GRADE_TABLE (studentid TEXT, classid TEXT, pointgrade INTEGER, lettergrade TEXT)");
推荐答案
您永远不需要至少一次更新多个表，例如一次更改.学生的姓名已更改(学生表将更改)或说成绩已更改(更改学生各自的成绩条目).
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.
因此，我相信以下代码包含方法deleteStudentInfo，changeStudentName，changeStudentFirstName，changeStudentLastName和changeStudentGrade，并且还包括示例用法以及创建和填充表(您可能希望考虑使用修订的架构):-
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):-
public class MainActivity extends AppCompatActivity {
public static final String DBNAME = "study";
public static final String STUDENT_TABLE_NAME = "STUDENT_TABLE";
public static final String COL_STUDENT_ID = "studentid";
public static final String COL_STUDENT_FIRSTNAME = "fname";
public static final String COL_STUDENT_LASTNAME = "lname";
public static final String CLASS_TABLE_NAME = "CLASS_TABLE";
public static final String COL_CLASS_ID = "classid";
public static final String COL_CLASS_NAME = "classname";
public static final String GRADE_TABLE_NAME = "GRADE_TABLE";
public static final String COL_GRADE_POINTGRADE = "pointgrade";
public static final String COL_GRADE_LETTERGRADE = "lettergrade";
public static final String BY_STUDENTID = COL_STUDENT_ID + "=?";
public static final String BY_CLASSID = COL_CLASS_ID + "=?";
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
SQLiteDatabase db = 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 id
if (deleteStudentInfo(db,"001")) {
Log.d("DELETION","Student 001 deleted.");
} else {
Log.d("DELETION","Ooops Student 001 not deleted?????");
}
// Corrected Student ID
if (deleteStudentInfo(db,"00001")) {
Log.d("DELETION","Student 001 deleted.");
} else {
Log.d("DELETION","Ooops Student 001 not deleted?????");
}
}
private boolean deleteStudentInfo(SQLiteDatabase db, String studentid) {
String tag = "STUDENT_DELETE";
String student_table = "STUDENT_TABLE";
String grade_table = "GRADE_TABLE";
long pre_delete_student_count = DatabaseUtils.queryNumEntries(db,student_table);
long pre_delete_grade_count = DatabaseUtils.queryNumEntries(db,grade_table);
String whereclause = "studentid =?";
String[] whereargs = {studentid};
db.delete(student_table,whereclause,whereargs);
db.delete(grade_table,whereclause,whereargs);
long post_delete_student_count = DatabaseUtils.queryNumEntries(db,student_table);
long post_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)) {
return true;
}
return false;
}
/**
* 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
*/
private void changeStudentName(SQLiteDatabase db, String studentid, String newfirstname, String newlastname ) {
//Anything to do? if not do nothing
if ((newfirstname == null || newfirstname.length() < 1) && (newlastname == null || newlastname.length() < 1)) {
return;
}
ContentValues cv = new ContentValues();
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 check
if (cv.size() < 1) {
return;
}
db.update(STUDENT_TABLE_NAME,cv,BY_STUDENTID,new String[]{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
*/
private void changeStudentFirstName(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
*/
private void changeStudentLastName(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
*/
private void changeStudentGrade(SQLiteDatabase db, String studentid, String classid, int newpointsgrade, String newlettergrade) {
// Anything to do? if not do nothing
if (newpointsgrade < 0 && (newlettergrade == null || newlettergrade.length() < 1)) {
return;
}
ContentValues cv = new ContentValues();
if (newpointsgrade >= 0) {
cv.put(COL_GRADE_POINTGRADE,newpointsgrade);
}
if (newlettergrade != null && newlettergrade.length() > 0) {
cv.put(COL_GRADE_LETTERGRADE,newlettergrade);
}
String whereclause = COL_STUDENT_ID + "=? AND " + COL_CLASS_ID + "=?";
String[] whereargs = new String[]{studentid,classid};
db.update(GRADE_TABLE_NAME,cv,whereclause,whereargs);
}
}
这篇关于如何使用两个不同表中的元素更新/删除SQLite的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
