Jdbc createdStatements for plural value inside IN

Question

How to set values ​​for sql with IN , which can contain variable numbers, such as ... WHERE ... status IN (4, 6,7); ?

PreparedStatement ps = con.prepareStatement( "SELECT ea.* FROM employeeAssignment ea " + "JOIN employee e ON e.employeeID = ea.employeeID " + "WHERE e.resourceID = ? and ea.status IN (?);"); ps.setInt(1, 75); ps.setInt(2, someArray/some thing other?); 

Answer 1

You can generate the SQL IN clause according to how many status codes you need to pass. Therefore, if your status is IN (4,6,7) , you can generate an SQL statement ending with the same number of question marks as the codes: IN (?,?,?) .

Answer 2

You need to bind a value for each entry in the array / list:

SQL:

 ea.status IN (?, ?, ..., ?) 

Java:

 ps.setInt(2, someArray[0]); ps.setInt(3, someArray[1]); .. ps.setInt([...], someArray[someArray.length]); 

Answer 3

Let me mention a solution that AFAIK only works in Postgres. Postgres has arrays, and you can pass the string representation of '{1,2,3}'. IN can be replaced by ANY (in fact, in some situations, PG processes the IN request internally, according to the output of the scheduler. An array can be created with a loop entirely on the client side and then passed as a string, voila.

Answer 4

Here is the Spring 4 documentation. Http://docs.spring.io/spring/docs/current/spring-framework-reference/html/jdbc.html#jdbc-in-clause