Java 如何使用 Spring 的 JDBCTemplate 有效地执行 IN() SQL 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1327074/
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 execute IN() SQL queries with Spring's JDBCTemplate effectivly?
提问by Malax
I was wondering if there is a more elegant way to do IN() queries with Spring's JDBCTemplate. Currently I do something like that:
我想知道是否有更优雅的方式来使用 Spring 的 JDBCTemplate 进行 IN() 查询。目前我做这样的事情：
StringBuilder jobTypeInClauseBuilder = new StringBuilder();
for(int i = 0; i < jobTypes.length; i++) {
Type jobType = jobTypes[i];
if(i != 0) {
jobTypeInClauseBuilder.append(',');
}
jobTypeInClauseBuilder.append(jobType.convert());
}
Which is quite painful since if I have nine lines just for building the clause for the IN() query. I would like to have something like the parameter substitution of prepared statements
这是非常痛苦的，因为如果我有九行只是为了构建 IN() 查询的子句。我想要类似准备语句的参数替换
采纳答案by yawn
You want a parameter source:
你想要一个参数源：
Set<Integer> ids = ...;
MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("ids", ids);
List<Foo> foo = getJdbcTemplate().query("SELECT * FROM foo WHERE a IN (:ids)",
parameters, getRowMapper());
This only works if getJdbcTemplate()returns an instance of type NamedParameterJdbcTemplate
这仅适用于getJdbcTemplate()返回类型的实例NamedParameterJdbcTemplate
回答by Mr Lou
I do the "in clause" query with spring jdbc like this:
我使用 spring jdbc 执行“in 子句”查询，如下所示：
String sql = "SELECT bg.goodsid FROM beiker_goods bg WHERE bg.goodsid IN (:goodsid)";
List ids = Arrays.asList(new Integer[]{12496,12497,12498,12499});
Map<String, List> paramMap = Collections.singletonMap("goodsid", ids);
NamedParameterJdbcTemplate template =
new NamedParameterJdbcTemplate(getJdbcTemplate().getDataSource());
List<Long> list = template.queryForList(sql, paramMap, Long.class);
回答by Mahmood Omari
If you get an exception for : Invalid column type
如果出现以下异常：无效的列类型
Please use getNamedParameterJdbcTemplate()instead of getJdbcTemplate()
请使用getNamedParameterJdbcTemplate()代替getJdbcTemplate()
List<Foo> foo = getNamedParameterJdbcTemplate().query("SELECT * FROM foo WHERE a IN (:ids)",parameters,
getRowMapper());
Note that the second two arguments are swapped around.
请注意，后两个参数已交换。
回答by Abhishek Chatterjee
Refer to here
参考这里
write query with named parameter, use simple ListPreparedStatementSetterwith all parameters in sequence. Just add below snippet to convert the query in traditional form based to available parameters,
使用命名参数编写查询，ListPreparedStatementSetter按顺序使用简单的所有参数。只需添加以下代码段即可将传统形式的查询转换为可用参数，
ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(namedSql);
List<Integer> parameters = new ArrayList<Integer>();
for (A a : paramBeans)
parameters.add(a.getId());
MapSqlParameterSource parameterSource = new MapSqlParameterSource();
parameterSource.addValue("placeholder1", parameters);
// create SQL with ?'s
String sql = NamedParameterUtils.substituteNamedParameters(parsedSql, parameterSource);
return sql;
回答by luso
Many things changed since 2009, but I can only find answers saying you need to use NamedParametersJDBCTemplate.
许多事情自 2009 年以来发生了变化，但我只能找到说您需要使用 NamedParametersJDBCTemplate 的答案。
For me it works if I just do a
对我来说，如果我只是做一个
db.query(sql, new MyRowMapper(), StringUtils.join(listeParamsForInClause, ","));
using SimpleJDBCTemplate or JDBCTemplate
使用 SimpleJDBCTemplate 或 JDBCTemplate
