Java method to return String that can be used in SQL IN statement

Question

I have a comma separated string that I need to convert in order to use it in an SQL IN statement e.g SELECT * FROM table WHERE filedvalue IN(conversion)

Below is the method I have come up with so far. The value being passed for Parameter parameter is GBL075,GBL008

public String paramconverter(String parameter)
    {
        String conversion=""; 
        String newstring="";


        String[] parts = parameter.split(",");

            for(String part : parts)
            {                

               newstring = newstring.concat("'"+part+"',");
            }  

            conversion = new StringBuilder().append(newstring).toString();
            int ind = conversion.lastIndexOf(",");
            conversion = new StringBuilder(conversion).delete(ind,ind+1).toString();
            System.out.println(conversion);



        return conversion;

    }

However when I read the console output I get the below results for Variable conversion

'GBL075','GBL008' 'GBL075','GBL008'

This obviously does not work in the sql statement. I need to correct this method and I need assistance. Thanks.

score 0 · Answer 1 · edited May 23 '17 at 12:05

0

In Java 8 they added StringJoiner class:

StringJoiner joiner = new StringJoiner(", ");
joiner.add("foo");
joiner.add("bar");
joiner.add("baz");
String joined = joiner.toString(); // "foo, bar, baz"

Check this question, they offer different third party libs

edited May 23 '17 at 12:05

Community

1
1

answered Jun 08 '14 at 06:27

Uriil

11,948
11
47
68

score 0 · Answer 2 · answered Jun 08 '14 at 06:30

A SQL variable contains a single value, not a list of values.

Some databases allow for lists to be passed (for example as a custom data type), but that is unusual and typically complex.

The usual solution is the build a SQL string without parameters. This feels wrong, but there is no better solution. For example:

sql = "select * from tbl where name in ("
for(int i = 0; i < array.length; i++)
{
    if (i != 0)
        sql += ", ";
    sql += "'" + StringEscapeUtils.escapeSql(name) + "'";
}
sql += ");"

Note the use of escapeSql so the code works for names with a single quote.

Alex Pakka · Answer 3 · 2014-06-08T06:59:31.280

Typical way of solving your problem (would be a beginner Java developer's solution):

        String newstring="";
        String[] parts = parameter.split(",");

        for(String part : parts)
        {                
           if (!newstring.isEmpty()) newstring += ",";
           newstring += "'" + part + "'";
        }  

        System.out.println(newstring);

        return newstring;

There are more efficient ways of doing it. And, of course, you should escape single quotes, if you do not use prepared statements.

If you were asking me, I would do this at least (kind of itermediate level, but add parameter validation / NPE checks and you will see it in a lot of code):

//this class should be put for later re-use in the code
public class StringUtils {

    public static String[] escapeAndQuote(String[] arr) {
       String[] ret = new String[arr.length];
       for (int i=0; i<arr.length; i++) {
           ret[i] = "'" + arr[i].replace("'","''")+"'";
       }
       return ret;
    }

    public static String join(String delim, String[] arr) {
       if (arr.length == 0) return "";
       StringBuilder ret = new StringBuilder(arr[0]);
       for (int i=1; i<arr.length; i++) {
           ret.append(delim).append(arr[i]);
       }
       return ret.toString();
    }
}

...

//this is how you use it
public String paramconverter(String parameter)
{
   return StringUtils.join(",",StringUtils.escapeAndQuote(parameter.split(",")));
}

Even better way would be to do a monadic wrapper a-la jQuery, but it only pays off if you do a lot of stuff with Strings.

Last, but not least, if you know there are only 2 values, parametrized SQL query (prepared statement) is a way to go.

I do not know the number of values that can be passed hence the need to create this method to do this auto conversion — MaxI, Jun 08 '14 at 06:36

score 0 · Answer 4 · answered Jun 08 '14 at 06:44

public String paramconverter(String parameter){
    StringTokenizer token = new StringTokenizer(parameter,",");
    StringBuilder builder = new StringBuilder();
    while(token.hasNext()){
        builder.append("'");
        builder.append(token.next());
        builder.append("'");
        if(builder.hasNext()){
            builder.append(",");
        }
    }
    return builder.toString();
}

OR

public String paramconverter(String parameter){
    String res = ("'"+parameter+"'").replace(",","','");
    return res;
}

Java method to return String that can be used in SQL IN statement

4 Answers4