multiple parameter "IN" prepared statement

Question

I was trying to figure out how can I set multiple parameters for the IN clause in my SQL query using PreparedStatement.

For example in this SQL statement, I'll be having indefinite number of ?.

select * from ifs_db where img_hub = ? and country IN (multiple ?)

I've read about this in PreparedStatement IN clause alternatives?

However I can't figure it out how to apply it to my SQL statement above.

Did you see the link of the other question? http://www.javaranch.com/journal/200510/Journal200510.jsp#a2 — , Mar 20 '12 at 16:09
most of them used "id" or int as their parameter.. I was wondering if how would it apply if the parameter is a string.... thanks, — toink, Mar 20 '12 at 16:32
In Java you can set a parameter to a given String value in the following way: void java.sql.PreparedStatement.setString(int parameterIndex, String x) throws SQLException. See also https://docs.oracle.com/javase/8/docs/api/java/sql/PreparedStatement.html#setString-int-java.lang.String- — Yuci, Jan 08 '16 at 11:43

score 2 · Answer 1 · answered Mar 20 '12 at 16:47

There's not a standard way to handle this.

In SQL Server, you can use a table-valued parameter in a stored procedure and pass the countries in a table and use it in a join.

I've also seen cases where a comma-separated list is passed in and then parsed into a table by a function and then used in a join.

If your countries are standard ISO codes in a delimited list like '#US#UK#DE#NL#', you can use a rather simplistic construct like:

select * from ifs_db where img_hub = ? and ? LIKE '%#' + country + '#%'

I would do the same in Oracle: CallableStatement with an array IN parameter and a cursor OUT parameter. The stored proc doing a join to form the refcursor. — Glenn, Mar 20 '12 at 17:12

score 1 · Accepted Answer · answered Mar 21 '12 at 12:17

Sormula will work for any data type (even custom types). This example uses int's for simplicity.

ArrayList<Integer> partNumbers = new ArrayList<Integer>();
partNumbers.add(999);
partNumbers.add(777);
partNumbers.add(1234);

// set up
Database database = new Database(getConnection());
Table<Inventory> inventoryTable = database.getTable(Inventory.class);

ArrayListSelectOperation<Inventory> operation =
    new ArrayListSelectOperation<Inventory>(inventoryTable, "partNumberIn");

// show results
for (Inventory inventory: operation.selectAll(partNumbers))
    System.out.println(inventory.getPartNumber());

score 0 · Answer 3 · answered Jun 09 '16 at 19:38

You could use setArray method as mentioned in the javadoc below:

http://docs.oracle.com/javase/6/docs/api/java/sql/PreparedStatement.html#setArray(int, java.sql.Array)

Code:
PreparedStatement statement = connection.prepareStatement("Select * from    test where field in (?)");
Array array = statement.getConnection().createArrayOf("VARCHAR", new    Object[]{"AA1", "BB2","CC3"});
statement.setArray(1, array);
ResultSet rs = statement.executeQuery();

multiple parameter "IN" prepared statement

3 Answers3