java - 使用 SQLite 从数据库中选择 - 'No such column'

Question

在我的应用程序中，我想根据谁登录到应用程序来调出模块。

如果我使用测试数据(例如简单数据，例如 1 作为用户名和 1 作为密码)，这将非常有效，但是当我使用实际测试数据(例如 C343)时，它会抛出错误，指出没有这样的列，即使尽管在 logcat 中它显示在数据库中。

下面是我如何从数据库中选择信息:

在DB.java

public class DB extends SQLiteOpenHelper {
    public static final int db_version = 1;  
    //Student Table
    public static final String Table = "Students";      
    public static final String Student_ID = "Student_ID";
    public static final String Student_Name = "Student_Name";
    public static final String Student_Password = "Student_Password";
    public static final String Student_Gender = "gender";
    public static final String Student_Age = "age";
    public static final String Student_Course = "course";
    public static final String Modules1 = "modules1";
    public static final String Modules2 = "modules2";
    public static final String Modules = "modules";
    public static final String CNumber = "CNumber";
    //Modules Table
    public static final String Table2 = "Modules";  
    public static final String Module_ID = "moduleid";
    public static final String Module_Name = "modulename";
    public static final String Module_Lectureroom = "modulelectureroom";
    public static final String Module_Seminarroom = "moduleseminarroom";
    public static final String Module_Lecturer = "modulelecturer";
    public static final String Module_Group = "modulegroup";
    public static final String Module_Lecturetime = "modulelecturetime";
    public static final String Module_Seminartime = "moduleseminartime";
    public static final String Module_Lecturedate = "modulelecturedate";
    public static final String Module_Seminardate = "moduleseminardate";

    public boolean checker = false;
    public DB(Context context) {
        super(context, tableColumns.Database, null, db_version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL("DROP TABLE IF EXISTS " + Table);
        Log.d("DB", "Creating table...");   
        //Students Table
        db.execSQL("CREATE TABLE " + Table + "(" + 
                Student_ID + " INTEGER PRIMARY KEY, " +      
                Student_Name + " TEXT, " +
                Student_Password + " TEXT, " +
                Student_Age + " TEXT, " +
                Student_Gender + " TEXT, " +              
                Student_Course + " TEXT, " +
                Modules1 + " TEXT, " +
                Modules2 + " TEXT, " +
                Modules + " TEXT, " +
                CNumber + " TEXT)");
        Log.d("DB", "Student Table Created");
        //Modules Table
        db.execSQL("DROP TABLE IF EXISTS " + Table2);
        db.execSQL("CREATE TABLE " + Table2 + "(" + 
                Module_ID + " INTEGER PRIMARY KEY, " +
                Module_Name + " TEXT, " +
                Module_Lectureroom + " TEXT, " +
                Module_Seminarroom + " TEXT, " +
                Module_Lecturer + " TEXT, " +
                Module_Group + " TEXT, " +            
                Module_Lecturetime + " TEXT, " +
                Module_Seminartime + " TEXT, " +
                Module_Lecturedate + " TEXT, " +
                Module_Seminardate + " TEXT)");
        Log.d("DB", "Module Table Created");
            }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + Table);
        db.execSQL("DROP TABLE IF EXISTS " + Table2);
        onCreate(db);
    }

    public boolean Login(String username, String password) throws SQLException
    {
    SQLiteDatabase db = this.getWritableDatabase();
    Cursor mCursor = db.rawQuery("SELECT * FROM " + Table + " WHERE CNumber=? AND Student_Password=?", new String[]{username,password});        
    if (mCursor != null) {
    if(mCursor.getCount() > 0)
    {
    checker = true;
    return true;
    }
    }
    return false;
    }


    public void createstudents()
    {
        insertStudent("Matt", "123", 22, "Male", "Computing", "Digital Security", "Mod2", "Mod3", "C3438525");
        insertStudent("Charlie", "Test", 19, "Male", "Sport Science", "Biomech Analysis", "Meas & Eval for Phsio", "Prof Dev", "C33429960");
        insertStudent("Amber", "Test", 20, "Female", "Travel & Tourism", "Work based learning", "Sustainable Tourism", "Tourism Operations", "C3399607");
        insertStudent("1", "1", 1, "1", "1", "1", "1", "3", "2");
        insertStudent("Test", "Test", 2, "Test", "Test", "Test", "Test", "Test", "C343Test");
        Log.d("Students", "Created");       
    }

    public void createmodules()
    {
        insertModules("Digital Security", "CAG05", "JG202", "Michael Kemp", "A", "15:00PM", "11:00AM", "Tuesday", "Tuesday");
        Log.d("Modules", "Created");
    }

    public void reset(SQLiteDatabase db)
    {
        db.execSQL("DROP TABLE IF EXISTS " + Table);
        onCreate(db);   
    }

    public List<tableStudents> getData() {
        List<tableStudents> studentList = new ArrayList<tableStudents>();
        // Select All Query
        String selectQuery = "SELECT * FROM " + Table;
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                tableStudents student = new tableStudents();
                student.name = cursor.getString(1);
                student.password = cursor.getString(2);
                student.age = Integer.parseInt(cursor.getString(3));
                student.gender = cursor.getString(4);            
                student.course = cursor.getString(5);
                student.modules = cursor.getString(6);             
                student.modules1 = cursor.getString(7);
                student.modules2 = cursor.getString(8);
                student.cnumber = cursor.getString(9);
                studentList.add(student);
            } while (cursor.moveToNext());
        }
        // return contact list
        return studentList;     
    }

    public List<tableStudents> getStudentsModules(String username) {
        List<tableStudents> studentModules = new ArrayList<tableStudents>();

        // Select All Query depending on who is logged in.      
        String selectQuery = "SELECT " + Modules1 + ", " + Modules2 + ", " + Modules + " FROM " + Table + " WHERE " + CNumber + " = " + username;
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                tableStudents student = new tableStudents();                
                student.modules = cursor.getString(0);             
                student.modules1 = cursor.getString(1);
                student.modules2 = cursor.getString(2);       
                studentModules.add(student);
            } while (cursor.moveToNext());
        }
        // return contact list
        return studentModules;      
    }

    public List<tableModules> getModules() {
        List<tableModules> moduleList = new ArrayList<tableModules>();
        // Select All Query
        String selectQuery = "SELECT * FROM " + Table2;
        SQLiteDatabase db = this.getWritableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);
        // looping through all rows and adding to list
        if (cursor.moveToFirst()) {
            do {
                tableModules module = new tableModules();
                module.modulename = cursor.getString(1);
                module.modulelectureroom = cursor.getString(2);
                module.moduleseminarroom = cursor.getString(3);
                module.modulelecturer = cursor.getString(4);             
                module.modulegroup = cursor.getString(5);
                module.modulelecturetime = cursor.getString(6);            
                module.moduleseminartime = cursor.getString(7);
                module.modulelecturedate = cursor.getString(8);
                module.moduleseminardate = cursor.getString(9);
                moduleList.add(module);
            } while (cursor.moveToNext());
        }
        // return contact list
        return moduleList;      
    }

    public boolean insertStudent(String name, String password, int age, String gender, String course, String modules, String modules1, String modules2, String CNumb) {     
        SQLiteDatabase db = getWritableDatabase();  
        ContentValues contentValues = new ContentValues();       
        contentValues.put(Student_Name, name);    
        contentValues.put(Student_Gender, gender);
        contentValues.put(Student_Age, age);      
        contentValues.put(Student_Password, password);
        contentValues.put(Student_Course, course);
        contentValues.put(Modules, modules);
        contentValues.put(Modules1, modules1);
        contentValues.put(Modules2, modules2);
        contentValues.put(CNumber, CNumb);   
        db.insert(Table, null, contentValues);
        Log.d("DB", "Students Inserted Successfully");
        return true;
    }

    public boolean insertModules(String modulename, String modulelectureroom, String moduleseminarroom, String modulelecturer, String modulegroup, String modulelecturetime, String moduleseminartime, String modulelecturedate, String moduleseminardate) {        
        SQLiteDatabase db = getWritableDatabase();  
        ContentValues contentValues = new ContentValues();       
        contentValues.put(Module_Name, modulename);   
        contentValues.put(Module_Lectureroom, modulelectureroom);
        contentValues.put(Module_Seminarroom, moduleseminarroom);     
        contentValues.put(Module_Lecturer, modulelecturer);
        contentValues.put(Module_Group, modulegroup);
        contentValues.put(Module_Lecturetime, modulelecturetime);
        contentValues.put(Module_Seminartime, moduleseminartime);
        contentValues.put(Module_Lecturedate, modulelecturedate);
        contentValues.put(Module_Seminardate, moduleseminardate);    
        db.insert(Table2, null, contentValues);
        Log.d("DB", "Modules Inserted Successfully");
        return true;
    }

}

然后在 MainActivity.java 中调用它，将模块保存为字符串。

List<tableStudents> studentModules = db.getStudentsModules(sessionName);            
            for (tableStudents session: studentModules)
            {
                //Save modules to string
                String module1 = session.modules.toString();
                String module2 = session.modules1.toString();
                String module3 = session.modules2.toString();           
            } 

这是当我使用 C343Test 作为用户名和 Test 作为密码登录时的 Logcat:

02-22 00:24:15.214: D/Username+PW Combos(2593): Username: C343Test Password: Test
02-22 00:24:15.214: D/Digital Security(2593): Michael Kemp
02-22 00:24:15.302: E/SQLiteLog(2593): (1) no such column: C343Test
02-22 00:24:15.322: D/AndroidRuntime(2593): Shutting down VM
02-22 00:24:15.333: E/AndroidRuntime(2593): FATAL EXCEPTION: main
02-22 00:24:15.333: E/AndroidRuntime(2593): Process: com.example.project, PID: 2593
02-22 00:24:15.333: E/AndroidRuntime(2593): java.lang.RuntimeException: Unable to resume activity {com.example.project/com.example.project.MainActivity}: android.database.sqlite.SQLiteException: no such column: C343Test (code 1): , while compiling: SELECT modules1, modules2, modules FROM Students WHERE CNumber = C343Test
02-22 00:24:15.333: E/AndroidRuntime(2593):     at android.app.ActivityThread.performResumeActivity(ActivityThread.java:2989)
02-22 00:24:15.333: E/AndroidRuntime(2593):     at android.app.ActivityThread.handleResumeActivity(ActivityThread.java:3020)
02-22 00:24:15.333: E/AndroidRuntime(2593):     at android.app.ActivityThread.handleLaunchActivity(ActivityThread.java:2395)
02-22 00:24:15.333: E/AndroidRuntime(2593):     at android.app.ActivityThread.access$800(ActivityThread.java:151)
02-22 00:24:15.333: E/AndroidRuntime(2593):     at android.app.ActivityThread$H.handleMessage(ActivityThread.java:1303)
02-22 00:24:15.333: E/AndroidRuntime(2593):     at android.os.Handler.dispatchMessage(Handler.java:102)
02-22 00:24:15.333: E/AndroidRuntime(2593):     at android.os.Looper.loop(Looper.java:135)
02-22 00:24:15.333: E/AndroidRuntime(2593):     at android.app.ActivityThread.main(ActivityThread.java:5257)
02-22 00:24:15.333: E/AndroidRuntime(2593):     at java.lang.reflect.Method.invoke(Native Method)
02-22 00:24:15.333: E/AndroidRuntime(2593):     at java.lang.reflect.Method.invoke(Method.java:372)
02-22 00:24:15.333: E/AndroidRuntime(2593):     at com.android.internal.os.ZygoteInit$MethodAndArgsCaller.run(ZygoteInit.java:903)
02-22 00:24:15.333: E/AndroidRuntime(2593):     at com.android.internal.os.ZygoteInit.main(ZygoteInit.java:698)
02-22 00:24:15.333: E/AndroidRuntime(2593): Caused by: android.database.sqlite.SQLiteException: no such column: C343Test (code 1): , while compiling: SELECT modules1, modules2, modules FROM Students WHERE CNumber = C343Test
02-22 00:24:15.333: E/AndroidRuntime(2593):     at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
02-22 00:24:15.333: E/AndroidRuntime(2593):     at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:889)
02-22 00:24:15.333: E/AndroidRuntime(2593):     at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:500)
02-22 00:24:15.333: E/AndroidRuntime(2593):     at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
02-22 00:24:15.333: E/AndroidRuntime(2593):     at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:58)
02-22 00:24:15.333: E/AndroidRuntime(2593):     at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
02-22 00:24:15.333: E/AndroidRuntime(2593):     at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
02-22 00:24:15.333: E/AndroidRuntime(2593):     at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1316)
02-22 00:24:15.333: E/AndroidRuntime(2593):     at android.database.sqlite.SQLiteDatabase.rawQuery(SQLiteDatabase.java:1255)
02-22 00:24:15.333: E/AndroidRuntime(2593):     at com.example.project.DB.getStudentsModules(DB.java:160)
02-22 00:24:15.333: E/AndroidRuntime(2593):     at com.example.project.MainActivity.onResume(MainActivity.java:79)
02-22 00:24:15.333: E/AndroidRuntime(2593):     at android.app.Instrumentation.callActivityOnResume(Instrumentation.java:1257)
02-22 00:24:15.333: E/AndroidRuntime(2593):     at android.app.Activity.performResume(Activity.java:6076)
02-22 00:24:15.333: E/AndroidRuntime(2593):     at android.app.ActivityThread.performResumeActivity(ActivityThread.java:2978)
02-22 00:24:15.333: E/AndroidRuntime(2593):     ... 11 more

Answer 1

如果您以任何方式修改了表，则必须增加数据库版本，否则不会调用 onUpdate。如果要进行字符串比较，则必须使用引号:

String selectQuery = "SELECT " + Modules1 + ", " + Modules2 + ", " + Modules + " FROM " + Table + " WHERE " + CNumber + " = \"" + username + "\"";