如何在PostgreSQL中将IN子句与PreparedStatement一起使用 [英] How to use IN clause with PreparedStatement in PostgreSQL

问题描述

我必须为Postgres数据库编写一个SQL查询，如下所示:

I have to write a SQL query for Postgres database as follows:

DELETE FROM employee WHERE ename IN (?)

我想将ename作为列表或字符串传递，其中将包含多个员工姓名，例如"abc, bcd, efg".如何设置值?

I want to pass ename as list or string which will contain multiple employee names, e.g. "abc, bcd, efg". How to set the values?

如何在Postgres中将IN子句与PreparedStatement一起使用?

How to use IN clause with PreparedStatement in Postgres?

推荐答案

有几种方法可以做到这一点.干净的方法是将数组传递给准备好的语句.尼克答案的另一种选择是传递适当的java.sql.Array值:

There are several ways to do this. The clean way is to pass an array to the prepared statement. An alternative to Nick's answer is to pass a proper java.sql.Array value:

PreparedStatement pstmt = connection.prepareStatement(
    "DELETE FROM employee WHERE ename = ANY (?)");
String[] idList = new String[] {"abc", "bcd", "efg"};
Array ids = connection.createArray("varchar", idList);
pstmt.setArray(1, ids);

另一个选择是使用Postgres的字符串函数将逗号分隔的列表转换为数组.那可能是最简单的方法:

Another option is to use Postgres' string functions to convert the comma separated list to an array. That is probably the easiest way:

PreparedStatement pstmt = connection.prepareStatement(
     "DELETE FROM employee WHERE ename = ANY (string_to_array(?, ','))");
psmt.setString(1, "abc,bcd,efg");

请注意，您在逗号和值之间一定不能有空格！这些空间将成为string_to_array()完成工作后存储在数组中的值的一部分，因此进行比较.是ename = ' bcd'，如果ename列中没有前导空格(很可能)

Note that you must not have spaces between the comma and the value! Those spaces will be part of the value that is stored in the array after string_to_array() has done it's job so comparison would e.g. be ename = ' bcd' which would fail if there is no leading space in the column ename(which is highly likely)

这篇关于如何在PostgreSQL中将IN子句与PreparedStatement一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！