Using spring jdbc template to query for list of parameters

Question

New to Spring JDBC template but I'm wondering if I am able to pass a list of parameters and execute query once for each parameter in list. As I've seen many examples, the list of parameters being passed is for the execution of the query using all the parameters provided. Rather I am trying to execute query multiple times and for each time using new parameter in list.

For example: Let's say I have a List of Ids - params (Strings)

List<String> params = new ArrayList<String>();
params.add("1234");
params.add("2345");

trying to do something like:

getJdbcTemplate().query(sql, params, new CustomResultSetExtractor());

which I know as per documentation is not allowed. I mean for one it has to be an array. I've seen simple examples where query is something like "select * from employee where id = ?" and they are passing new Object[]{"1234"} into method. And I'm trying to avoid the IN() condition. In my case each id will return multiple rows which is why I'm using ResultSetExtractor.

I know one option would be to iterate over list and include each id in list as a parameter, something like:

for(String id : params){
  getJdbcTemplate().query(sql, new Object[]{id}, new CustomResultSetExtractor());
}

Just want to know if I can do this some other way. Sorry, I Should mention that I am trying to do a Select. Originally was hoping to return a List of custom objects for each resultset.

Answer 1

You do need to pass an array of params for the API, but you may also assume that your first param is an array. I believe this should work:

String sql = "select * from employee where id in (:ids)"; // or should there be '?'
getJdbcTemplate().query(sql, new Object[]{params}, new CustomResultSetExtractor());

Or you could explicitly specify, that the parameter is an array

getJdbcTemplate().query(sql, new Object[]{params}, new int[]{java.sql.Types.ARRAY}, new CustomResultSetExtractor());