向SQLite数据库添加数据时禁止重复记录的实现方案

嘿，我来帮你梳理下实现这两个重复校验需求的最优路径，顺便解决你现有代码里的一些潜在问题～

首先，最可靠的方式是从数据库层面添加约束，再配合应用层的校验逻辑——数据库约束能从底层杜绝并发场景下的重复数据，应用层则能给用户更友好的即时提示。另外你当前的SQL拼接写法存在SQL注入风险，这也得一并优化。

一、数据库层面添加核心约束 #

1. 禁止同一Student ID对应不同姓名 #

给studentid添加唯一约束，确保每个学生ID只能关联一组固定的姓名。如果想更贴合数据库设计规范，建议拆分表：

• 新建STUDENTS表：存储studentid（设为主键）、fname、lname，天然保证ID唯一且姓名一一对应。
• 新建GRADES表：存储studentid（外键关联STUDENTS表）、classid、classname、pointgrade、lettergrade，并给studentid+classid添加复合唯一约束。

如果暂时不想拆分表，至少修改现有表的创建语句，加入两个关键约束：

db.execSQL("CREATE TABLE IF NOT EXISTS STUDENT_GRADES(
    studentid VARCHAR UNIQUE NOT NULL,
    fname VARCHAR NOT NULL,
    lname VARCHAR NOT NULL,
    classid VARCHAR NOT NULL,
    classname VARCHAR,
    pointgrade INTEGER,
    lettergrade VARCHAR,
    CONSTRAINT unique_student_class UNIQUE (studentid, classid)
);");

• studentid UNIQUE：确保同一个学生ID只能存在一条记录，避免不同姓名复用同ID。
• CONSTRAINT unique_student_class UNIQUE (studentid, classid)：确保同一学生的同一课程不会重复录入。

2. 处理约束冲突 #

当用户尝试插入违反约束的记录时，SQLite会抛出异常，我们需要在代码中捕获并给用户针对性提示。

二、应用层优化与校验逻辑 #

1. 替换SQL拼接，使用参数化查询 #

直接拼接字符串的写法极易引发SQL注入攻击，改用ContentValues或者PreparedStatement来插入数据更安全：

用ContentValues的优化代码示例：

add.setOnClickListener(new OnClickListener() {
    @Override
    public void onClick(View v) {
        String studentId = studentid.getText().toString().trim();
        String firstName = fname.getText().toString().trim();
        String lastName = lname.getText().toString().trim();
        String classId = classid.getText().toString().trim();
        String className = classname.getText().toString().trim();
        String pointGradeStr = pointgrade.getText().toString().trim();
        String letterGrade = lettergrade.getText().toString().trim();

        // 基础非空校验
        if (firstName.isEmpty() || lastName.isEmpty() || studentId.isEmpty()) {
            showMessage("Error", "Please enter First & Last Name and Student ID");
            return;
        }

        // 处理分数格式转换
        Integer pointGrade = null;
        try {
            pointGrade = Integer.parseInt(pointGradeStr);
        } catch (NumberFormatException e) {
            showMessage("Error", "Grade Point must be a valid number");
            return;
        }

        ContentValues values = new ContentValues();
        values.put("studentid", studentId);
        values.put("fname", firstName);
        values.put("lname", lastName);
        values.put("classid", classId);
        values.put("classname", className);
        values.put("pointgrade", pointGrade);
        values.put("lettergrade", letterGrade);

        try {
            // 插入数据，违反约束会抛出SQLiteConstraintException
            long result = db.insertOrThrow("STUDENT_GRADES", null, values);
            if (result != -1) {
                showMessage("Success", "Student Record added successfully");
                clearText();
            } else {
                showMessage("Error", "Failed to add record");
            }
        } catch (SQLiteConstraintException e) {
            // 区分不同的约束冲突，给出精准提示
            if (e.getMessage().contains("UNIQUE constraint failed: STUDENT_GRADES.studentid")) {
                showMessage("Error", "This Student ID already exists with a different name");
            } else if (e.getMessage().contains("UNIQUE constraint failed: STUDENT_GRADES.studentid, STUDENT_GRADES.classid")) {
                showMessage("Error", "This student already has a record for this class");
            } else {
                showMessage("Error", "Duplicate record detected");
            }
        } catch (Exception e) {
            showMessage("Error", "An error occurred: " + e.getMessage());
        }
    }
});

2. 可选：插入前预查询校验 #

虽然数据库约束已经能保证数据一致性，但你也可以在插入前先做查询，提前给用户反馈（注意：并发场景下仍可能出现冲突，所以数据库约束是必须的兜底方案）：

// 检查Student ID是否已存在且姓名不一致
Cursor studentCursor = db.query("STUDENT_GRADES", 
    new String[]{"fname", "lname"}, 
    "studentid = ?", 
    new String[]{studentId}, 
    null, null, null);
if (studentCursor.moveToFirst()) {
    String existingFname = studentCursor.getString(0);
    String existingLname = studentCursor.getString(1);
    if (!existingFname.equals(firstName) || !existingLname.equals(lastName)) {
        showMessage("Error", "This Student ID is already registered with a different name");
        studentCursor.close();
        return;
    }
}
studentCursor.close();

// 检查学生+课程组合是否已存在
Cursor gradeCursor = db.query("STUDENT_GRADES", 
    new String[]{"studentid"}, 
    "studentid = ? AND classid = ?", 
    new String[]{studentId, classId}, 
    null, null, null);
if (gradeCursor.moveToFirst()) {
    showMessage("Error", "This student already has a record for this class");
    gradeCursor.close();
    return;
}
gradeCursor.close();

三、额外小建议 #

• 原表名CUSTOMER_TABLE语义不符，建议改成STUDENT_GRADES或者拆分后的STUDENTS、GRADES，更清晰易懂。
• 考虑把studentid和classid设为整数类型（如果业务允许），或者设置为主键，数据库查询和存储效率会更高。
• 记得在合适的时机关闭数据库连接和Cursor，避免内存泄漏（比如在Activity的onDestroy方法中关闭db）。

内容的提问来源于stack exchange，提问作者Tanner Tattini