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.

The specific problem nothing to do with the IN clause.

BalusC
– BalusC

2010-05-11 12:06:31 +00:00
Commented May 11, 2010 at 12:06 — BalusC
– BalusC, Commented May 11, 2010 at 12:06

AstroCB · Accepted Answer · 2014-09-06 03:40:51Z

9

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.

edited Sep 6, 2014 at 3:40

AstroCB

12.4k20 gold badges59 silver badges75 bronze badges

answered May 11, 2010 at 12:05

mdma

57.9k12 gold badges97 silver badges128 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

mezmo Over a year ago

I think that's the key, make sure what you are using is the NamedParameterJdbcTemplate, the regular JdbcTemplate doesn't support the named parameters.

matt b · Accepted Answer · 2010-05-11 12:03:59Z

4

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

answered May 11, 2010 at 12:03

matt b

140k66 gold badges287 silver badges352 bronze badges

3 Comments

GuruKulki Over a year ago

getting this execption org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [SELECT * FROM INCOME WHERE PROVNUM=:provNum AND FISCALDATE IN ( :ids )]; Unable to convert between org.springframework.jdbc.core.namedparam.MapSqlParameterSource and JAVA_OBJECT.; nested exception is java.sql.SQLException: Unable to convert between org.springframework.jdbc.core.namedparam.MapSqlParameterSource and JAVA_OBJECT.

jnt30 Over a year ago

The SpringSource documentation on this can be found: static.springsource.org/spring/docs/2.5.x/reference/… Make sure that you are using a collection implementing the List interface and that you are not using a custom object. To be truthful, I haven't tried this with a list of Dates, but Matt's example would work for List<Integer>, List<Long> etc.

matt b Over a year ago

@GK please post the code in question, including the types/values of whatever parameters you add with addValue()

Collectives™ on Stack Overflow

How to execute query with IN clause in spring?

2 Answers 2

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related