Convert a String to pass multiple values in SQL WHERE IN clause in Java-CodePudding

I have a String parameter fruit which should accept comma separated string for example: "Banana, Apple, Orange". How do I split this String into 3 data like "Banana", "Apple", "Orange" so it can be passed to my SQL below:

StringBuilder sql = new StringBuilder(
"SELECT * FROM fruit f where ");

 if (!inputParameters.getFruit().isEmpty()) {
   sql.append("f.name IN (:fruit) ");
   parameterSource.addValue("fruit", inputParameters.getFruit());
 ) 
}

Here, I want to pass the value of getFruit in the SQL IN clause which is already converted into 3 data: "Banana", "Apple", "Orange" rather than only 1 String data "Banana, Apple, Orange"

CodePudding user response：

The simplest way to do this is passing the whole "Banana, Apple, Orange" string to postgres and letting postgres explode the string via string_to_array and unnest():

StringBuilder sql = new StringBuilder(
"SELECT * FROM fruit f where ");

if (!inputParameters.getFruit().isEmpty()) {
  sql.append("f.name IN (SELECT UNNEST(string_to_array(:fruit, ', ')) ");
  parameterSource.addValue("fruit", inputParameters.getFruit());
  ) 
}

string_to_array will explode the :fruit string into an array, and UNNEST will transform that array into a recordset suitable for IN

CodePudding user response：

In SQL, string literals need to be enclosed in single quote characters. Hence you want to replace :fruit (in the text of your SQL query) with the following

'Banana','Apple','Orange'

In other words you don't need to split the string that is returned by method getFruit, you just need to change it a little. The following code does that.

String fruit = "Banana, Apple, Orange"; // value returned from method `getFruit`
String sql = "SELECT * FROM fruit f where f.name IN (:fruit)";
System.out.println(sql.replaceFirst(":fruit", fruit.replaceAll("([A-Z][a-z] )", "'$1'")));

Running the above code produces the following:

SELECT * FROM fruit f where f.name IN ('Banana', 'Apple', 'Orange')

CodePudding user response：

I think this could help you :

String oneStringFruit = "Banana, Apple, Orange";
StringBuilder sql = new StringBuilder("SELECT * FROM fruit f where f.name IN (");
String[] fruits = oneStringFruit.split(",");

Set.of(fruits).forEach(fruit -> sql.append("'").append(fruit.trim()).append("', "));
sql.replace(sql.lastIndexOf(","), sql.lastIndexOf(",")   1, ")");
System.out.println(sql);

Enjoy!