WHERE IN clause in Android sqlite?

问题:

I can't get WHERE IN clause to work on android SQLite database.

Is there any way to execute a statement like this in android? :

SELECT body FROM table1 WHERE title IN ('title1', 'title2', 'title3')

回答1:

You can use TextUtils.join(",", parameters) to take advantage of sqlite binding parameters, where parameters is a list with "?" placeholders and the result string is something like "?,?,..,?".

Here is a little example:

Set<Integer> positionsSet = membersListCursorAdapter.getCurrentCheckedPosition();
List<String> ids = new ArrayList<>();
List<String> parameters = new ArrayList<>();
for (Integer position : positionsSet) {
    ids.add(String.valueOf(membersListCursorAdapter.getItemId(position)));
    parameters.add("?");
}
getActivity().getContentResolver().delete(
    SharedUserTable.CONTENT_URI,
    SharedUserTable._ID + " in (" + TextUtils.join(",", parameters) + ")",
    ids.toArray(new String[ids.size()])
);

回答2:

You will have to use the rawQuery method:

Cursor c = db.rawQuery("SELECT body FROM table1 WHERE title IN ('title1', 'title2', 'title3')");

回答3:

If you are using a content provider you can use the query function as such:

getContentResolver().query(URI,new String[] {"body"}, "title IN ?", new String[] {"('title1','title2','title3')"}, null)

Note that I have not tested this, but according to the documentation, this should work.

回答4:

Please, take a look at this answer. It helped me in the similiar case.

String[] names = { "name1", "name2" }; // do whatever is needed first
String query = "SELECT * FROM table" + " WHERE name IN (" + makePlaceholders(names.length) + ")";
Cursor cursor = mDb.rawQuery(query, names);

The main point here is to provide variable question-mark placeholders (?) to match your IN clause items. And of course - argument for them as String array.

回答5:

Another way of doing this

Cursor mCursor = SQLiteDatabase.query(true, "Name of table","String array of selection columns","title  in ('title1', 'title2', 'title3')", null, null, null, null, null);
if (mCursor != null) {
    mCursor.moveToFirst();
}

回答6:

You need to write rawquery that way -

 Cursor cursor = db.rawQuery("SELECT colunm1 FROM table WHERE colunm2 IN ('value1', 'value2', 'value3')", null);

回答7:

Adding to Cristian answer: While his answer will work, you should try to use the correct way of querying:

db.rawQuery("Select * from table1 where title = ?", new String[]{param1});

This approach can also be used with WHERE IN clause, but the passed string has to be in format of 'asd','zxc','qwe'

Like this:

String param1 = "\'asd\',\'zxc\',\'qwe\'";
db.rawQuery("Select * from table1 where title in (?)", new String[]{param1});