sqlite - 如何使用来自两个不同表 SQLite 的元素更新/删除
问题描述
我正在开发一个学生成绩提交程序，该程序接受以下输入：学生 ID、学生名字、学生姓氏、班级 ID、班级名称、成绩点和字母成绩。为了防止数据冗余，我创建了三个不同的表。但是，当尝试“修改”或“删除”具有来自不同表的两个值的条目时，我遇到了问题。（例如删除 WHERE student.id 等于 STUDENT_TABLE 中的 student.id 并且 WHERE class.id 等于 CLASS_TABLE 中的 class.id）
话虽如此，这是我当前用于修改学生班级的修改代码。我需要以关联 student.id 和 class.id 的方式进行修改（位于两个不同的表中）
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)");
解决方案
您永远不需要至少一次更新多个表，例如学生姓名已更改（学生表将更改）或说成绩已更改（更改学生各自的成绩条目）。
所以我相信最适合的是更新学生详细信息（名字或姓氏或两者）的方法和改变成绩的方法（再次或两者）。
至于删除，您可以依次从成绩表和学生表中删除，反之亦然。
因此，我相信以下代码包括方法、、、和deleteStudentInfo还changeStudentName包括示例用法以及创建和填充表（您可能希望考虑修改后的模式）：-changeStudentFirstNamechangeStudentLastNamechangeStudentGrade
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);
}
}
推荐阅读
- android - Android SDK 管理器中的支持存储库
- javascript - pg-promise 错误地将多面体插入到 postgis 数据库中
- scala - 如何获取每个值的 rdd 的前 n 个元素？
- c# - 计算机是否开始正确存储 0.1？
- r - 查找列之间的匹配模式并计算一列中每个不同元素的数量
- c# - 失败无法初始化类 org.sikuli.script.Screen' sikuli C#
- javascript - 通过Javascript动态设置相对位置
- pycharm - 未定义 R 解释器：许多与 R 相关的功能（如组合、代码检查和帮助）将不可用
- laravel - 重定向到laravel中RegisterController的create方法
- mysql - 表设计：Type_id 列或布尔列