Is there a concise, predictable way to get a column value from a cursor?

Question

Suppose you're trying to get values from several columns of an Android cursor (I'm using SQLite if that matters). You only care about one row, so caching the column indices doesn't make sense. You can try to get each column's value in one line like this:

String value = cursor.getString(cursor.getColumnIndex(columnName));

But how do you detect and handle a case where a column by that name doesn't exist in the cursor?

We know that if that happens, cursor.getColumnIndex(name) will return -1. But then what will cursor.getString(-1) do? Can you use try, and catch any exception thrown? It's not clear ... the documentation says,

The result and whether this method throws an exception when the column value is null or the column type is not a string type is implementation-defined.

But it doesn't say anything about exceptions or other specified behavior for a columnIndex of -1.

My testing on a few devices indicates that you get this exception:

java.lang.IllegalStateException: Couldn't read row 0, col -1 from CursorWindow.
     Make sure the Cursor is initialized correctly before accessing data from it.

Can we count on that exception being thrown?

Another way to do it is to check the index returned by getColumnIndex() before calling getString(columnIndex):

columnIndex = cursor.getColumnIndex(columnName);
if (columnIndex < 0) {
    // handle absence of column
} else {
    value = cursor.getString(columnIndex);
}

But using that many lines per column seems verbose, and violates the DRY principle, lending itself to careless errors in the code.

I wouldn't mind the repetition if we could extract that code into a separate method to be called for each column. But I don't see any simple way to do it, because the columns can have different types. (We could subclass CursorWrapper and implement getInt(), getString(), etc. to do the checking we want, thus confining the repetition to a specialized utility class. Is that the best we can do?)

Another approach would be using getColumnIndexOrThrow() instead of getColumnIndex(). That would allow you to skip the step of saving the value of columnIndex and testing it, because you can count on an exception being thrown if columnIndex is -1 (and catch it). But that doesn't perform very well:

If you're not sure if a column will exist or not use getColumnIndex(String) and check for -1, which is more efficient than catching the exceptions.

Any other ideas? Any thoughts on the above ideas?

Answer 1

Is there a concise, predictable way to get a column value from a cursor?

I'd say yes but that would be according to good design methodology which would largely if not completely eliminate guessing/not knowing a column name. That is the column names would be known before the Cursor is created.

However, there could be situations where the column name, even if correct, could result in unanticipated results. For example where a Cursor with joined tables which had the same column name (e.g. the often used _id column) or perhaps going to the realms of insanity something like SELECT _id, _id, _id FROM cards . In such cases renaming column name via an AS clause would be the solution.

But how do you detect and handle a case where a column by that name doesn't exist in the cursor?

Again, as above, a well designed project would likely fully negate the chance of this happening. If not, then use of the Cursor getColumnCount and getColumnName methods could be used to circumvent the -1, IllegalStateException condition e.g.

public boolean isColumnInCursor(String columName, Cursor csr) {
    for (int i=0; i < csr.getColumnCount(); i++) {
        if (csr.getColumnName(i).toLowerCase().equals(columName.toLowerCase())) {
            csr.close();
            return true;
        }
    }
    csr.close();
    return false;
}

An example usage being :-

    Cursor csr = ex001db.getAisleAndShopsWithUniqueIDColumns();
    String test_column = "Fred";
    if (!ex001db.isColumnInCursor("Fred", csr)) {
        Log.d("OUCH","Column " + test_column + " not in Cursor");
    }
    test_column = DBHlpr001.AISLENAMECOLUMN;
    if (!ex001db.isColumnInCursor(test_column, csr)) {
        Log.d("OUCH","Column " + test_column + " not in Cursor");
    } else {
        Log.d("NOT OUCH","Column " + test_column + " is in Cursor");
    }
    csr.close();

with output from the above being :-

09-20 13:25:35.163 4217-4217/? D/OUCH: Column Fred not in Cursor
09-20 13:25:35.164 4217-4217/? D/NOT OUCH: Column aislename is in Cursor

I wouldn't mind the repetition if we could extract that code into a separate method to be called for each column. But I don't see any simple way to do it, because the columns can have different types. (We could subclass CursorWrapper and implement getInt(), getString(), etc. to do the checking we want, thus confining the repetition to a specialized utility class. Is that the best we can do?)

I'm not really sure if the above code (isColumnInCursor method) would suit your requirements although I'm pretty sure it meets the single seperate method, at least for detecting the missing column condition.

Any other ideas? Any thoughts on the above ideas?

The above!?