using postgresql NOT IN statement

Question

i have this query in spring:

 private String SQL_Clear_Deleted_Options = "DELETE FROM vote_votes WHERE poll_id=? AND option_id NOT IN ?"

my problem is with second ?. the correct form would be (id1,id2,id3,...). how can i pass a string like cl="0,1,2,3,6" to this query?

i'm using jdbcTemplate. so it would be

jdbcTemplate.update(SQL_Clear_Deleted_Options, id,cl)

what should be cl?

Answer 1

You can use setArray of PreparedStatement to set multiple values. https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#setArray(int,%20java.sql.Array)

And the array should be of the correct type, so you could create it with the Connection.createArrayOf function: https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#createArrayOf(java.lang.String,%20java.lang.Object[])

You can see a lot of discussions about that topic on SO already, e.g.: How to use an arraylist as a prepared statement parameter

Edit: Forgot to mention: The JdbcTemplate should set the Parameter correctly when the Array is given as parameter.