Menu
Is there any way to perform the following query:
select * from table where field in (?)
and pass a list/set/array as a value for ? placeholder.
I'm using QueryRunner from the Apache db-commons library.
439
Arrays in query parameters are sometimes represented by repeating a parameter multiple times:?foo=bar&foo=qux. Sometimes by repeating a parameter along with empty square brackets:
In the latest versions of SQL Server, we can use the User Defined Data Type (UDT) with a base type of table to send array or list through a parameter. Let’s see with an example how to use User Defined Type to pass a list or an array to a stored procedure.
You can use http_build_query to generate a URL-encoded querystring from an array in PHP. Whilst the resulting querystring will be expanded, you can decide on a unique separator you want as a parameter to the http_build_query method, so when it comes to decoding, you can check what separator was used.
While using older versions of SQL Server, I’ve used to the XML method to pass array or list to stored procedure. In the latest versions of SQL Server, we can use the User Defined Data Type (UDT) with a base type of table to send array or list through a parameter.
Sure there is, use createArrayOf:
final List<Integer> id = new ArrayList<>();
id.add(12);
id.add(15);
final Array toDelete = connection.createArrayOf("int", id.toArray());
queryRunner.query(
connection,
"SELECT * FROM table WHERE id = ANY(?)",
resultSetHandler,
toDelete
);
(example is using PostgreSQL but should also work for others)
118
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
