如何将IN查询与NamedParameterJdbcTemplate一起使用

Question

如何将IN查询与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;
}

我把"姓氏1"、"姓氏2"和姓氏1、"姓氏2中"放在params中，所以：姓氏应该是"姓氏1、"姓氏2"，但两者都不起作用。当只有性别参数时，它就起作用了。

我该怎么做？

Answer 1

如果我们有这样的对象：

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

然后：

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

在您的paramMap中，将您要搜索的姓氏添加为Collection。例如：

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