java - db.query 无法使用整数字段进行选择

Question

我有一个包含 2 个表的数据库 - 类别和任务，每个任务都有一个与类别 _id 字段关联的字段。问题是，对tasks.category_id = 当前类别id 的选择返回空光标。

在 Debug模式下可以看出选择标准是正确的；检查数据库字段并具有正确的值；删除选择标准会正确返回完整数据库。如果在第一个参数中进行了所有选择，则将 .query 替换为 .rawQuery 将返回正确的值。这个效果很好:

  String selection = "select * from task where category_id = " + mId;
  Cursor taskCursor = mDb.rawQuery(selection, null);

主要 Activity :

 //request data from categories table
    mDb = mDbOpenHelper.getReadableDatabase();
    final Cursor categoryCursor = mDb.query(CategoryEntry.TABLE_NAME, 
        null,null, null, null, null, null);

    int mCategoryIdPos = mCursor.getColumnIndex(CategoryEntry._ID);
    int mId = mCursor.getInt(mCategoryIdPos);
 //request data from tasks table for the selected category
    String selection = TaskEntry.COLUMN_CATEGORY_ID + " = ?";
    String selectionArgs = Integer.toString(mId);
    Cursor taskCursor = mDb.query(TaskEntry.TABLE_NAME, null, selection, 
        selectionArgs, null, null, null, null)

任务光标为空。

数据库合约类:

    public static final class TaskEntry implements BaseColumns {
    public static final String TABLE_NAME = "task";
    public static final String COLUMN_TASK_NAME = "task_name";
    public static final String COLUMN_CATEGORY_ID = "category_id";
    public static final String COLUMN_SKILL_ID = "skill_id";
    public static final String COLUMN_TASK_PD = "task_pd";
    public static final String COLUMN_TASK_DATE = "task_date";
    public static final String COLUMN_TASK_PERIOD = "task_period";
    public static final String COLUMN_TASK_FREQUENCY = "task_frequency";
    public static final String COLUMN_TASK_ONBY = "task_onby";
    public static final String COLUMN_TASK_COMPLETION = 
      "task_completion";

    public static final String SQL_CREATE_TABLE = "CREATE TABLE " + 
         TABLE_NAME + " (" +
            _ID + " INTEGER PRIMARY KEY, " +
            COLUMN_TASK_NAME + " TEXT UNIQUE NOT NULL, " +
            COLUMN_CATEGORY_ID + ", " +
            COLUMN_SKILL_ID + ", " +
            COLUMN_TASK_PD + ", " +
            COLUMN_TASK_DATE + ", " +
            COLUMN_TASK_PERIOD + ", " +
            COLUMN_TASK_FREQUENCY + ", " +
            COLUMN_TASK_ONBY + ", " +
            COLUMN_TASK_COMPLETION + ")";
}

public static final class CategoryEntry implements BaseColumns{
    public static final String TABLE_NAME = "category";
    public static final String COLUMN_CATEGORY_NAME = "category_name";
    public static final String COLUMN_CATEGORY_PD = "category_pd";
    public static final String COLUMN_CATEGORY_DATE = "category_date";
    public static final String COLUMN_CATEGORY_PERIOD = 
   "category_period";
    public static final String COLUMN_CATEGORY_FREQUENCY = 
     "category_frequency";
    public static final String COLUMN_CATEGORY_ONBY = "category_onby";
    public static final String COLUMN_CATEGORY_COMPLETION = 
       "category_completion";

    public static final String SQL_CREATE_TABLE = "CREATE TABLE " + enter 
      code hereTABLE_NAME + " (" +
            _ID + " INTEGER PRIMARY KEY, " +
            COLUMN_CATEGORY_NAME + " TEXT UNIQUE NOT NULL, " +
            COLUMN_CATEGORY_PD + ", " +
            COLUMN_CATEGORY_DATE + ", " +
            COLUMN_CATEGORY_PERIOD + ", " +
            COLUMN_CATEGORY_FREQUENCY + ", " +
            COLUMN_CATEGORY_ONBY + ", " +
            COLUMN_CATEGORY_COMPLETION + ")";
}

Answer 1

您似乎遇到了一些问题。

问题 1

选择args，query的第4个参数，应该是 String[] 而不是 String。

即

public Cursor query (String table, 
            String[] columns, 
            String selection, 
            String[] selectionArgs, //<<<<<<<<<<
            String groupBy, 
            String having, 
            String orderBy)

尝试使用:-

String selection = TaskEntry.COLUMN_CATEGORY_ID + " = ?";
String[] selectionArgs = new String[]{Integer.toString(mId)};
Cursor taskCursor = mDb.query(TaskEntry.TABLE_NAME, null, selection, 
    selectionArgs, null, null, null, null)

问题 2

由于您定义了没有 TYPE 的 category_id 列，因此使用默认类型亲和性 NUMERIC，不幸的是，您遇到了 TYPE AFFINITY 很重要的细微差别。

简而言之，您应该应用合适的列类型(INTEGER 是正确的，尽管 TEXT 也可以)，因为这样使用:-

    public static final String SQL_CREATE_TABLE = "CREATE TABLE " +
            TABLE_NAME + " (" +
            _ID + " INTEGER PRIMARY KEY, " +
            COLUMN_TASK_NAME + " TEXT UNIQUE NOT NULL, " +
            COLUMN_CATEGORY_ID + " INTEGER , " + //<<<<<<<<<<
            COLUMN_SKILL_ID + " INTEGER, " + //<<<<<<<<<<
            COLUMN_TASK_PD + ", " +
            COLUMN_TASK_DATE + ", " +
            COLUMN_TASK_PERIOD + ", " +
            COLUMN_TASK_FREQUENCY + ", " +
            COLUMN_TASK_ONBY + ", " +
            COLUMN_TASK_COMPLETION + ")";

• 建议您包含所有列的预期类型。

• 请注意，要运行上述内容，您需要删除数据库或删除表。这可以通过执行以下操作之一来实现

  1. 卸载应用程序然后重新运行，或者
  2. 清除应用的数据然后重新运行，或者
  3. 如果onUpgrade方法已进行适当编码，则可以增加数据库版本号(SQLiteOpenHelper super 调用的第4个参数)。
     • 适当的编码需要先删除表，然后创建表(通常先删除表，然后调用 onCreate 方法)

• 如果您无法承受丢失数据的后果，那么您需要重命名该表(使用 ALTER TABLE)，然后创建它，然后从重命名的原始表中加载数据。

问题 2 - 替代解决方案

更改列类型的另一种方法是使用CAST来强制类型关联，例如你可以使用:-

String selection = TaskEntry.COLUMN_CATEGORY_ID + " = CAST(? AS INTEGER)";

或

String selection = "CAST(" + TaskEntry.COLUMN_CATEGORY_ID  + " AS INTEGER)=?";

• 使用上述任一方法都不需要重新创建表，但这不是更好的方法，因为它很困惑。