Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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+"')";
like image 343
Zala Janaksinh Avatar asked Nov 03 '22 10:11

Zala Janaksinh


1 Answers

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+")";
like image 61
Gaurav Vashisth Avatar answered Nov 12 '22 15:11

Gaurav Vashisth