Android Studio中SQLite查询含'-'的hh_id时Cursor返回空值问题

问题根源 #

你直接拼接SQL语句时，字符串类型的hh_id字段值没有用单引号包裹：

• 当household_no是纯数字（如1234）时，SQL会自动将其当作数字匹配，能正常查到数据；
• 当household_no包含-（如123-456）时，SQL会把hh_id=123-456解析成算术表达式（123减456），而非字符串匹配，自然找不到对应数据，导致Cursor返回空。

解决方案 #

方案1：使用参数化查询（推荐）

Android SQLite支持用?作为占位符，通过参数化查询避免语法错误和SQL注入风险，这是最佳实践。

示例代码：

String household_no = edt_hh.getText().toString();
// 用rawQuery带参数执行查询
Cursor search = MainActivity.sqLiteHelper.getReadableDatabase().rawQuery(
    "SELECT id,full_name,hh_id,client_status,address,sex,hh_set_group,current_grantee_card_number,other_card_number_1,other_card_holder_name_1,other_card_number_2,other_card_holder_name_2,other_card_number_3,other_card_holder_name_3,upload_history_id,created_at,updated_at,validated_at FROM emv_database_monitoring WHERE hh_id=?",
    new String[]{household_no}
);

// 遍历Cursor读取数据
while (search.moveToNext()) {
    String emv_id = search.getString(0);
    String full_name = search.getString(1);
    String hh_id = search.getString(2);
    String client_status = search.getString(3);
    String address = search.getString(4);
    String sex = search.getString(5);
    String hh_set_group = search.getString(6);
    String current_grantee_card_number = search.getString(7);
    String other_card_number_1 = search.getString(8);
    String other_card_holder_name_1 = search.getString(9);
    String other_card_number_2 = search.getString(10);
    String other_card_holder_name_2 = search.getString(11);
    String other_card_number_3 = search.getString(12);
    String other_cardholder_name_3 = search.getString(13);
    String upload_history_id = search.getString(14);
    String created_at = search.getString(15);
    String updated_at = search.getString(16);
    String validated_at = search.getString(17);
}
Log.v(ContentValues.TAG,"hahaha " +hh_id);
search.close();

也可以用query()方法更安全地构建查询：

String household_no = edt_hh.getText().toString();
String[] projection = {
    "id", "full_name", "hh_id", "client_status", "address", 
    "sex", "hh_set_group", "current_grantee_card_number", 
    "other_card_number_1", "other_card_holder_name_1", 
    "other_card_number_2", "other_card_holder_name_2", 
    "other_card_number_3", "other_card_holder_name_3", 
    "upload_history_id", "created_at", "updated_at", "validated_at"
};
String selection = "hh_id = ?";
String[] selectionArgs = {household_no};

Cursor search = MainActivity.sqLiteHelper.getReadableDatabase().query(
    "emv_database_monitoring",
    projection,
    selection,
    selectionArgs,
    null,
    null,
    null
);

方案2：手动添加单引号（不推荐）

如果一定要拼接字符串，必须给household_no包裹单引号，确保SQL将其识别为字符串：

String household_no = edt_hh.getText().toString();
Cursor search = MainActivity.sqLiteHelper.getData("SELECT id,full_name,hh_id,client_status,address,sex,hh_set_group,current_grantee_card_number,other_card_number_1,other_card_holder_name_1,other_card_number_2,other_card_holder_name_2,other_card_number_3,other_card_holder_name_3,upload_history_id,created_at,updated_at,validated_at FROM emv_database_monitoring WHERE hh_id='" + household_no + "'");

⚠️ 这种方式存在SQL注入风险，若用户输入包含单引号（如O'Neil），会直接导致SQL语法错误，因此不建议使用。

内容的提问来源于stack exchange，提问作者marivic valdehueza