如何在spring中用IN子句执行查询？ [英] How to execute query with IN clause in spring?

问题描述

我有以下代码：

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;
}

但我无法发送PROVNUM的值。怎么做？

But i am not able to send the value for PROVNUM. how to do that?

需要帮助，
谢谢。

need help, thanks.

推荐答案

看起来你正在混合命名和位置参数。最好使用其中一个，但不能同时使用两个。

It looks like you are mixing named and position parameters. It's best to use one or the other, but not both.

尝试

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);

在您发表评论后，我发现您使用了的错误重载query（）方法：有很多可供选择，所以一些错误可以进入就不足为奇了！

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!

你需要调用

return this.template.getJdbcOperations().query(
       "SELECT  ... etc.", parameters, this.rowMapper);

在原始调用中，您正在调用版本查询（String sql， RowMapper mapper，Object ... params），它需要文字参数。修改后的调用是查询（String sql，SqlParameterSource params，RowMapper mapper） - SqlParamtersSource作为第二个参数是键。另外，值得检查您使用的是 NamedParameterJdbcTemplate 。

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.

这篇关于如何在spring中用IN子句执行查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！