CODEWITHSUNDEEP
sqljdbcprepared-statementin-clause
toink
toink

Reputation: 255

multiple parameter "IN" prepared statement

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.

Upvotes: 1

Views: 4396

Answers (3)

Panky031
Panky031

Reputation: 443

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

Upvotes: 0

Jeff Miller
Jeff Miller

Reputation: 1434

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

Upvotes: 1

Cade Roux
Cade Roux

Reputation: 89661

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 + '#%'

Upvotes: 2

Related Questions