How to bind INTEGER/NULL to a ? placeholder with rawQuery selectionArgs in Android?

Anything can change below to reach a working solution! I have full control over all of the below schema/data/query/code, so any reasonable improvement is welcome: I'm looking for a simple/clean/to-the-point solution. For example making two different queries (= ? and is null) is the last resort.

Question

I want to change the below code so I can call listCategoriesIn(1) and listCategoriesIn(null) and they both give the correct expected result. I can't make listCategoriesIn(null) work with a WHERE clause like c.parent = ?.

• Is it at all possible to bind an INTEGER or NULL to = ??


• How should I modify the WHERE clause to make it work for both cases?


• What else may I change to make it work?

Table

CREATE TABLE Category (
    _id         INTEGER      NOT NULL,
    name        VARCHAR      NOT NULL,
    parent      INTEGER          NULL --< important bit
        CONSTRAINT fk_Category_parent
            REFERENCES Category(_id)
            ON UPDATE CASCADE
            ON DELETE CASCADE,
    PRIMARY KEY(_id AUTOINCREMENT),
    UNIQUE(name)
);

Sample Data

INSERT INTO Category
                  (_id, parent, name)
          SELECT     0,   NULL, 'cat0'
    UNION SELECT     1,   NULL, 'cat1' --< important bit
    UNION SELECT    11,      1,   'cat1-1'
    UNION SELECT    12,      1,   'cat1-2'
    UNION SELECT   121,     12,     'cat1-2-1'
    UNION SELECT   122,     12,     'cat1-2-2'
    UNION SELECT    13,      1,   'cat1-3'
    UNION SELECT   131,     13,     'cat1-3-1'
    UNION SELECT     2,   NULL, 'cat2' --< important bit
    UNION SELECT    21,      2,   'cat2-1'
    UNION SELECT     3,   NULL, 'cat3' --< important bit
;

Query

IRL I'm using much more complex ones involving views, sub-queries, multiple JOINs.

select
    c.*,
    (select count() from Category where parent = c._id) as count
from Category c
where c.parent = ? --< important bit
;

Wrong Code #1

public Cursor listCategoriesIn(SQLiteDatabase db, Long categoryID) {
    // public Cursor SQLiteDatabse.rawQuery(String sql, String[] selectionArgs);
    return db.rawQuery(CATEGORY_QUERY, new String[] {
        String.valueOf(categoryID)
    });
}

listCategoriesIn(1): works OK

listCategoriesIn(null): the resulting Cursor is empty, possibly = 'null' or = NULL is bound.

Wrong Code #2

public Cursor listCategoriesIn(SQLiteDatabase db, Long categoryID) {
    // public Cursor SQLiteDatabse.rawQuery(String sql, String[] selectionArgs);
    return db.rawQuery(CATEGORY_QUERY, new String[] {
        categoryID == null? null : categoryID.toString()
    });
}

listCategoriesIn(1): works OK

listCategoriesIn(null): java.lang.IllegalArgumentException: the bind value at index 1 is null