jdbc preparedStatements for multiple value inside IN

How t开发者_开发知识库o set values for sql with IN which can hold variable numbers like, ... 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?); 

---

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

---

You need to bind a value for every 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]); 

---

Let me mention a solution that AFAIK works only in Postgres. Postgres has arrays and you can pass in a string representation '{1,2,3}'. IN can be replaced by ANY (in fact, in some situations this is how PG handles the IN query internally, according to the planner output. The array can be built up with a loop entirely on the client side and then passed in as a string, voila.

---

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