java 如何在 spring 中使用 IN 子句执行查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2810418/
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 query with IN clause in spring?
提问by GuruKulki
I have following code :
我有以下代码：
try {
MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("ids", year);
return this.template.getJdbcOperations().query(
"SELECT * FROM INCOME WHERE PROVNUM=? AND FISCALDATE IN ( :ids )", this.rowMapper, parameters);
} catch (EmptyResultDataAccessException ex) {
return null;
}
But i am not able to send the value for PROVNUM. how to do that?
但我无法发送 PROVNUM 的值。怎么做？
need help, thanks.
需要帮助，谢谢。
回答by mdma
It looks like you are mixing named and position parameters. It's best to use one or the other, but not both.
看起来您正在混合命名和位置参数。最好使用其中之一，但不要同时使用两者。
Try
尝试
MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("ids", year);
parameters.addValue("provnum", provnum);
return this.template.getJdbcOperations().query(
"SELECT * FROM INCOME WHERE PROVNUM=:provnum AND FISCALDATE IN ( :ids )", this.rowMapper, parameters);
After your comment, I see that you are using the wrong overload of the query()method: there are quite a few to choose from, so it's not surprising a few errors can creep in!
在您发表评论后，我发现您使用了错误的query()方法重载：有很多可供选择，因此可能会出现一些错误也就不足为奇了！
You need to call
你需要打电话
return this.template.getJdbcOperations().query(
"SELECT ... etc.", parameters, this.rowMapper);
In your original call, you are calling the version query(String sql, RowMapper mapper, Object... params)that expects literal parameters. The revised call is query(String sql, SqlParameterSource params, RowMapper mapper) - the SqlParamtersSource as the second argument is the key. Also, just worth checking that that you are using NamedParameterJdbcTemplate.
在您的原始调用中，您正在调用query(String sql, RowMapper mapper, Object... params)需要文字参数的版本。修改后的调用是 query(String sql, SqlParameterSource params, RowMapper mapper) - SqlParamtersSource 作为第二个参数是关键。另外，值得检查一下您是否正在使用NamedParameterJdbcTemplate。
回答by matt b
Just use a named parameter for "provnum" also:
也只需为“provnum”使用命名参数：
String sql = "SELECT * FROM INCOME WHERE PROVNUM=:provnum AND FISCALDATE IN (:ids )"
MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("ids", year);
parameters.addValue("provnum", ...);
return template.getJdbcOperations().query(sql, rowMapper, parameters);
