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

Question

How can I write a where in clause in an Android SQLite query?

Function to retrieve a single client

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); } 

Feature to Get 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.

Answer 1

 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); 

Answer 2

You can use the Android TextUtils join method to make a list of identifiers separated by commas for placement in the in section.

 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 use Longs to store them, not integers, otherwise it will overflow when the identifiers become large.

Answer 3

I want to add something to @ JosephL's answer according to my research:

I have two ArrayList with the following values:

First ArrayList (in the first column) have duplicate values, and Second ArrayList (in the second column) have unique values.

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

Printing both after processing and below:

• First array: "(" + TextUtils.join(",", arrayList1) + ")"
• Second array: "(" + TextUtils.join(",", arrayList2) + ")"

• First array (Delete duplicates using new HashSet<>(arrayList1) ):

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

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

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

  => First array (Delete duplicates using new HashSet<>(arrayList1) ): (67.70)

Hope this will be helpful. Thanks.