0

I have a table with an ID that is of type UUID, how do I delete the records where id is in a list of UUIDs? How to construct this query?

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 is of type UUID I get the following error.

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)
Adelin
  • 18,144
  • 26
  • 115
  • 175
  • https://stackoverflow.com/questions/17842211/how-to-use-an-arraylist-as-a-prepared-statement-parameter https://stackoverflow.com/questions/3107044/preparedstatement-with-list-of-parameters-in-a-in-clause – krzydyn Mar 23 '23 at 09:29

1 Answers1

0

You can use the ANY Operator. Or use two placeholders and then set them individually.

Solution 1: Any Operator

Change your query to

WHERE id = ANY (?)

This should have the same result and work with the current code you have.

Solution 2: Multiple placeholders

Change your query to

WHERE id = IN (?, ?)

And then set both UUIDs

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