Android Studio中SQLite多表关联查询实现方法求助

首先，先明确你的核心需求：通过关联三张表，获取包含Student ID、Student First Name、Student Last Name、Class ID、Class Name、Point Grade、Letter Grade的完整数据集，用于后续程序开发。下面直接给出解决方案，同时帮你指出现有代码里的小问题。

一、你的表结构与现有代码 #

建表语句 #

db=openOrCreateDatabase("STUDENTGRADES", Context.MODE_PRIVATE, null); 
db.execSQL("CREATE TABLE IF NOT EXISTS STUDENT_TABLE(studentid VARCHAR, fname VARCHAR, lname VARCHAR);"); 
db.execSQL("CREATE TABLE IF NOT EXISTS CLASS_TABLE(studentid VARCHAR, classid VARCHAR PRIMARY KEY UNIQUE, classname VARCHAR UNIQUE);"); 
db.execSQL("CREATE TABLE IF NOT EXISTS GRADE_TABLE(studentid VARCHAR, classid VARCHAR, classname VARCHAR, pointgrade INTEGER, lettergrade VARCHAR);");

添加功能代码 #

add.setOnClickListener(new OnClickListener() { 
    @Override 
    public void onClick(View v) { 
        // TODO Auto-generated method stub 
        if(fname.getText().toString().trim().length()==0|| 
           lname.getText().toString().trim().length()==0 || 
           studentid.getText().toString().trim().length()==0) { 
            showMessage("Error", "Please enter First & Last Name and Student ID"); 
            return; 
        } 
        ContentValues contentValues = new ContentValues(); 
        contentValues.put("studentid", studentid.getText().toString()); 
        contentValues.put("fname", fname.getText().toString()); 
        contentValues.put("lname", lname.getText().toString()); 
        long result = db.insertWithOnConflict("STUDENT_TABLE", "studentid", contentValues, SQLiteDatabase.CONFLICT_IGNORE); 
        if (result == -1) { 
            showMessage("Error", "This Name Data entry already exists"); 
        } 
        ContentValues contentValues2 = new ContentValues(); 
        contentValues2.put("classid", classid.getText().toString()); 
        contentValues2.put("classname", classname.getText().toString()); 
        long result2 = db.insertWithOnConflict("CLASS_TABLE", "classid", contentValues2, SQLiteDatabase.CONFLICT_IGNORE); 
        if (result2 == -1) { 
            showMessage("Error", "This Class Data entry already exists"); 
        } 
        ContentValues contentValues3 = new ContentValues(); 
        contentValues3.put("studentid", studentid.getText().toString()); 
        contentValues3.put("classid", classid.getText().toString()); 
        contentValues3.put("pointgrade", pointgrade.getText().toString()); 
        contentValues3.put("lettergrade", lettergrade.getText().toString()); 
        long result3 = db.insertWithOnConflict("GRADE_TABLE", "studentid", contentValues3, SQLiteDatabase.CONFLICT_IGNORE); 
        if (result3 == -1) { 
            showMessage("Error", "This Grade Data entry already exists"); 
        } 
        if (result != -1 && result2 != -1 && result3 != -1) 
            showMessage("Success", "Student Record added successfully"); 
        clearText(); 
    } 
});

删除功能代码（注意：存在一处逻辑bug） #

delete.setOnClickListener(new OnClickListener() { 
    Cursor csr = db.query("GRADE_TABLE JOIN STUDENT_TABLE ON STUDENT_TABLE.studentid = GRADE_TABLE.studentid JOIN CLASS_TABLE ON CLASS_TABLE.classid = GRADE_TABLE.classid",null,null,null,null,null,null); 
    @Override 
    public void onClick(View v) { 
        // TODO Auto-generated method stub 
        if(studentid.getText().toString().trim().length()==0 || classid.getText().toString().trim().length()==0) { 
            showMessage("Error", "Please enter Student and Class ID "); 
            return; 
        } 
        Cursor csr=db.rawQuery("SELECT * FROM GRADE_TABLE WHERE studentid='"+studentid.getText()+"' AND classid='"+classid.getText()+"'", null); 
        if(csr.moveToFirst()) { 
            // 此处bug：classid条件错误使用了studentid.getText()，会导致删除逻辑失效
            db.execSQL("DELETE FROM GRADE_TABLE WHERE studentid='"+studentid.getText()+"' AND classid='"+studentid.getText()+"'"); 
            showMessage("Success", "Record Deleted"); 
        } else { 
            showMessage("Error", "Invalid First and Last Name or Student ID"); 
        } 
        clearText(); 
    } 
});

二、多表关联查询的实现方法 #

要得到你需要的合并数据集，我们需要通过JOIN语句关联三张表：

• STUDENT_TABLE与GRADE_TABLE通过studentid字段关联
• CLASS_TABLE与GRADE_TABLE通过classid字段关联

方法1：直接执行关联SQL（最直观） #

你可以通过rawQuery执行以下SQL语句，直接获取合并后的完整数据：

SELECT 
    s.studentid AS 'Student ID',
    s.fname AS 'Student First Name',
    s.lname AS 'Student Last Name',
    c.classid AS 'Class ID',
    c.classname AS 'Class Name',
    g.pointgrade AS 'Point Grade',
    g.lettergrade AS 'Letter Grade'
FROM GRADE_TABLE g
JOIN STUDENT_TABLE s ON g.studentid = s.studentid
JOIN CLASS_TABLE c ON g.classid = c.classid

对应Android代码实现：

// 获取合并后的数据集
Cursor mergedCursor = db.rawQuery(
    "SELECT s.studentid, s.fname, s.lname, c.classid, c.classname, g.pointgrade, g.lettergrade " +
    "FROM GRADE_TABLE g " +
    "JOIN STUDENT_TABLE s ON g.studentid = s.studentid " +
    "JOIN CLASS_TABLE c ON g.classid = c.classid",
    null
);

// 遍历Cursor提取数据
if (mergedCursor.moveToFirst()) {
    do {
        String studentId = mergedCursor.getString(mergedCursor.getColumnIndex("studentid"));
        String firstName = mergedCursor.getString(mergedCursor.getColumnIndex("fname"));
        String lastName = mergedCursor.getString(mergedCursor.getColumnIndex("lname"));
        String classId = mergedCursor.getString(mergedCursor.getColumnIndex("classid"));
        String className = mergedCursor.getString(mergedCursor.getColumnIndex("classname"));
        int pointGrade = mergedCursor.getInt(mergedCursor.getColumnIndex("pointgrade"));
        String letterGrade = mergedCursor.getString(mergedCursor.getColumnIndex("lettergrade"));
        
        // 此处可将数据存入实体类，或直接用于UI展示
    } while (mergedCursor.moveToNext());
}

// 务必关闭Cursor，避免资源泄漏
mergedCursor.close();

方法2：使用SQLiteQueryBuilder构建查询（更安全） #

如果你想避免手写SQL的拼写错误，可以用Android官方提供的SQLiteQueryBuilder来构建关联查询：

SQLiteQueryBuilder queryBuilder = new SQLiteQueryBuilder();

// 设置关联表结构
queryBuilder.setTables("GRADE_TABLE g JOIN STUDENT_TABLE s ON g.studentid = s.studentid JOIN CLASS_TABLE c ON g.classid = c.classid");

// 指定需要查询的字段
String[] projection = {
    "s.studentid",
    "s.fname",
    "s.lname",
    "c.classid",
    "c.classname",
    "g.pointgrade",
    "g.lettergrade"
};

// 执行查询
Cursor mergedCursor = queryBuilder.query(
    db,
    projection,
    null, // 可选：WHERE条件
    null, // 可选：WHERE条件参数
    null, // 可选：GROUP BY
    null, // 可选：HAVING
    null  // 可选：ORDER BY
);

// 遍历和关闭Cursor的逻辑与方法1一致

三、额外优化建议 #

1. 修复删除代码的bug：将删除语句中的classid='"+studentid.getText()+"'改为classid='"+classid.getText()+"'，否则无法正确匹配班级ID。
2. 避免SQL注入风险：当前代码直接拼接用户输入到SQL语句中，存在安全隐患。建议改用参数化查询：

   // 参数化删除示例
   db.execSQL(
       "DELETE FROM GRADE_TABLE WHERE studentid=? AND classid=?",
       new String[]{studentid.getText().toString(), classid.getText().toString()}
   );
   

3. 优化表结构：CLASS_TABLE中的studentid字段多余，班级应独立于学生存在，一个班级可对应多个学生，通过GRADE_TABLE即可维护学生与班级的关联关系。

内容的提问来源于stack exchange，提问作者SE Optimix