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

This question already has answers here: Ho开发者_运维百科w to execute IN() SQL queries with Spring's JDBCTemplate effectively? (5 answers) Closed 4 years ago.

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.

---

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

---

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.

---

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;

    }

---

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