How to pass the comma separated string value in sql query?

Question

I create one database related app.

In this i create simple database and get the value from database using in in sql query.

My problem is there i got the on string like a "2,6,8,9,10" this. first i split this string and store in one arraylist. with out comma. after this arraylist i merge with comma like a 2,6,8,9,10 this.

i do all of this very well. i use this string in query to get the value from database. My query is

SELECT tm.icon,tm.topic_no,td.topic_id,td.name FROM topicmaster tm ,topicmaster_description td  WHERE td.topic_id =tm.topic_id AND  td.langid='3' AND tm.oraganisationid ='1' AND  td.topic_id in(2,6,8,9,10);

this.

but when i pass the merge string it`s look like a

SELECT tm.icon,tm.topic_no,td.topic_id,td.name FROM topicmaster tm ,topicmaster_description td  WHERE td.topic_id =tm.topic_id AND  td.langid='3' AND tm.oraganisationid ='1' AND  td.topic_id in('2,6,8,9,10');

so i can not run the query because the different of pass value in in cause. in above query work fine because its like ain(2,6,8,9,10)in second it look like ain('2,6,8,9,10')so what to do. how to remove this'` from string?

First i split String

//getTopicFile ArrayList with 
    public  ArrayList<String> getAssignTopicArrayList(String passString) 
    {
        ArrayList<String> rtnArrayList=new ArrayList<String>();
        try 
        {
            StringTokenizer strTokens = new StringTokenizer(passString,",");
            while (strTokens.hasMoreElements()) 
            {
                rtnArrayList.add(String.valueOf(strTokens.nextToken()));
            }
        } catch (Exception ex) 
        {
            ex.printStackTrace();
            System.err.println("Error in Fetch ArrayList-->"+ex.toString());
        }
        return rtnArrayList;
    }       
    //getTopicFile ArrayList with

After i merge in New String

genHelper.showErrorLog("Assign Topic-->"+chapterAssignTopicName);
        ArrayList<String> getTopicList=genHelper.getAssignTopicArrayList(chapterAssignTopicName);

        String passConcat = "";
        for (int i = 0; i < getTopicList.size(); i++) 
        {
            System.out.println("Topic List--->"+getTopicList.get(i));

            if(getTopicList.size()==1)
            {
                passConcat=passConcat.concat(String.valueOf(getTopicList.get(i)));
            }
            else
            {
                if(getTopicList.size()-1 == i)
                {
                    System.err.println("value if --> " + i);
                    passConcat=passConcat.concat(String.valueOf(getTopicList.get(i)));
                }else {
                    System.err.println("value else--> " + i);
                    passConcat=passConcat.concat(String.valueOf(getTopicList.get(i)).concat(","));
                }
            }
        }

        genHelper.showErrorLog("PassConcat String-->"+passConcat);

then i got New String and pass in query it` below

String topicQuery="SELECT tm.icon,tm.topic_no,td.topic_id,td.name FROM topicmaster tm ,topicmaster_description td  WHERE td.topic_id =tm.topic_id AND  td.langid='"+LanguageActivity.languageId+"' AND tm.oraganisationid ='"+genHelper.loadPreferences(String.valueOf(R.string.sharePrefin_Login_organizationid))+"' AND  td.topic_id in('"+passConcat+"')";

take a look to that post http://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives — nano_nano, Nov 29 '12 at 12:23

score 2 · Accepted Answer · answered Nov 29 '12 at 12:32

should not

String topicQuery="SELECT tm.icon,tm.topic_no,td.topic_id,td.name FROM topicmaster tm ,topicmaster_description td  WHERE td.topic_id =tm.topic_id AND  td.langid='"+LanguageActivity.languageId+"' AND tm.oraganisationid ='"+genHelper.loadPreferences(String.valueOf(R.string.sharePrefin_Login_organizationid))+"' AND  td.topic_id in('"+passConcat+"')";

be

String topicQuery="SELECT tm.icon,tm.topic_no,td.topic_id,td.name FROM topicmaster tm ,topicmaster_description td  WHERE td.topic_id =tm.topic_id AND  td.langid='"+LanguageActivity.languageId+"' AND tm.oraganisationid ='"+genHelper.loadPreferences(String.valueOf(R.string.sharePrefin_Login_organizationid))+"' AND  td.topic_id in("+passConcat+")";

Thank Dear your answer slove my problem. so +1 for this dear — Zala Janaksinh, Nov 29 '12 at 12:39

score 0 · Answer 2 · answered Nov 29 '12 at 12:28

You best option, assuming you are using prepared statements, and you want to keep your db secure, is to prepare the statement with the sql string '.... in (?,?,?,...)' where the number of '?' equals the number of actual values you have in your array.

the downside is that you sacrifice performance, because you need to prepare the statement every time you wish to execute it.

score 0 · Answer 3 · answered Nov 29 '12 at 12:32

0

Use some sting operations like indexOf and replace functions to get rid of these commas.

answered Nov 29 '12 at 12:32

Ali Imran

8,927
3
39
50

Dear problem is how to remove 'and ' from string – Zala Janaksinh Nov 29 '12 at 12:34

How to pass the comma separated string value in sql query?

3 Answers3