CODEWITHSUNDEEP
javasqljdbc
random.learner
random.learner

Reputation: 53

jdbc preparedStatements for multiple value inside IN

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

Upvotes: 4

Views: 3905

Answers (4)

Vins
Vins

Reputation: 2070

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

Upvotes: 0

Andrew Lazarus
Andrew Lazarus

Reputation: 19310

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.

Upvotes: 1

Nathan Hughes
Nathan Hughes

Reputation: 96385

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

Upvotes: 2

Marius Burz
Marius Burz

Reputation: 4645

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

Upvotes: 1

Related Questions