在Java的SQL WHERE in子句中将字符串转换为传递多个值

Question

我有一个字符串参数fruit，应该接受逗号分隔的字符串，例如:"Banana, Apple, Orange"。我如何把这个字符串分割成3个数据像"Banana", "Apple", "Orange"，所以它可以传递给我的SQL下面:

StringBuilder sql = new StringBuilder(
"SELECT * FROM fruit f where ");
if (!inputParameters.getFruit().isEmpty()) {
sql.append("f.name IN (:fruit) ");
parameterSource.addValue("fruit", inputParameters.getFruit());
) 
}

这里，我想在SQL IN子句中传递getFruit的值，该子句已经转换为3个数据:"Banana", "Apple", "Orange"而不仅仅是1个字符串数据"Banana, Apple, Orange"

Answer 1

最简单的方法是将整个"Banana, Apple, Orange"字符串传递给postgres，并让postgres通过string_to_array和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将把:fruit字符串爆炸成一个数组，UNNEST将把该数组转换成一个适合IN的记录集

Answer 2

在SQL中，字符串字量需要用单引号括起来。因此，您希望将:fruit(在SQL查询的文本中)替换为以下

'Banana','Apple','Orange'

换句话说，您不需要拆分方法getFruit返回的字符串，您只需要稍微改变它。下面的代码就是这样做的。

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'")));

运行上面的代码产生如下结果:

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

Answer 3

我想这对你有帮助:

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);

享受吧!