How to join list items using ',' using streams with 'as prefix and suffix

Question

I have an insert statement as shown below

private final COLUMNS = "NAME,TYPE,STATUS,CATEGORY"; String values = list .stream() .collect(Collectors.joining(",")); String insertStatement = String.format("INSERT INTO ifc.documents (%s) VALUES (%s) ",COLUMNS,values); 

I can easily put COLUMNS in quotation marks, but not for values, my SQL does not work and complains about missing quotes for the code above.

So i tried

  String values = list.stream() .collect(Collectors.joining("','")); 

But he fails with that. So I made a workaround and added another statement, prefix and suffix with one quote, and it started working.

  values = "'"+values+"'"; 

To be more specific, if I say “rest”, “test” and “best” in the list

then the expected result

 'rest','test','best' 

Does anyone know a better solution for this?

Answer 1

In fact, you can use Collectors.joining(CharSequence delimiter,CharSequence prefix,CharSequence suffix) , and you can look here for the API.

 String values = list.stream().collect(Collectors.joining("','", "'", "'")); 

Answer 2

You can use the Collectors.joining overload , which accepts prefix and suffix parameters.

  String values = list.stream() .collect(Collectors.joining("','", "'", "'")); 

This will put single quotes around all of your values ​​correctly, assuming all of your values ​​are strings that need single quotes for syntax.

But, as with all the problems associated with constructing a query by combining values, it leaves you vulnerable to SQL injection. A safer and more universal solution involves using PreparedStatement . The placeholders are ? characters, and you can use various setXyz methods to safely store values. It protects against SQL injection and allows you to use any data types, not just row types.

Answer 3

Do not create the "values" part using string concatenation, as it opens up the possibility for SQL Injection attacks.

I would use a prepared expression here. You can create your request as follows:

 List<String> columns = Arrays.asList("column1", "column2", "column3"); String columnsFragment = columns.stream().collect(Collectors.joining(",")); String placeholdersFragment = columns.stream().filter(s -> "?").collect(Collectors.joining(",")) String insertStatement = String.format("INSERT INTO ifc.documents (%s) VALUES (%s) ", columnsFragment, placeholdersFragment); 

And then use insertStatement with PreparedStatement :

 PreparedStatement st = connection.prepareStatement(insertStatement); for (int i = 0; i < values.size(); i++) { // +1 because prepared statement parameters indices are 1-based st.setString(i + 1, values.get(i)); } st.executeUpdate(); 

In this case, the resulting query will look like

 INSERT INTO ifc.documents (column1, column2, column3) VALUES (?, ?, ?) 

Answer 4

Another solution for String.join similar to this

 String result = "'"+String.join("','", list)+"'"; Function<List<String>,String> function = list2->"'".concat(String.join("','",list2)).concat("'"); System.out.println(function.apply(list)); 

or use reduction like this

 String result = "'"+list.stream().reduce((s, p) -> s + "','" + p).get()+"'";