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-11 00:54:06Z

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, 2017 at 0:54

answered Jan 11, 2017 at 0:19

thyago stall

1,7043 gold badges17 silver badges31 bronze badges

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

6 Comments

mcprilla79 Over a year ago

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 Over a year ago

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

Nitesh Verma Over a year ago

I have made an edit to this answer to make it work. just take it to length-1 and do append("'"+in[i]+"'").

thyago stall Over a year ago

@NiteshVerma if you change to length - 1 it doesn't include the last element.

mcprilla79 Over a year ago

thanx man the function with for (int i = 0; i < in.length; i++) worked.. thanx so much

|

CodyEngel · Accepted Answer · 2017-01-11 01:00:14Z

0

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

answered Jan 11, 2017 at 1:00

CodyEngel

1,50114 silver badges22 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:07:19Z

-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, 2017 at 12:07

CommunityBot

11 silver badge

answered Jan 11, 2017 at 0:09

Dibzmania

2,0241 gold badge18 silver badges33 bronze badges

2 Comments

mcprilla79 Over a year ago

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

Dibzmania Over a year ago

@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))'

Collectives™ on Stack Overflow

Passing an array of values in a SELECT statement in android

3 Answers 3

6 Comments

Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related