0

I have setup an application which currently can lookup an input id with one on the database to then give a single result. E.g. user enters id = 1 , database contains a record with an id of 1 then returns the name or number etc...

Now I want to improve the system slightly by querying my database with an arraylist which contains a range of id's e.g. 3, 456, 731 etc... which I want my database to search for. I have also grouped multiple values to certain id's for example the database might search for an id of 3 it will then find 5 results I want it to return the telephone number of each one of those results into another arraylist which I can print to the logs.

I hope I have explained this enough, but please ask questions if you require more information.

The code below demonstrates the modified version of the query used to gain a single result, but I cannot see what I'm doing wrong to gain multiple results.

Activity....

// New array list which is going to be used to store values from the database
ArrayList<String> contactsList;

// This arrayList has been received from another activity and contains my id's
ArrayList<String> contacts = intent.getStringArrayListExtra("groupCode");

// The database which i'm using
ContactDBHandler contactDBHandler = new ContactDBHandler(getApplicationContext(), null, null, 1);

//getAllValues is used to pass my arraylist id's to the database.
contactsList = contactDBHandler.GetAllValues(contacts);


// Simple log statement to loop and display results
for (int i = 0; i < contactsList.size(); i++){

        Log.i("Numbers", contactsList.get(i));

    }

ContactDBHandler

Query

// I'm telling it to get the contact number from the contact_list
// when the groupcode matches the code recieved.


public ArrayList<String> GetAllValues(ArrayList groupCode)
{
    SQLiteDatabase db = this.getReadableDatabase();
    Cursor cursor = null;
    String alarmName = "";
    ArrayList<String> list = new ArrayList<>();
    cursor = db.rawQuery("SELECT contact_number FROM contact_list WHERE grp_code=?", new String[]{groupCode+ ""});
    if (cursor.moveToFirst())
    {
        do
        {
            list.add(cursor.getString(0));
        }
        while (cursor.moveToNext());
    }
    if (cursor != null && !cursor.isClosed())
    {
        cursor.close();
    }

    return list;
}

Thanks Can you see where I have gone wrong?

user3403733
  • 79
  • 1
  • 7

2 Answers2

0

You cannot pass an ArrayList to an SQLQuery. To check for multiple values in the same field you have to use the 'in' keyword.

Ex:

SELECT * FROM `table1` where column in ( 'element1', 'element2', 'element3')

In your case,

String str = "";
for(String s: groupCode){
     str = str+","+"\'"+s+"\'";
}
//to remove the extra ' in the begining
str = str.substring(1);
return str;

cursor = db.rawQuery("SELECT contact_number FROM contact_list WHERE grp_code IN (?)", new String[]{str});
Aditya Desai
  • 415
  • 1
  • 6
  • 14
0

Try this: 

cursor = db.rawQuery("SELECT contact_number FROM contact_list WHERE grp_code IN (" + TextUtils.join(",", Collections.nCopies(groupCode.size(), "?")) + ")", groupCode.toArray(new String[groupCode.size()]));

Your current code fails to pass the list in the sql-format: = does only support single values, for lists you have to use IN.

Your code would result in a query like this:

SELECT contact_number FROM contact_list WHERE grp_code=["some_id","other_id"]

But what you need (and my code produces) is:

SELECT contact_number FROM contact_list WHERE grp_code IN ('some_id','other_id')

References:

Community
  • 1
  • 1
F43nd1r
  • 7,690
  • 3
  • 24
  • 62