首次启动时创建 SQL 数据库并在下次启动时检索数据库数据

Question

关于android开发，我只是想在第一次启动活动时创建一个SQL数据库（使用首选项）。第二次启动该活动时，它应该从数据库中检索数据并输出一条日志消息。

我第一次成功启动了该活动（假设数据库是在这里创建的），但第二次我收到 IllegalStateException 错误：从第 0 行到第 -1 列获取字段槽失败。不太确定我在这里哪里出错了。有人可以检查一下吗？谢谢

主类

public class MainMenu extends Activity implements OnClickListener{
private ModulesDbAdapter mpDbHelper;

/** Called when the activity is first created. */
@Override
public void onCreate(Bundle savedInstanceState) {
    super.onCreate(savedInstanceState);
    setContentView(R.layout.main);

    //create database here
    SharedPreferences initialPref = getSharedPreferences("INITIAL", 0);
    boolean firsttimer = initialPref.getBoolean("INITIAL", false);

    if (!firsttimer){
        //create database here
        mpDbHelper = new ModulesDbAdapter(this);
        mpDbHelper.open();

        long id1 = mpDbHelper.createReminder("Hello1", "Test 1", "Date1");
        long id2 = mpDbHelper.createReminder("Hello2", "Test 2", "Date2");
        long id3 = mpDbHelper.createReminder("Hello3", "Test 3", "Date3");

        /*Cursor c = mpDbHelper.fetchModules((String)"CS118");
        Log.d("TESTING",c.getString(c.getColumnIndex("KEY_MOD_NAME")));*/

        //get boolean preference to true
        SharedPreferences.Editor editorPref = initialPref.edit();
        editorPref.putBoolean("INITIAL", true);
        editorPref.commit();
    }else {
        fetchData();
    }
}

private void fetchData(){
    mpDbHelper = new ModulesDbAdapter(this);
    mpDbHelper.open();
    long input = 2;

    Cursor c = mpDbHelper.fetchReminder(input);
    startManagingCursor(c);
    Log.d("TESTING",c.getString(c.getColumnIndex("KEY_BODY")));
}

/*@Override
protected void onPause() {
    super.onPause();
    mpDbHelper.close();
}

@Override
protected void onStop() {
    super.onPause();
    mpDbHelper.close();
}*/

}
}

适配器类

public class ModulesDbAdapter {
private static final String DATABASE_NAME = "data";
private static final String DATABASE_TABLE = "reminders";
private static final int DATABASE_VERSION = 1;

public static final String KEY_TITLE = "title";
public static final String KEY_BODY = "body";
public static final String KEY_DATE_TIME = "reminder_date_time";
public static final String KEY_ROWID = "_id";

private DatabaseHelper mDbHelper; 
private SQLiteDatabase mDb;

//defines the create script for the database
private static final String DATABASE_CREATE =
        "create table " + DATABASE_TABLE + " ("
        + KEY_ROWID + " integer primary key autoincrement, "
        + KEY_TITLE + " text not null, "
        + KEY_BODY + " text not null, "
        + KEY_DATE_TIME + " text not null);";

//Context object that will be associated with the SQLite database object
private final Context mCtx;

//The Context object is set via the constructor of the class
public ModulesDbAdapter(Context ctx) {
    this.mCtx = ctx;
}

//helps with the creation and version management of the SQLite database.
private static class DatabaseHelper extends SQLiteOpenHelper {

    //call made to the base SQLiteOpenHelper constructor. This call creates, opens, and/or manages a database
    DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(DATABASE_CREATE); 
    }
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion,int newVersion) {
        // Not used, but you could upgrade the database with ALTER
        // Scripts
    }
}

//now create the database by calling the getReadableDatabase()
public ModulesDbAdapter open() throws android.database.SQLException {
    mDbHelper = new DatabaseHelper(mCtx);
    mDb = mDbHelper.getWritableDatabase();
    return this;
}

//close the database
public void close() {
    mDbHelper.close();
}

public long createReminder(String title, String body, String
    reminderDateTime) {
    ContentValues initialValues = new ContentValues();
    initialValues.put(KEY_TITLE, title);
    initialValues.put(KEY_BODY, body);
    initialValues.put(KEY_DATE_TIME, reminderDateTime);

    //insert row into database
    return mDb.insert(DATABASE_TABLE, null, initialValues);
}



public boolean deleteReminder(long rowId) {
    return mDb.delete(DATABASE_TABLE, KEY_ROWID + "=" + rowId, null) > 0;
}

//utilizes the query() method on the SQLite database to find all the reminders in the system
public Cursor fetchAllReminders() {
    return mDb.query(DATABASE_TABLE, new String[] {KEY_ROWID, KEY_TITLE,
    KEY_BODY, KEY_DATE_TIME}, null, null, null, null, null);
}

public Cursor fetchReminder(long rowId) throws SQLException { 
    Cursor mCursor =
    mDb.query(true, DATABASE_TABLE, new String[] {KEY_ROWID,
    KEY_TITLE, KEY_BODY, KEY_DATE_TIME}, KEY_ROWID + "=" +
    rowId, null, null, null, null, null);



    if (mCursor != null) {
        mCursor.moveToFirst();
    }
    return mCursor;
}

public boolean updateReminder(long rowId, String title, String body, String reminderDateTime) {
    ContentValues args = new ContentValues();
    args.put(KEY_TITLE, title);
    args.put(KEY_BODY, body);
    args.put(KEY_DATE_TIME, reminderDateTime);
    return mDb.update(DATABASE_TABLE, args, KEY_ROWID + "=" + rowId, null) > 0;
}
        // The SQLiteOpenHelper class was omitted for brevity
        // That code goes here.

}

Answer 1

错误很简单。您已在 DbAdapter 类中将 KEY_BODY 定义为静态字符串常量值，但在访问代码中使用了字符串文字“KEY_BODY”。

Log.d("TESTING",c.getString(c.getColumnIndex("KEY_BODY")));

应该是

Log.d("TESTING",c.getString(c.getColumnIndex(ModulesDbAdapter.KEY_BODY)));

Answer 2

您不必明确检查数据库是否已创建。如果您创建实现 onCreate(SQLiteDatabase)、onUpgrade(SQLiteDatabase, int, int) 的 SQLiteOpenHelper 子类，它将“知道”不创建数据库（如果数据库已存在）。

否则，您的错误是在该行中引起的：

Log.d("TESTING",c.getString(c.getColumnIndex("KEY_BODY")));

因为您使用字符串“KEY_BODY”作为列索引，而不是静态字符串变量 KEY_BODY 的值。

Answer 3

我相信您的问题出在您的日志行中。将字符串更改为文字：

Log.d("TESTING",c.getString(c.getColumnIndex(KEY_BODY)));