android - 如何修复 Android SQLITE_ERROR，错误代码 1

Question

试图获取一个包含日期和相​​关注释的表格。我正在发送请求以获取表格和数据，日期 = 我决定的任何内容。

我收到以下错误:

E/AndroidRuntime: FATAL EXCEPTION: main
Process: com.example.ar.calendar13, PID: 22214
android.database.sqlite.SQLiteException: near "Feb": syntax error (code 1): , while compiling: SELECT CAL_EVENT_ID, CAL_EVENT_TTTLE, CAL_EVENT_NOTES, CAL_REMINDER_DATE, CAL_EVENT_REPEAT FROM CAL_EVENT_TABLE WHERE CAL_REMINDER_DATE = Tue Feb 12 00:00:00 EST 2019
#################################################################
Error Code : 1 (SQLITE_ERROR)
Caused By : SQL(query) error or missing database.
    (near "Feb": syntax error (code 1): , while compiling: SELECT CAL_EVENT_ID, CAL_EVENT_TTTLE, CAL_EVENT_NOTES, CAL_REMINDER_DATE, CAL_EVENT_REPEAT FROM CAL_EVENT_TABLE WHERE CAL_REMINDER_DATE = Tue Feb 12 00:00:00 EST 2019)
#################################################################
    at android.database.sqlite.SQLiteConnection.nativePrepareStatement(Native Method)
    at android.database.sqlite.SQLiteConnection.acquirePreparedStatement(SQLiteConnection.java:1096)
    at android.database.sqlite.SQLiteConnection.prepare(SQLiteConnection.java:661)
    at android.database.sqlite.SQLiteSession.prepare(SQLiteSession.java:588)
    at android.database.sqlite.SQLiteProgram.<init>(SQLiteProgram.java:59)
    at android.database.sqlite.SQLiteQuery.<init>(SQLiteQuery.java:37)
    at android.database.sqlite.SQLiteDirectCursorDriver.query(SQLiteDirectCursorDriver.java:44)
    at android.database.sqlite.SQLiteDatabase.rawQueryWithFactory(SQLiteDatabase.java:1746)
    at android.database.sqlite.SQLiteDatabase.queryWithFactory(SQLiteDatabase.java:1593)
    at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1464)
    at android.database.sqlite.SQLiteDatabase.query(SQLiteDatabase.java:1632)
    at com.example.ar.calendar13.Cal_DBManager.fetchDayRecords(Cal_DBManager.java:85)

数据库管理器:

 public Cursor fetchDayRecords(String strCalDate) {
    String[] columns = new String[]{
            Cal_DBHelper.CAL_EVENT_ID, Cal_DBHelper.CAL_EVENT_TTTLE,
            Cal_DBHelper.CAL_EVENT_NOTES, Cal_DBHelper.CAL_REMINDER_DATE,
            Cal_DBHelper.CAL_EVENT_REPEAT
    };

    Cursor cursor = database.query(Cal_DBHelper.TABLE_NAME1, columns,
            Cal_DBHelper.CAL_REMINDER_DATE + " = " + strCalDate,
            null, null, null, null);

    if (cursor != null) {
        cursor.moveToFirst();
    }//if

    return cursor;
}//displayRecordTable1

数据库助手:

 private static final String CREATE_TABLE1 = "create table " + TABLE_NAME1 + "(" + CAL_EVENT_ID
        + " INTEGER PRIMARY KEY AUTOINCREMENT, " + CAL_EVENT_TTTLE + " TEXT NOT NULL, " +
        CAL_EVENT_NOTES + " TEXT NOT NULL, " + CAL_REMINDER_DATE + " TEXT NOT NULL, " +
        CAL_EVENT_REPEAT + " INTEGER);";

我调用“FETCHDAYRECORDS”的代码:

final Cursor cursor = dbManager.fetchDayRecords(dateClicked.toString());
    dbNumItems = cursor.getCount();

dateCLicked 是一个字符串值，看起来像这样(例如):2019 年美国东部时间 2 月 12 日星期二 00:00:00

为什么我会收到错误消息？？？它说我的错误是接近“二月”？？？

是不是因为 table 是空的？我怀疑，我正在检查是否有任何记录......

任何帮助都会有帮助!

提前致谢!

托尼

Answer 1

您的问题是 WHERE 子句中的日期(即 Tue Feb 12 00:00:00 EST 2019)未用引号引起来。

如果您使用查询方法的第 4 个参数，即指定选择参数 (string[]) 并在 WHERE 子句(第 3 个参数)中包含占位符 (?)，那么除了被正确地括在引号中，您还可以防止 SQL 注入(inject)。

例如你可以使用

public Cursor fetchDayRecords(String strCalDate) {
    String[] columns = new String[]{
            Cal_DBHelper.CAL_EVENT_ID, Cal_DBHelper.CAL_EVENT_TTTLE,
            Cal_DBHelper.CAL_EVENT_NOTES, Cal_DBHelper.CAL_REMINDER_DATE,
            Cal_DBHelper.CAL_EVENT_REPEAT
    };

    Cursor cursor = database.query(Cal_DBHelper.TABLE_NAME1, columns,
            Cal_DBHelper.CAL_REMINDER_DATE + " =?", new String[]{strCalDate},
            null, null, null);
    cursor.moveToFirst();
    return cursor;

}//displayRecordTable1

• 请注意，从查询方法返回的 Cursor 永远不会为 null，因此 if (cursor !- null) 没有任何效果，已被删除。