Android SQLite：如何区分字段的NULL值与0值？

场景还原 #

创建students表的SQL语句：

CREATE TABLE students (name TEXT, grade INTEGER);

插入仅包含name的记录：

INSERT INTO students(name) VALUES('Allen');

此时grade字段实际值为NULL，但通过以下Android代码读取时：

Cursor cursor = database.rawQuery("SELECT grade FROM students WHERE name = 'Allen'", null);
cursor.moveToFirst();
long serverId = cursor.getLong(cursor.getColumnIndexOrThrow("grade"));
// serverId 结果为0
cursor.close();

getLong()返回0，无法直接区分是NULL还是真实的0值，可通过以下方法解决：

解决方案 #

1. 使用Cursor.isNull()直接判断

这是最直接可靠的方式，在调用getLong()前先检查对应列是否为NULL：

Cursor cursor = database.rawQuery("SELECT grade FROM students WHERE name = 'Allen'", null);
if (cursor.moveToFirst()) {
    int gradeColumnIndex = cursor.getColumnIndexOrThrow("grade");
    long serverId;
    if (cursor.isNull(gradeColumnIndex)) {
        // 处理NULL值的逻辑，比如设置一个业务标识值
        serverId = -1;
    } else {
        serverId = cursor.getLong(gradeColumnIndex);
    }
}
cursor.close();

2. 在SQL查询中添加NULL标记字段

通过CASE语句在查询时生成一个标记字段，明确区分NULL与0：

SELECT 
    grade,
    CASE WHEN grade IS NULL THEN 1 ELSE 0 END AS is_grade_null
FROM students 
WHERE name = 'Allen'

再在Java代码中读取标记字段判断：

Cursor cursor = database.rawQuery("SELECT grade, CASE WHEN grade IS NULL THEN 1 ELSE 0 END AS is_grade_null FROM students WHERE name = 'Allen'", null);
if (cursor.moveToFirst()) {
    int gradeColumnIndex = cursor.getColumnIndexOrThrow("grade");
    int isNullColumnIndex = cursor.getColumnIndexOrThrow("is_grade_null");
    long serverId;
    if (cursor.getInt(isNullColumnIndex) == 1) {
        // 处理NULL值
        serverId = -1;
    } else {
        serverId = cursor.getLong(gradeColumnIndex);
    }
}
cursor.close();

3. 利用getType()判断字段存储类型

Cursor.getType()会返回列的实际存储类型，NULL值对应Cursor.FIELD_TYPE_NULL：

Cursor cursor = database.rawQuery("SELECT grade FROM students WHERE name = 'Allen'", null);
if (cursor.moveToFirst()) {
    int gradeColumnIndex = cursor.getColumnIndexOrThrow("grade");
    long serverId;
    if (cursor.getType(gradeColumnIndex) == Cursor.FIELD_TYPE_NULL) {
        // 处理NULL值
        serverId = -1;
    } else {
        serverId = cursor.getLong(gradeColumnIndex);
    }
}
cursor.close();

内容的提问来源于stack exchange，提问作者L. Swifter