如何从PostgreSQL中删除UUID在数组列表中的一些行?

jq6vz3qz  于 2023-03-29  发布在  PostgreSQL
关注(0)|答案(1)|浏览(180)

我有一个表,它的ID是UUID类型,我如何删除记录,其中id是在一个列表中的UUID?如何构造这个查询?

List<UUID> s = new ArrayList<>();
    s.add(UUID.fromString("1a93f6f4-c1b4-489a-83dc-a7fe0a15d8ce"));
    s.add(UUID.fromString("1a93f6f4-c1b4-489a-83dc-a7fe0a15d8ce"));

PreparedStatement stm = conn.prepareStatement(
                        "delete from table_name where id in (?)");
        Array array = conn.createArrayOf("UUID", s.toArray());
        stm.setArray(1, array);
        int deletedCount = stm.executeUpdate();

id是UUID类型,我得到以下错误。

Exception in thread "main" org.postgresql.util.PSQLException: ERROR: cannot cast type uuid[] to uuid
  Position: 129
    at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2713)
    at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:2401)
    at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:368)
    at org.postgresql.jdbc.PgStatement.executeInternal(PgStatement.java:498)
    at org.postgresql.jdbc.PgStatement.execute(PgStatement.java:415)
    at org.postgresql.jdbc.PgPreparedStatement.executeWithFlags(PgPreparedStatement.java:190)
    at org.postgresql.jdbc.PgPreparedStatement.executeUpdate(PgPreparedStatement.java:152)
    at org.example.TestJJDBCUUID.main(TestJJDBCUUID.java:35)
knpiaxh1

knpiaxh11#

您可以使用ANY运算符。或者使用两个占位符,然后分别设置它们。

溶液1:任何操作员

将查询更改为

WHERE id = ANY (?)

这应该有相同的结果,并与您的当前代码一起工作。

溶液2:多个占位符

将查询更改为

WHERE id = IN (?, ?)

然后设置两个UUID

stm.setString(1, "1a93f6f4-c1b4-489a-83dc-a7fe0a15d8ce");
stm.setString(2, "1a93f6f4-c1b4-489a-83dc-a7fe0a15d8ce");

相关问题