Sqlite query returning 0 even if the value is null

Question

Following is the query I use to fetch an int value corresponding to a particular field in my sqlite db table.

"SELECT conn_status FROM profiles WHERE devID = '" + id+"'"

If there is no value saved for 'conn_status' corresponding to the devID provided, then the existing value in the table would be null. I retrieve the data from java as shown:

c.getInt(c.getColumnIndex("conn_status"))

The problem here is, with the given query , c.getInt returns 0 even if the value existing in the field is null. How can I modify this query such that, it returns a different value instead of 0, say 5, if the value is null.

Any help is appreciated.

Answer 1

You can use the isNull() function. Here's an example:

static int getInt(String columnName)
{
    if(c.isNull(c.getColumnIndex(columnName)))
        return -1;
    return c.getInt(c.getColumnIndex(columnName));
}

Answer 2

In SQLite, you can use the IFNULL function to replace NULL values:

SELECT IFNULL(conn_status, 5) FROM profiles WHERE devID = ?

Answer 3

int in Java is a primitive datatype and cannot be null. Hence, if there is no value returned by your getInt, you get 0. The Integer object may be null so you may want to consider using Integer instead of int if your logic requires you to check for nulls instead of 0's