How to write WHERE IN clause from a dynamic ArrayList for Android SQLite query

Question

How can i write the where in clause in Android SQLite query?

Function to retrieve a single customer

public Cursor getCustomers(int groupId) {
    return db.query(TABLE_CUSTOMERS, new String[] { KEY_CUSTOMER_ID, KEY_NAME}, KEY_GROUP_ID+" = "+groupId, null, null, null, null);
}

Function to retrieve a more customers

public Cursor getCustomers(ArrayList<Integer> groupIds) {
    // Need to apply SELECT id, name FROM customers WHERE id IN (11, 13, ...18);
    //return db.query(TABLE_CUSTOMERS, new String[] { KEY_CUSTOMER_ID, KEY_NAME}, KEY_GROUP_ID+" = "+groupIds, null, null, null, null);
}

The size of the groupId ArrayList is dynamic.

score 16 · Answer 1 · answered Mar 12 '12 at 22:17

You can use the Android TextUtils class join method to make a comma separated list of IDs to put in the in clause.

String selection = KEY_GROUP_ID + " IN (" + TextUtils.join(", ", groupIds) + ")";
return db.query(TABLE_CUSTOMERS, new String[] { KEY_CUSTOMER_ID, KEY_NAME}, selection, null, null, null, null);

BTW if groupIds is a list of primary keys from another table you should be using Longs to store them not Integers otherwise it will overflow when the IDs get large.

score 4 · Accepted Answer · edited Mar 22 '14 at 04:57

4

return db.query(TABLE_CUSTOMERS, new String[] { KEY_CUSTOMER_ID, KEY_NAME }, KEY_GROUP_ID + " >=" + groupIdsLowLimit + " and " + KEY_GROUP_ID + " <=" + groupIdsUpLimit, null, null, null, null);

String where = "";
for (int i = 0; i < list.size(); i++) {
    where = where + KEY_GROUP_ID + " =" + list.get(i).toString() + "";
    if (i != (list.size() - 1))
        where = where + " or";
}

return db.query(TABLE_CUSTOMERS, new String[] { KEY_CUSTOMER_ID, KEY_NAME }, where, null, null, null, null);

edited Mar 22 '14 at 04:57

uyuyuy99

353
4
11

answered Aug 19 '11 at 09:03

Rasel

15,499
6
40
50

sorry, id list may be like (11, 103, 6, 100) – Mithun Sreedharan Aug 19 '11 at 09:05

score 1 · Answer 3 · edited May 23 '17 at 12:09

I want to add something in @JosephL's answer as per my study:

I have two ArrayList with following values:

First ArrayList (in First Column) have duplicate values and Second ArrayList (in Second Column) has unique values.

=> 67 : 35
=> 67 : 36
=> 70 : 41
=> 70 : 42

Printing both after processing as below:

First Array : "(" + TextUtils.join(",", arrayList1) + ")"
Second Array : "(" + TextUtils.join(",", arrayList2) + ")"
First Array (Remove Duplicates using new HashSet<>(arrayList1)):

"(" + TextUtils.join(",", new HashSet<>(arrayList1)) + ")"

=> First Array : (67,67,70,70)

=> Second Array : (35,36,41,42)

=> First Array (Remove Duplicates using new HashSet<>(arrayList1)): (67,70)

Hope it will useful. Thanks.

How to write WHERE IN clause from a dynamic ArrayList for Android SQLite query

3 Answers3