Geek asks and answers

Choosing NULL values ​​in Android SQLite

I am executing the following method without success, because selectArgs is incorrect (at least this is what I consider.

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 with - null, 'null' and '' using db.query?

+5
source share
4 answers

The Android database API does not allow passing values NULLas parameters; it allows only strings. (This is a terrible design error. Even worse, SQLiteStatement allows all types of parameters to be used, but only works for queries that return a single value.)

You have no choice but to change the query string to blah IS NULL.

+11
source

, , . - ( ) sdk android, ,

SQLiteDatabase db = getReadableDatabase();
ContentValues contentValues = new ContentValues();

contentValues.put("columnName", newValue);

String nullSelection = "columnName" + " IS NULL";

db.update("tableName", contentValues, nullSelection, null);
db.close();

,

0

, "IS NULL" . ( delete , , ):

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 " = ? ";
    }
}
0

NULL 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();
0

More articles:


All Articles