java 我可以要求 JDBCTemplate 扩展一个列表参数以在 in() 子句中使用吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3613881/
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
Can I ask JDBCTemplate to expand a list parameter for use in an in() clause?
提问by royal
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.
具体来说，我正在使用 Spring 和 JdbcTemplate/SimpleJdbcTemplate 类。
采纳答案by The Alchemist
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.
对不起，不能那样做。你可以自己写一个方便的方法来做到这一点，但setParameterList()据我所知，没有像 Hibernate 这样的方法。
回答by kaarlo
You can do it by using NamedParameterJdbcTemplate.
您可以使用 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);
回答by Vicky
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;
}
回答by Dave Rogers
Yes you can in Spring 3 using a named parameter.
是的，您可以在 Spring 3 中使用命名参数。
See http://docs.spring.io/spring/docs/3.0.x/spring-framework-reference/html/jdbc.html#jdbc-in-clause
见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:
它应该采用任何原语列表并展开列表。请注意您的列表不会超过您的数据库支持的最大大小。（Oracle 限制为 1000）。这样的事情应该工作：
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);
