java - 无法从数据库读取值

Question

这是我第一次尝试使用 Android 和 SQLite 写入/读取数据库。

下面的代码似乎正在插入数据(我可以看到行数增加)，但是当我尝试调用该值时，会抛出异常

E/CursorWindow﹕ Failed to read row 0, column -1 from a CursorWindow which has 6 rows, 2 columns.

我不明白为什么下面失败了。

  public void Save(String name, String email) {

    _db.execSQL("CREATE TABLE IF NOT EXISTS MailingList (Email VARCHAR, Name VARCHAR);");

    _db.execSQL("INSERT INTO MailingList (Email, Name) VALUES('" + email + "', '" + name + "');");


    ReadDatabase();
    _db.close();

    //_db.deleteAll();
}

private void ReadDatabase() {

    Cursor cursor = _db.rawQuery("SELECT * FROM MailingList", null);

    int i = cursor.getCount();

    ArrayList<String> results = new ArrayList<String>();
    if (cursor != null) {

        if (cursor.moveToFirst()) {
            do {

                String name = cursor.getString(cursor.getColumnIndex("Name")); //ERROR
                String email = cursor.getString(cursor.getColumnIndex("Email"));
                results.add("Name: " + name + ", Email: " + email);
            } while (cursor.moveToNext());
        }
    }
    ListView myList = (ListView) findViewById(R.id.listViewFromDB);
    myList.setAdapter(new ArrayAdapter<String>(this, R.layout.item_layout, results));
}

日志猫

 Caused by: java.lang.IllegalStateException: Couldn't read row 0, col -1 from CursorWindow.  Make sure the Cursor is initialized correctly before accessing data from it.
            at android.database.CursorWindow.nativeGetString(Native Method)
            at android.database.CursorWindow.getString(CursorWindow.java:435)
            at android.database.AbstractWindowedCursor.getString(AbstractWindowedCursor.java:51)
            at com.lmsites.dave.lifecrymailinglist.MyActivity.ReadDatabase(MyActivity.java:106)
            at com.lmsites.dave.lifecrymailinglist.MyActivity.Save(MyActivity.java:88)
            at com.lmsites.dave.lifecrymailinglist.MyActivity.SaveClick(MyActivity.java:73)
            at java.lang.reflect.Method.invokeNative(Native Method)
            at java.lang.reflect.Method.invoke(Method.java:515)
            at android.view.View$1.onClick(View.java:3830)
            at android.view.View.performClick(View.java:4450)
            at android.view.View$PerformClick.run(View.java:18600)
            at android.os.Handler.handleCallback(Handler.java:733)
            at android.os.Handler.dispatchMessage(Handler.java:95)
            at android.os.Looper.loop(Looper.java:136)
            at android.app.ActivityThread.main(ActivityThread.java:5026)
            at java.lang.reflect.Method.invokeNative(Native Method)

Answer 1

E/CursorWindow﹕ Failed to read row 0, column -1 from a CursorWindow which has 6 rows, 2 columns.

此错误表明您试图获取不存在的列的列索引。当找不到特定列时，getColumnIndex() 返回-1。我在您的代码中没有看到任何错误，因此我认为数据库本身可能有问题。

开发时必须记住，只有当您卸载应用程序或删除应用程序管理器中的所有数据时，SQLite 数据库才会真正删除。
因此，如果您在某个时刻在 SQL 脚本中犯了错误，或者添加、删除或重命名了某些列，您需要卸载并重新安装应用程序，以使这些更改在数据库中生效。

<小时/>

这对于许多应用程序来说当然是一个问题，并且在开发时您会一直遇到这个问题。但是，一旦您的应用程序进入应用程序商店，人们就会使用它，并且您不能在每次更新应用程序时要求每个人都重新安装您的应用程序。这太荒谬了，人们不会喜欢它，但有一种方法可以解决这个问题。

您需要将版本号传递到SQLiteOpenHelper中。每次更改数据库时都会增加此版本号，然后当 Android 注意到数据库版本已增加时，将调用 onUpgrade() 回调来相应地升级您的数据库!尝试这样的事情:

public class ExampleSQLiteOpenHelper extends SQLiteOpenHelper {

    public ExampleSQLiteOpenHelper(Context context, String name, SQLiteDatabase.CursorFactory factory, int version) {
        super(context, name, factory, version);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        // The create query here always has to be for the most up to 
        // date version of the database
        db.execSQL("CREATE TABLE MailingList (Email VARCHAR, Name VARCHAR, PhoneNumber VARCHAR);");
        db.execSQL("CREATE TABLE SomeTable (_id PRIMARY KEY AUTOINCREMENT, SomeColumn VARCHAR)");
        ...
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

        // This will be called when you are starting your app for the first time 
        // after increasing the version number.

        // This loops through all the version between the current version of             
        // the database and the newest version. upgradeTo is called with each 
        // version in between.
        for(int i = oldVersion + 1; i <= newVersion; i++) {
            upgradeTo(db, i);
        }
    }

    private void upgradeTo(SQLiteDatabase db, int version) {

        // With this switch you can execute the upgrade scripts for each version 
        // of the databse
        switch (version) {
            case 2:
                // in this version we added a new column to MailingList so we 
                // use ALTER TABLE to add the new column
                db.execSQL("ALTER TABLE MailingList ADD COLUMN PhoneNumber VARCHAR");
                break;

            case 3:
                // In this version we added a new table
                db.execSQL("CREATE TABLE SomeTable (_id PRIMARY KEY AUTOINCREMENT, SomeColumn VARCHAR)");
                break;

            case 4:
                ...
                break;

            case 5:
                ...
                break;
        }
    }
}

当然，最简单的解决方案是删除所有表并在 onUpgrade 中重新创建它们，但数据库中的所有数据都会丢失。如果您不希望这样，您可以使用类似上面的代码来逐步升级您的数据库!