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

Just use a named parameter for "provnum" also:

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

Answer 2

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

EDIT: 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 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.