SQLite - Querying a db where selectionArgs contains an array

Question

I have an array userNames which contains list of names and its not predetermined. I have to query a database which returns only the rowIDs which have any of the username from the array only if their availability is true. I think I can achieve this by using the code below but in my opinion its not efficient to use it.

ArrayList<Long> rowIDs = new ArrayList<Long>();

for (int i = 0 ; i < userNames.length ; i++) {
   String selection = Columns.NAME + "=? and " + Columns.AVAILABILITY + "=?";
   String[] selectionArgs = { userNames[i], true };
   Cursor c = getContentResolver().query(Columns.URI,null,selection,selectionArgs,null);
   if (c.getCount() > 0) {
      rowIDs.add(c.getLong(c.getColumnIndex(Columns.ID)));
   }
}

userNames in the array containing usernames. As it shows, db will be accessed again and again which isn't efficient especially if there are many user names. If possible, provide an efficient way to achieve it.

score 0 · Answer 1 · answered May 21 '14 at 05:52

I donno where you require the thing.... I think we can use this String query="SELECT * FROM users WHERE username IN (?,?,.......)"; Cursor cur=db.rawQuery(query,new String[]{'name1','name2',....}); directly in the query. This is one method i remember.

But if you find something good do let me know too...

thx

score -1 · Answer 2 · answered May 21 '14 at 05:59

Try this:
ArrayList rowIDs = new ArrayList(); String users=""; for (int i = 0 ; i < userNames.length ; i++) { users=users+","+userNames[i]; } String selection = Columns.NAME + " in (?) and " + Columns.AVAILABILITY + "=?"; String[] selectionArgs = { users[i], true }; Cursor c = etContentResolver().query(Columns.URI,null,selection,selectionArgs,null); if (C.moveToFirst()) { do{ rowIDs.add(c.getLong(c.getColumnIndex(Columns.ID))); }while(c.moveToNext()); } You can also use like instead of IN

SQLite - Querying a db where selectionArgs contains an array

2 Answers2