如何处理 SQLite 数据库中可为空的整数列 [英] How to handle nullable integer columns in SQLite database

问题描述

我有一个像这样的 Android SQLite 表定义:

I have an Android SQLite table definition like this:

create table MyTable(..., myDate integer, ...);

稍后在我的代码中，我查询此表以通过游标检索 myDate 的值:

Later in my code I query this table to retrieve the value for myDate via a cursor:

long dateInstant = cursor.getLong(cursor.getColumnIndexOrThrow("myDate"));

但是，我想知道这是否是最好的方法.如果该值未设置(即数据库上的 null)，则上述方法返回 0，这恰好也是一个有效值.我需要区分有效值和空值.另外，来自 getLong 的 javadoc:

However, I'm wondering if this is the best approach. If the value is not set (i.e. null on the database) the above method returns 0, which happens to also be a valid value. I need to differentiate valid values from null values. Also, from the javadoc for getLong:

当列值为空、列类型不是整数类型或整数值超出范围[Long.MIN_VALUE, Long.MAX_VALUE]时，结果以及该方法是否抛出异常由实现定义.

The result and whether this method throws an exception when the column value is null, the column type is not an integral type, or the integer value is outside the range [Long.MIN_VALUE, Long.MAX_VALUE] is implementation-defined.

这似乎表明，如果我正确阅读了这一点，那么在未来的某个时刻，Android 中提供的 SQLite 实现可能会选择抛出异常而不是返回 0.我正在寻找跨 Android 版本的一致解决方案.

which seems to suggest, if I read this correctly, that at some point in the future the SQLite implementation shipped in Android may choose to throw an exception rather than return 0. I'm looking for a consistent solution across Android versions.

简而言之，如何以一种允许我区分有效值和空值的方式稳健地处理从列中检索整数数据?

In short, how can I robustly handle the retrieval of integer data from a column in a way which allows me to differentiate between valid values and null?

推荐答案

你可以做

long dateInstant  = 0; // some value to represent null
if (!cursor.isNull (colIndex))
  dateInstance = cursor.getLong (colIndex);

这篇关于如何处理 SQLite 数据库中可为空的整数列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！