Select where id = array (Oracle JDBC)

Question

I'm currently working with trying to compare an ID in Oracle(A VARCHAR2) with an array of IDs I have as input.

This is what I want to do:

Select user, city where id = :arrayOfIds

In Postgres with JDBC I would use:

Select user, city where id = any(:arrayOfIds)

Is there an equivalent function in Oracle, or a way to do this?

Answer 1

You should use:

Select user, city where id in (:arrayOfIds)

and in you code, you need to trasform your array in string with ids:

arrayOfIds[0] --> 1
arrayOfIds[1] --> 3
arrayOfIds[2] --> 5

... in

1, 3, 5, ...

and you can use:

Array array = conn.createArrayOf("arrayOfIds", new Object[]{"1", "2","3"});
pstmt.setArray(1, array);

How to use an arraylist as a prepared statement parameter