Can I ask JDBCTemplate to expand a list parameter for use in an in() clause?

Question

Can I do something like this:

select * from mytable m where m.group_id in (?)

... and pass in a list or array of arguments to be expanded in to my parameter, ie:

select * from mytable m where m.group_id in (1,2,3,4)

Specifically, I'm using Spring and the JdbcTemplate/SimpleJdbcTemplate classes.

My recollection is No - the `?` placeholder signifies a single value as it would in SQL, so it can't contain a comma separated list. — OMG Ponies, Aug 31 '10 at 23:17

score 27 · Answer 1 · answered Sep 09 '10 at 11:22

27

You can do it by using NamedParameterJdbcTemplate.

With your sample it would go something like:

NamedParameterJdbcTemplate db = ...;
List paramList = ...;

Map idsMap = Collections.singletonMap("ids", paramList);
db.query("select * from mytable m where m.group_id in (:ids)", idsMap);

answered Sep 09 '10 at 11:22

kaarlo

311
2
5

1

Thanks! It is worth mentioning that a NamedParameterJdbcTemplate can be create from a starndard JdbcTemplate: NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate); – Ivo Jun 08 '15 at 14:26

score 6 · Accepted Answer · answered Sep 01 '10 at 02:29

6

Sorry, can't do that. You can write yourself a convenience method to do that, but there's no setParameterList() like Hibernate, as far as I know.

answered Sep 01 '10 at 02:29

The Alchemist

3,397
21
22

1

The simplest convenience method, for a list of numeric types might be list.toString().replace("[","(").replace("]",")") – Stewart Jun 03 '13 at 09:30

score 2 · Answer 3 · edited Sep 26 '17 at 08:49

Please find the below code

public Collection<Employee> findByIds(List<String> ids) {

        Map<String, Object> params = new HashMap<String, Object>();
        params.put("ids", ids);

        List<Employee> employees = namedParameterJdbcTemplate.query(
                "SELECT * FROM trn_employee where employee_id IN (:ids)",
                params,
                ParameterizedBeanPropertyRowMapper.newInstance(Employee.class));

        return employees;

    }

score 0 · Answer 4 · edited Dec 12 '15 at 19:43

Yes you can in Spring 3 using a named parameter.

See http://docs.spring.io/spring/docs/3.0.x/spring-framework-reference/html/jdbc.html#jdbc-in-clause

It should take any list of primitives and expand the list. Just be careful that your list does not go over the max size your DB supports. (Oracle limit is 1000). Something like this should work:

    List<Integer> ids = new ArrayList<Integer>();
    ids.add(1);
    ids.add(2);
    ids.add(3);

    Map<String,Object>  params = new HashMap<String, Object>();

    String sql = "SELECT PERSON.ID, PERSON.USERNAME, PERSON.EMAIL_ADDRESS, PERSON.FIRST_NAME, PERSON.LAST_NAME, PERSON.ACCOUNT_STATUS FROM PERSON WHERE ID IN (:ids)";
    params.put("ids",ids);

    return getSimpleJdbcTemplate().query(sql, rowMapper, params);

Can I ask JDBCTemplate to expand a list parameter for use in an in() clause?

4 Answers4