Android Sqlite IN, NOT IN syntax

I'm trying to run NOT IN select where NOT IN list is dynamic. Something like

SELECT id, type FROM CONTACTS where type NOT IN ('connect','answer')

In the code my futile attempts were:

db.query(TABLE,开发者_C百科 new String[] { "id", ""}, " type NOT IN (?)", "connect,answer", 
    null, null, null); // didn't work
db.query(TABLE, new String[] { "id", ""}, " type NOT IN (?)", "'connect','answer'", 
null, null, null); // didn't work

My take on this is that ? substitution treats my comma list as single argument. I did find a solution but it is rather ugly so I won't post it here until someone comes forward with something more elegant

---

You cannot place just one '?' instead of a list of values. As such, there is little to gain from trying to parametrize lists. One can, of course, create 2,4,16-value prepared statements ..." type NOT IN (?,?)", new String[]{ "connect","answer" },... but even on a server with remote RDBMS it has questionable value. Instead, do

db.query(TABLE, new String[] { "id", ""}, " type NOT IN ('connect','answer')", 
     null, null, null, null);

if the list is dynamic, you will have to escape the strings and put them into single quoted list.

---

You can use String.format to dynamic set the args.

for example:

db.query(TABLE, new String[] { "id", ""}, " type NOT IN (?)", "connect,answer", null, null, null);

=>

String argsArrayToString(List<String> args) {
    StringBuilder argsBuilder = new StringBuilder();
    argsBuilder.append("(");
    final int argsCount = args.size();
    for (int i=0; i<argsCount; i++) {
        argsBuilder.append(args.get(i));
        if (i < argsCount - 1) {
            argsBuilder.append(",");
        }
    }
    argsBuilder.append(")");
}

db.query(TABLE, new String[] { "id", ""}, String.format(" type NOT IN (%s)", argsArrayToString(arrays)), null, null, null, null);

---

You can have it dynamically use "not in" if you dynamically create a String that contains the list of "not in" values and then append them to the end of your sql string like in the following:

public static Vector selectAllFormTypesForAccountIgnoringFormVersions(
        int accountId, String formsIgnored) {
protected final static String selectAllFormTypesForAccountIgnoringFormVersions = "SELECT DISTINCT FormType.*"
        + " FROM FormType, Form WHERE Form.accountId=? AND FormType.formTypeContentId = Form.formTypeContentId"
        + " AND Form.formVersionId NOT IN (";

    Vector allFormTypes = new Vector();
    SQLiteDatabase db = null;
    String[] selectionArgs = { Integer.toString(accountId)};

    try {
        DataManager_Platform dataManager = (DataManager_Platform) DataManager_Platform
                .getDataManager();
        db = (SQLiteDatabase) dataManager.getDatabase();
        Cursor cursor = db.rawQuery(
                selectAllFormTypesForAccountIgnoringFormVersions + formsIgnored + ")",
                selectionArgs);

        if (cursor.getCount() > 0) {
            cursor.moveToFirst();
            while (!cursor.isAfterLast()) {
                FormType_Platform formType = new FormType_Platform();
                formType.populateModel(cursor);
                allFormTypes.add(formType);
                cursor.moveToNext();
            }
        }
    } catch (Exception e) {
        System.out.println("Error: " + e.getMessage());
        e.printStackTrace();
    } finally {
        try {
            if (db != null) {
                db.close();
            }
        } catch (Exception e) {
            System.out.println("Error: " + e.getMessage());
            e.printStackTrace();
        }
    }

    return allFormTypes;
}