jdbc preparedStatements for multiple value inside IN

Question

How to 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?);

Related: http://stackoverflow.com/questions/178479/preparedstatement-in-clause-alternatives — Nivas, Sep 01 '11 at 17:05
possible duplicate of [What is the best approach using JDBC for parameterizing an IN clause?](http://stackoverflow.com/questions/2861230/what-is-the-best-approach-using-jdbc-for-parameterizing-an-in-clause) — BalusC, Sep 01 '11 at 17:50

Nathan Hughes · Accepted Answer · 2011-09-01T17:16:00.367

2

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 (?,?,?).

edited Sep 01 '11 at 17:16

answered Sep 01 '11 at 17:12

Nathan Hughes

94,330
19
181
276

If I am not wrong, you are talking about adding "?" char dynamically based on available statuses inside the sql. And then set those values using loop. :) I am also thinking about it. – random.learner Sep 01 '11 at 17:16

score 1 · Answer 2 · answered Sep 01 '11 at 17:10

1

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]);

answered Sep 01 '11 at 17:10

Marius Burz

4,555
2
18
28

score 0 · Answer 3 · answered Jan 27 '15 at 16:28

0

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

answered Jan 27 '15 at 16:28

Vins

1,931
16
14

score 0 · Answer 4 · answered Sep 01 '11 at 23:11

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.

jdbc preparedStatements for multiple value inside IN

4 Answers4

Linked