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.

JosephL · Accepted Answer · 2012-03-12 22:17:12Z

16

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.

answered Mar 12, 2012 at 22:17

JosephL

5,9831 gold badge32 silver badges25 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

uyuyuy99 · Accepted Answer · 2014-03-22 04:57:55Z

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, 2014 at 4:57

uyuyuy99

3534 silver badges11 bronze badges

answered Aug 19, 2011 at 9:03

Rasel

15.5k6 gold badges42 silver badges50 bronze badges

1 Comment

Mithun Sreedharan Over a year ago

sorry, id list may be like (11, 103, 6, 100)

Community · Accepted Answer · 2017-05-23 12:09:33Z

1

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.

edited May 23, 2017 at 12:09

CommunityBot

11 silver badge

answered Nov 18, 2016 at 6:57

Pratik Butani

62.6k63 gold badges289 silver badges453 bronze badges

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related