java 如何使用 jdbc 模板将值列表作为参数传递给 IN 子句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/45696465/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to pass list of values as a parameter to IN clause using jdbc template
提问by Jan69
I want to pass the car names as a bind variable (changes at runtimme) How to achieve that .
我想将汽车名称作为绑定变量传递（在运行时更改）如何实现。
Java Version 1.7
Java 1.7 版
private JdbcTemplate jdbcTemplate;
public Collection<Cars> findAll(){
String sql = "SELECT NAME, YEAR, TYPE FROM CARS where NAME in ('Honda','Audi','Benz')";
List<Cars> carsList = new ArrayList<Cars>();
List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql);
for (Map row : rows) {
Cars car = new Cars();
car.setName(String.valueOf(row.get("NAME")));
car.setType(String.valueOf(row.get("TYPE")));
car.setYear(String.valueOf(row.get("YEAR")));
carsList.add(car);
}
return carsList;
}
回答by Darshan Mehta
Use named parameters as explained here, e.g.:
按照此处的说明使用命名参数，例如：
String sql = "SELECT NAME, YEAR, TYPE FROM CARS where NAME in (:cars)";
List<Cars> carsList = new ArrayList<Cars>();
NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(datasource);
List<String> carNames = new ArrayList<String>();
for(Car car : carList){
carNames.add(car.getName());
}
SqlParameterSource namedParameters = new MapSqlParameterSource("cars", carNames);
namedParameterJdbcTemplate.queryForObject(sql, namedParameters, ResponseType.class);
回答by Jiri Tousek
jdbcTemplate.queryForList(
"SELECT NAME, YEAR, TYPE FROM CARS where NAME in (?,?,?)",
new Object[] { "Honda", "Audi", "Benz" }
);
You should probably wrap the logic into a method that accepts the IN values and generates appropriate sequence of question marks.
您可能应该将逻辑包装到一个方法中，该方法接受 IN 值并生成适当的问号序列。
Some frameworks (like MyBatis) have built-in support for this.
一些框架（如 MyBatis）对此有内置支持。
Also keep in mind that different DBs have different limits on how long the IN list (or query as whole) may be. If you have too many values to put in the IN clause, you'll have to handle this (break it down to "batches" of appropriate size or use a different approach altogether).
还要记住，不同的 DB 对 IN 列表（或整个查询）的长度有不同的限制。如果要在 IN 子句中放入太多值，则必须处理此问题（将其分解为适当大小的“批次”或完全使用不同的方法）。
