The most sophisticated way for creating comma-separated Strings from a Collection/Array/List?

Question

During my work with databases i noticed that i write query strings and in this strings i have to put several restrictions in the where-clause from a list/array/collection. Should looks like this:

select * from customer 
where customer.id in (34, 26, ..., 2);

You can simplify this by reducing this to the question that you have collection of strings and want to create a comma-separated list of this strings in just one string.

My approach i have used so far is something like that:

 String result = "";
 boolean first = true;
 for(String string : collectionOfStrings) {
  if(first) {
   result+=string;
   first=false;
  } else {
   result+=","+string;
  }
 }

But this is as you can see very ugly. You cannot see what happens there on the first look, especially when the constructed strings (like every SQL query) is getting complicated.

What is your (more) elegant way?

Answer 1

I'm not sure how "sophisticated" this is, but it's certainly a bit shorter. It will work with various different types of collection e.g. Set<Integer>, List<String>, etc.

public static final String toSqlList(Collection<?> values) {

    String collectionString = values.toString();

    // Convert the square brackets produced by Collection.toString() to round brackets used by SQL
    return "(" + collectionString.substring(1, collectionString.length() - 1) + ")";
}

Exercise for reader: modify this method so that it correctly handles a null/empty collection :)

Answer 2

There are some third-party Java libraries that provide string join method, but you probably don't want to start using a library just for something simple like that. I would just create a helper method like this, which I think is a bit better than your version, It uses StringBuffer, which will be more efficient if you need to join many strings, and it works on a collection of any type.

public static <T> String join(Collection<T> values)
{
 StringBuffer ret = new StringBuffer();
 for (T value : values)
 {
  if (ret.length() > 0) ret.append(",");
  ret.append(value);
 }
 return ret.toString();
}

Another suggestion with using Collection.toString() is shorter, but that relies on Collection.toString() returning a string in a very specific format, which I would personally not want to rely on.

Answer 3

Since strings are immutable, you may want to use the StringBuilder class if you're going to alter the String in the code. The StringBuilder class can be seen as a mutable String object which allocates more memory when its content is altered.

The original suggestion in the question can be written more clearly and efficiently, by taking care of the redundant trailing comma:

    StringBuilder result = new StringBuilder();
    for(String string : collectionOfStrings) {
        result.append(string);
        result.append(",");
    }
    return result.substring(0, result.length() - 1) ;

Answer 4

I think it's not a good idea contruct the sql concatenating the where clause values like you are doing :

SELECT.... FROM.... WHERE ID IN( value1, value2,....valueN)

Where valueX comes from a list of Strings.

First, if you are comparing Strings they must be quoted, an this it isn't trivial if the Strings could have a quote inside.

Second, if the values comes from the user,or other system, then a SQL injection atack is posible.

It's a lot more verbose but what you should do is create a String like this:

SELECT.... FROM.... WHERE ID IN( ?, ?,....?)

and then bind the variables with Statement.setString (nParameter,parameterValue)

Answer 5

I just looked at code that did this today. This is a variation on AviewAnew's answer.

collectionOfStrings = /* source string collection */;
String csList = StringUtils.join(collectionOfStrings.toArray(), ",");

FYI I believe the StringUtils we used is from Apache Commons.

Answer 6

The way I write that loop is:

    StringBuilder buff = new StringBuilder();
    String sep = "";
    for (String str : strs) {
        buff.append(sep);
        buff.append(str);
        sep = ",";
    }
    return buff.toString();

Don't worry about the performance of sep. An assignment is very fast. Hotspot tends to peel off the first iteration of a loop anyway (as it often has to deal with odities such as null and mono/bimorphic inlining checks).

If you use it lots (more than once), put it in a shared method.

There is another question on stackoverflow dealing with how to insert a list of ids into an SQL statement.

Answer 7

Use the Google Collections API join method:

Join.join(",", collectionOfStrings);

Answer 8

Just another method to deal with this problem. Not the most short, but it is efficient and gets the job done.

/**
 * Creates a comma-separated list of values from given collection.
 * 
 * @param <T> Value type.
 * @param values Value collection.
 * @return Comma-separated String of values.
 */
public <T> String toParameterList(Collection<T> values) {
   if (values == null || values.isEmpty()) {
      return ""; // Depending on how you want to deal with this case...
   }
   StringBuilder result = new StringBuilder();
   Iterator<T> i = values.iterator();
   result.append(i.next().toString());
   while (i.hasNext()) {
      result.append(",").append(i.next().toString());
   }
   return result.toString();
}

Answer 9

I found the iterator idiom elegant, because it has a test for more elements (ommited null/empty test for brevity):

public static String convert(List<String> list) {
    String res = "";
    for (Iterator<String> iterator = list.iterator(); iterator.hasNext();) {
        res += iterator.next() + (iterator.hasNext() ? "," : "");
    }
    return res;
}

Answer 10

You may be able to use LINQ (to SQL), and you may be able to make use of the Dynamic Query LINQ sample from MS. http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

Answer 11

I am surprised by your use of "AND" in the first example you gave. Are you sure you didn't mean "OR" instead?

The way you've written it, there should be no rows that meet the criterion.

Answer 12

You could try

List collections = Arrays.asList(34, 26, "...", 2);
String asString = collection.toString();
// justValues = "34, 26, ..., 2"
String justValues = asString.substring(1, asString.length()-1);

Answer 13

What makes the code ugly is the special-handling for the first case. Most of the lines in this small snippet are devoted, not to doing the code's routine job, but to handling that special case. And that's what alternatives like gimel's solve, by moving the special handling outside the loop. There is one special case (well, you could see both start and end as special cases - but only one of them needs to be treated specially), so handling it inside the loop is unnecessarily complicated.

Answer 14

There's a lot of manual solutions to this, but I wanted to reiterate and update Julie's answer above. Use google collections Joiner class.

Joiner.on(", ").join(34, 26, ..., 2)

It handles var args, iterables and arrays and properly handles separators of more than one char (unlike gimmel's answer). It will also handle null values in your list if you need it to.

Answer 15

Join 'methods' are available in Arrays and the classes that extend AbstractCollections but doesn't override toString() method (like virtually all collections in java.util)

for instance: String s= java.util.Arrays.toString(collectionOfStrings.toArray());

s = s.substing(1, s.length()-1);// [] are guaranteed to be there

---

that's quite weird way since it works only for numbers alike data sql wise.

Answer 16

I've just checked-in a test for my library dollar:

@Test
public void join() {
    List<Integer> list = Arrays.asList(1, 2, 3, 4, 5);
    String string = $(list).join(",");
}

it create a fluent wrapper around lists/arrays/strings/etc using only one static import: $.

NB:

using ranges the previous list can be re-writed as $(1, 5).join(",")

Answer 17

We can take help of toString() method of collection class :)

String temp = collectionOfStrings.toString()
String result  = temp.substring(1, temp.length()-1);