How to execute query with IN clause in spring?

Question

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?

need help, thanks.

Answer 1

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!

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.