java - 向 SQlite 插入行时 Android 应用程序崩溃？

Question

这是我正在使用的 DBAdapter，由于某种原因，当我尝试插入一行时，应用程序崩溃了。

public class DBAdapter {

private static final String TAG = "DBAdapter"; //used for logging database version changes

// Field Names:
static final String KEY_ROWID = "Row ID";
static final String NAME_COLUMN = "Name";
static final String EMAIL_COLUMN = "E-mail";
static final String PHONENUMBER_COLUMN = "Phone Number";
static final String ADDRESS_COLUMN = "Street Address";
static final String ZIP_COLUMN = "Zip Code";
static final String ARRIVAL_COLUMN = "Arrival Date";
static final String DEPARTURE_COLUMN = "Departure Date";
static final String ROOM_COLUMN = "Room Number";
static final String NOTES_COLUMN = "Notes Area";
public static final String[] ALL_KEYS = new String[] {NAME_COLUMN, EMAIL_COLUMN, PHONENUMBER_COLUMN, ADDRESS_COLUMN, ZIP_COLUMN, ARRIVAL_COLUMN, DEPARTURE_COLUMN, ROOM_COLUMN, NOTES_COLUMN};

// Column Numbers for each Field Name:
public static final int COL_ROWID = 0;
public static final int COL_NAME = 1;
public static final int COL_EMAIL = 2;
public static final int COL_PHONENUMBER = 3;
public static final int COL_ADDRESS = 4;
public static final int COL_ZIP = 5;
public static final int COL_ARRIVAL = 6;
public static final int COL_DEPARTURE = 7;
public static final int COL_ROOM = 8;
public static final int COL_NOTES = 9;

// DataBase info:
static final String DATABASE_NAME = "Reservations.db";
static final String DATABASE_TABLE = "Reservations";
public static final int DATABASE_VERSION = 2; // The version number must be incremented each time a change to DB structure occurs.

//SQL statement to create database
private static final String DATABASE_CREATE_SQL =
        "CREATE TABLE " + DATABASE_TABLE
                + " (" + KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                + NAME_COLUMN + " TEXT NOT NULL, "
                + EMAIL_COLUMN + " TEXT"
                + PHONENUMBER_COLUMN + " TEXT NOT NULL, "
                + ADDRESS_COLUMN + " TEXT"
                + ZIP_COLUMN + " TEXT NOT NULL, "
                + ARRIVAL_COLUMN + " TEXT NOT NULL, "
                + DEPARTURE_COLUMN + " TEXT"
                + ROOM_COLUMN + " TEXT NOT NULL, "
                + NOTES_COLUMN + " TEXT"
                + ");";

private final Context context;
private DatabaseHelper myDBHelper;
private SQLiteDatabase db;


public DBAdapter(Context ctx) {
    this.context = ctx;
    myDBHelper = new DatabaseHelper(context);
}

// Open the database connection.
public DBAdapter open() {
    db = myDBHelper.getWritableDatabase();
    return this;
}

// Close the database connection.
public void close() {
    myDBHelper.close();
}

// Add a new set of values to be inserted into the database.
public long insertRow(String nameValue, String emailValue, String phoneValue, String addressValue, String zipValue, String arrivalValue, String departureValue, String roomValue, String notesValue) {
    ContentValues initialValues = new ContentValues();
    initialValues.put(NAME_COLUMN, nameValue);
    initialValues.put(EMAIL_COLUMN, emailValue);
    initialValues.put(PHONENUMBER_COLUMN, phoneValue);
    initialValues.put(ADDRESS_COLUMN, addressValue);
    initialValues.put(ZIP_COLUMN, zipValue);
    initialValues.put(ARRIVAL_COLUMN, arrivalValue);
    initialValues.put(DEPARTURE_COLUMN, departureValue);
    initialValues.put(ROOM_COLUMN, roomValue);
    initialValues.put(NOTES_COLUMN, notesValue);

    // Insert the data into the database.
    return db.insert(DATABASE_TABLE, null, initialValues);
}

// Delete a row from the database, by rowId (primary key)
public boolean deleteRow(long rowId) {
    String where = KEY_ROWID + "=" + rowId;
    return db.delete(DATABASE_TABLE, where, null) != 0;
}

public void deleteAll() {
    Cursor c = getAllRows();
    long rowId = c.getColumnIndexOrThrow(KEY_ROWID);
    if (c.moveToFirst()) {
        do {
            deleteRow(c.getLong((int) rowId));
        } while (c.moveToNext());
    }
    c.close();
}

// Return all data in the database.
public Cursor getAllRows() {
    String where = null;
    Cursor c =  db.query(true, DATABASE_TABLE, ALL_KEYS, where, null, null, null, null, null);
    if (c != null) {
        c.moveToFirst();
    }
    return c;
}

// Get a specific row (by rowId)
public Cursor getRow(long rowId) {
    String where = KEY_ROWID + "=" + rowId;
    Cursor c =  db.query(true, DATABASE_TABLE, ALL_KEYS,
            where, null, null, null, null, null);
    if (c != null) {
        c.moveToFirst();
    }
    return c;
}

// Change an existing row to be equal to new data.
public boolean updateRow(long rowId, String name, String email, String phone, String address, String zipcode, String arrival, String departure, String room, String notes) {
    String where = KEY_ROWID + "=" + rowId;
    ContentValues newValues = new ContentValues();
    newValues.put(NAME_COLUMN, name);
    newValues.put(EMAIL_COLUMN, email);
    newValues.put(ADDRESS_COLUMN, address);
    newValues.put(ZIP_COLUMN, zipcode);
    newValues.put(ARRIVAL_COLUMN, arrival);
    newValues.put(DEPARTURE_COLUMN, departure);
    newValues.put(ROOM_COLUMN, room);
    newValues.put(NOTES_COLUMN, notes);
    // Insert it into the database.
    return db.update(DATABASE_TABLE, newValues, where, null) != 0;
}


private static class DatabaseHelper extends SQLiteOpenHelper
{
    DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase _db) {
        _db.execSQL(DATABASE_CREATE_SQL);
    }

    @Override
    public void onUpgrade(SQLiteDatabase _db, int oldVersion, int newVersion) {
        Log.w(TAG, "Upgrading application's database from version " + oldVersion
                + " to " + newVersion + ", which will destroy all old data!");

        // Destroy old database:
        _db.execSQL("DROP TABLE IF EXISTS " + DATABASE_TABLE);

        // Recreate new database:
        onCreate(_db);
    }
}


}

这是我的新预订 Activity 的代码

     public void createReservation(View view) {
    db.open();
    EditText nameText = (EditText)findViewById(R.id.nameText);
    String nameValue = nameText.getText().toString();
    EditText emailText = (EditText)findViewById(R.id.emailText);
    String emailValue = emailText.getText().toString();
    EditText phoneNumber = (EditText)findViewById(R.id.phoneNumber);
    String phoneValue = phoneNumber.getText().toString();
    EditText addressText = (EditText)findViewById(R.id.addressText);
    String addressValue = addressText.getText().toString();
    EditText zipNumber = (EditText)findViewById(R.id.zipNumber);
    String zipValue = zipNumber.getText().toString();
    EditText arrivalDate = (EditText)findViewById(R.id.arrivalDate);
    String arrivalValue = arrivalDate.getText().toString();
    EditText departureDate = (EditText)findViewById(R.id.departureDate);
    String departureValue = departureDate.getText().toString();
    EditText notesArea = (EditText)findViewById(R.id.notesText);
    String notesValue = notesArea.getText().toString();
    EditText roomNum = (EditText)findViewById(R.id.roomNum);
    String roomValue = roomNum.getText().toString();

    db.insertRow(nameValue, emailValue, phoneValue, addressValue, zipValue, arrivalValue, departureValue, roomValue, notesValue);
    Toast toast = Toast.makeText(getApplicationContext(), "Creating Reservation", Toast.LENGTH_LONG);
    toast.show();
    db.close();
}

非常感谢任何和所有的帮助。我尝试在网上查找内容，这是我找到 DBAdapter 的地方，但是当我使用这个 DBAdapter 时，我觉得我可能搞砸了一些东西。例如，在 Android Studio 中，列号向我发出检查警告，表明该字段从未使用过。然而它们包含在原始的 DBAdapter 中。我觉得在调整 DBAdapter 以满足我的需求时可能犯了一个错误，但我似乎无法弄清楚我哪里出了问题。我对 Android 编程非常陌生，之前只做过一个其他应用程序。感谢任何帮助

Answer 1

您的 CREATE TABLE 实际上没有创建任何表，因为它错误:

private static final String DATABASE_CREATE_SQL =
        "CREATE TABLE " + DATABASE_TABLE
                + " (" + KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                + NAME_COLUMN + " TEXT NOT NULL, "
                + EMAIL_COLUMN + " TEXT"
                + PHONENUMBER_COLUMN + " TEXT NOT NULL, "
                + ADDRESS_COLUMN + " TEXT"
                + ZIP_COLUMN + " TEXT NOT NULL, "
                + ARRIVAL_COLUMN + " TEXT NOT NULL, "
                + DEPARTURE_COLUMN + " TEXT"
                + ROOM_COLUMN + " TEXT NOT NULL, "
                + NOTES_COLUMN + " TEXT"
                + ");";

应该是:

private static final String DATABASE_CREATE_SQL =
        "CREATE TABLE " + DATABASE_TABLE
                + " (" + KEY_ROWID + " INTEGER PRIMARY KEY AUTOINCREMENT, "
                + NAME_COLUMN + " TEXT NOT NULL, "
                + EMAIL_COLUMN + " TEXT, " // Mind the comma
                + PHONENUMBER_COLUMN + " TEXT NOT NULL, "
                + ADDRESS_COLUMN + " TEXT, " // Mind the comma
                + ZIP_COLUMN + " TEXT NOT NULL, "
                + ARRIVAL_COLUMN + " TEXT NOT NULL, "
                + DEPARTURE_COLUMN + " TEXT, " // Mind the comma
                + ROOM_COLUMN + " TEXT NOT NULL, "
                + NOTES_COLUMN + " TEXT"
                + ");";

请注意，您不需要也不应该使用硬编码列索引。
这是因为如果您执行 SELECT * ... 则没有人可以预测行内的列顺序。

因此，也许在一次查询运行中，电子邮件字段的索引为 2，而在另一次运行中，它的索引为 5。

这就是为什么使用固定列索引不好的原因，最好使用带有 getColumnIndex 的列名。

这段代码让我假设你会做一些坏事:

// Column Numbers for each Field Name:
public static final int COL_ROWID = 0;
public static final int COL_NAME = 1;
public static final int COL_EMAIL = 2;
public static final int COL_PHONENUMBER = 3;
public static final int COL_ADDRESS = 4;
public static final int COL_ZIP = 5;
public static final int COL_ARRIVAL = 6;
public static final int COL_DEPARTURE = 7;
public static final int COL_ROOM = 8;
public static final int COL_NOTES = 9;

相反，使用

cursor.getString(cursor.getColumnIndex(YOUR_COLUMN_NAME)); // I used getString, but you'd use the actual column type

此外，请勿在列名称中使用空格或连字符!

这个

// Field Names:
static final String KEY_ROWID = "Row ID";
static final String NAME_COLUMN = "Name";
static final String EMAIL_COLUMN = "E-mail";
static final String PHONENUMBER_COLUMN = "Phone Number";
static final String ADDRESS_COLUMN = "Street Address";
static final String ZIP_COLUMN = "Zip Code";
static final String ARRIVAL_COLUMN = "Arrival Date";
static final String DEPARTURE_COLUMN = "Departure Date";
static final String ROOM_COLUMN = "Room Number";
static final String NOTES_COLUMN = "Notes Area";

应该是:

// Field Names:
static final String KEY_ROWID = "Row_ID";
static final String NAME_COLUMN = "Name";
static final String EMAIL_COLUMN = "eMail";
static final String PHONENUMBER_COLUMN = "Phone_Number";
static final String ADDRESS_COLUMN = "Street_Address";
static final String ZIP_COLUMN = "Zip_Code";
static final String ARRIVAL_COLUMN = "Arrival_Date";
static final String DEPARTURE_COLUMN = "Departure_Date";
static final String ROOM_COLUMN = "Room_Number";
static final String NOTES_COLUMN = "Notes_Area";