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.

Solution 1

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.

Solution 2

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

Solution 3

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;

    }

Comments

[Stewart]

The simplest convenience method, for a list of numeric types might be list.toString().replace("[","(").replace("]",")")

[Ivo]

Thanks! It is worth mentioning that a NamedParameterJdbcTemplate can be create from a starndard JdbcTemplate: NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);