How to execute a query with an IN clause in spring?

Question

I have the 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 can not send the value to PROVNUM. how to do it?

need help, thanks.

Answer 1

It looks like you are mixing the name and position parameters. It is 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 lot to choose from, so it’s not surprising that some errors can be painted over!

You need to call

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

In the original call, you call the version of query(String sql, RowMapper mapper, Object... params) that expects alphabetic parameters. The revised call is the query (String sql, SqlParameterSource params, RowMapper mapper) - the SqlParamtersSource key as the second argument. Also, it's just worth checking that you are using NamedParameterJdbcTemplate .

Answer 2

Just use the named parameter for "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);