Reputation: 2678
I'm executing the following method with no success beacause of the selectArgs being incorrect (at least this is what I believe.
findAll:
public Collection<Object> findAllByCodigoSetorOrderByStatusWhereDataAgendamentoIsNull(Integer vendedor) {
Collection<Object> objects = null;
String selection = Object.FIELDS[20] + "=?" + " OR " + Object.FIELDS[20] + "=?" + " OR " + Object.FIELDS[20] + "=?" + " AND " + Object.FIELDS[6] + "=?";
String[] selectionArgs = new String[] { "''", "'null'", "NULL", String.valueOf(vendedor) };
Collection<ContentValues> results = findAllObjects(Object.TABLE_NAME, selection, selectionArgs, Object.FIELDS, null, null, Object.FIELDS[4]);
objects = new ArrayList<Object>();
for (ContentValues result : results) {
objects.add(new Object(result));
}
return objects;
}
findAllObjects:
protected Collection<ContentValues> findAllObjects(String table, String selection, String[] selectionArgs, String[] columns, String groupBy, String having, String orderBy) {
Cursor cursor = null;
ContentValues contentValue = null;
Collection<ContentValues> contentValues = null;
try {
db = openRead(this.helper);
if (db != null) {
cursor = db.query(table, columns, selection, selectionArgs, groupBy, having, orderBy);
contentValues = new ArrayList<ContentValues>();
for (int i = 0; i < cursor.getCount(); i++) {
cursor.moveToPosition(i);
contentValue = new ContentValues();
for (int c = 0; c < cursor.getColumnCount(); c++) {
contentValue.put(cursor.getColumnName(c), cursor.getString(c));
}
contentValues.add(contentValue);
cursor.moveToNext();
}
}
return contentValues;
} finally {
close(db);
}
}
How can I correctly select and compare a column to - null, 'null' and '' using the db.query?
Upvotes: 6
Views: 6317
Reputation: 5215
You can bind NULL values to SQLiteStatement:
SQLiteDatabase db = getWritableDatabase();
SQLiteStatement stmt = db.compileStatement("UPDATE table SET " +
"parameter=? WHERE id=?");
if (param == null)
stmt.bindNull(1);
else
stmt.bindString(1, param);
stmt.execute();
stmt.close();
db.close();
Upvotes: 0
Reputation: 541
As mentioned in other answers, for null "IS NULL" need to be used. Here is some convenience code for having both null and strings (I'm using delete in the example but the same can be done for other methods, e.g. query):
public void deleteSomething(String param1, String param2, String param3) {
ArrayList<String> queryParams = new ArrayList<>();
mDb.delete(TABLE_NAME,
COLUMN_A + getNullSafeComparison(param1, queryParams) + "AND " +
COLUMN_B + getNullSafeComparison(param2, queryParams) + "AND " +
COLUMN_C + getNullSafeComparison(param3, queryParams),
queryParams.toArray(new String[0]));
}
private String getNullSafeComparison(String param, List<String> queryParams) {
if (param == null) {
return " IS NULL ";
} else {
queryParams.add(param);
return " = ? ";
}
}
Upvotes: 0
Reputation: 5460
Old question but i was still stuck on this for a few hours until i found this answer. For whatever reason this strange behaviour (or bug) still exists within the android sdk, if you want to query against null values simply do
SQLiteDatabase db = getReadableDatabase();
ContentValues contentValues = new ContentValues();
contentValues.put("columnName", newValue);
String nullSelection = "columnName" + " IS NULL";
db.update("tableName", contentValues, nullSelection, null);
db.close();
In this example i am updating values, but it is a similar concept when just selecting values
Upvotes: 1
Reputation: 180310
Android's database API does not allow to pass NULL
values as parameters; it allows only strings.
(This is a horrible design bug. Even worse, SQLiteStatement does allow all types for parameters, but works only for queries that return a single value.)
You have no choice but to change the query string to blah IS NULL
.
Upvotes: 16