Passing an array of values in a SELECT statement in android

Question

I have a select statement which selects diseases in sqlite database based on symptoms dynamically generated by user... Here is the query: SELECT D."+COLUMN_NAME+",group_concat( symp,',' ) AS conca,count(*) as SymptomsMatching FROM "+TABLE_LINKDS+ " DS JOIN "+TABLE_SYMPTOMS+" S ON DS."+COLUMN_SYMPTOMS_ID+"= S."+COLUMN_SYM_ID+ " JOIN "+TABLE_DISEASE+" D ON DS."+COLUMN_DISEASE_ID+"= D."+COLUMN_DS_ID+ " WHERE S."+COLUMN_SYMP+" IN ("+arrayOfSymptoms+") GROUP BY D.name ORDER BY SymptomsMatching DESC, D.name ASC";

Now i want to pass the array:String[] arrayOfSymptoms={"headache","nausea","chills"} in the query such that the query would look something like this :

"SELECT D."+COLUMN_NAME+",group_concat( symp,',' ) AS conca,count(*) as SymptomsMatching FROM "+TABLE_LINKDS+
            " DS JOIN "+TABLE_SYMPTOMS+" S ON DS."+COLUMN_SYMPTOMS_ID+"= S."+COLUMN_SYM_ID+
            " JOIN "+TABLE_DISEASE+" D ON DS."+COLUMN_DISEASE_ID+"= D."+COLUMN_DS_ID+
            " WHERE S."+COLUMN_SYMP+" IN ('headache','nausea','chills') GROUP BY D.name ORDER BY SymptomsMatching DESC, D.name ASC";

I tried using TextUtils.join() method and another custom but all dint work.

thyago stall · Accepted Answer · 2017-01-11T00:54:06.190

0

You can use this function:

public static String toArrayRep(String[] in) {
    StringBuilder result = new StringBuilder();
    for (int i = 0; i < in.length; i++) {
        if (i != 0) {
            result.append(",");
        }
        result.append("'" + in[i] + "'");
    }
    return result.toString();
}

And then you call it inside your SQL string.

edited Jan 11 '17 at 00:54

answered Jan 11 '17 at 00:19

thyago stall

1,654
3
16
30

it came out with this error :android.database.sqlite.SQLiteException: near "','": syntax error (code 1): , while compiling: SELECT D.name,group_concat( symp,',' ) AS conca,count(*) as SymptomsMatching FROM linkds DS JOIN symptoms S ON DS.symptomId= S.id JOIN disease D ON DS.diseaseId= D.id WHERE S.symp IN (headache,nausea) GROUP BY D.name ORDER BY SymptomsMatching DESC, D.name ASC... – mcprilla79 Jan 11 '17 at 00:42
seems we are getting close.. is there a way to modify the code to put single quotes around any symptom, like : 'headache','nausea'.. that will do the trick – mcprilla79 Jan 11 '17 at 00:44
I have made an edit to this answer to make it work. just take it to `length-1` and do `append("'"+in[i]+"'")`. – Nitesh Verma Jan 11 '17 at 00:52
@NiteshVerma if you change to `length - 1` it doesn't include the last element. – thyago stall Jan 11 '17 at 00:57
thanx man the function with for (int i = 0; i < in.length; i++) worked.. thanx so much – mcprilla79 Jan 11 '17 at 01:12
@thyagostall you are right. I thought error `SQLiteException: near "','": syntax error (code 1)` was caused due to trailing comma. Glad it worked. – Nitesh Verma Jan 11 '17 at 01:34

score 0 · Answer 2 · answered Jan 11 '17 at 01:00

Something like this

public static String fromArray(String[] in) {
    StringBuilder result = new StringBuilder();
    for (int i = 0; i < in.length - 1; i++) {
        if (i != 0) {
            result.append(",");
        }
        result.append("'" + in[i] + "'");
    }
    return result.toString();
}

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

-1

You can use a simple toString() method on the array and remove the preceding and trailing .

Check this SO link (i think similar question answered) -

Android - sqlite in clause using values from array

edited May 23 '17 at 12:07

Community

1
1

answered Jan 11 '17 at 00:09

Dibzmania

1,934
1
15
32

that will give u an error, besides it will logically look like "headache,nausea,chills" but i want it in the form 'headache','nausea','chills' to make the query work – mcprilla79 Jan 11 '17 at 00:20
@mcprilla79 The single quotes are required so that SQLIte is not confused with the input. This can also be achieved as below although it will put a single quote around each string but it will put on the whole input as a whole - 'DatabaseUtils.sqlEscapeString(TextUtils.join("," , array))' – Dibzmania Jan 11 '17 at 01:13

Passing an array of values in a SELECT statement in android

3 Answers3