How can I use IN query with NamedParameterJdbcTemplate?

Question

How can I use IN query with NamedParameterJdbcTemplate?

protected List getList(String sql, Map<String, ?> param) {
    String sql = "select birth from member where gender=:gender and surname in (:surname)";
    List list = this.simpleJdbcTemplate.queryForList(sql, param);
    return list;
}

I put 'surname1','surname2' and surname1','surname2 in params, so :surname would be 'surname1','surname2', but both didn't work. When there was only gender parameter, it worked.

How can I do this?

Answer 1

if we have an Object like this:

Class Member {
    String birth;
    String gender;
    String surname;
    //getter & setter
}

then:

String sql = "select birth from member where gender=:gender and surname in (:surname);";
List<String> surnames = new ArrayList<>();
surnames.add("aaaaa");
surnames.add("bbbbb");
MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("surname", surnames);
parameters.addValue("gender", "man");
NamedParameterJdbcTemplate givenParamJdbcTemp = new NamedParameterJdbcTemplate(simpleJdbcTemplate);
// query a list for Member;
List<Member> data = givenParamJdbcTemp.query(sql, parameters, new RowMapper<Member>() {
    @Override
    public Member mapRow(ResultSet rs, int rowNum) throws SQLException {
        Member member = new Member();
        member.setBirth(rs.getString("birth"));
        return member;
    }
}
//just query a list for birth
List<String> data = givenParamJdbcTemp.query(sql, parameters, new RowMapper<String>() {
    @Override
    public String mapRow(ResultSet rs, int rowNum) throws SQLException {
        return rs.getString("birth");
    }
}

Answer 2

In your param Map add the surnames you're searching for as a Collection. e.g.:

List<String> surnames = new ArrayList<>();
surnames.add("Smith");
surnames.add("Patel");
param.put("surnames", surnames);