CODEWITHSUNDEEP
javasqlspringjdbcjdbctemplate
Malax
Malax

Reputation: 9604

How to execute IN() SQL queries with Spring's JDBCTemplate effectively?

I was wondering if there is a more elegant way to do IN() queries with Spring's JDBCTemplate. Currently I do something like that:

StringBuilder jobTypeInClauseBuilder = new StringBuilder();
for(int i = 0; i < jobTypes.length; i++) {
    Type jobType = jobTypes[i];

    if(i != 0) {
        jobTypeInClauseBuilder.append(',');
    }

    jobTypeInClauseBuilder.append(jobType.convert());
}

Which is quite painful since if I have nine lines just for building the clause for the IN() query. I would like to have something like the parameter substitution of prepared statements

Upvotes: 197

Views: 230281

Answers (5)

Abhishek Chatterjee
Abhishek Chatterjee

Reputation: 2049

Refer to here

write query with named parameter, use simple ListPreparedStatementSetter with all parameters in sequence. Just add below snippet to convert the query in traditional form based to available parameters,

ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(namedSql);

List<Integer> parameters = new ArrayList<Integer>();
for (A a : paramBeans)
    parameters.add(a.getId());

MapSqlParameterSource parameterSource = new MapSqlParameterSource();
parameterSource.addValue("placeholder1", parameters);
// create SQL with ?'s
String sql = NamedParameterUtils.substituteNamedParameters(parsedSql, parameterSource);     
return sql;

Upvotes: 2

yawn
yawn

Reputation: 8214

You want a parameter source:

Set<Integer> ids = ...;

MapSqlParameterSource parameters = new MapSqlParameterSource();
parameters.addValue("ids", ids);

List<Foo> foo = getJdbcTemplate().query("SELECT * FROM foo WHERE a IN (:ids)",
     parameters, getRowMapper());

This only works if getJdbcTemplate() returns an instance of type NamedParameterJdbcTemplate

Upvotes: 304

luso
luso

Reputation: 3000

Many things changed since 2009, but I can only find answers saying you need to use NamedParametersJDBCTemplate.

For me it works if I just do a 

db.query(sql, new MyRowMapper(), StringUtils.join(listeParamsForInClause, ","));

using SimpleJDBCTemplate or JDBCTemplate

Upvotes: -3

Mr Lou
Mr Lou

Reputation: 1807

I do the "in clause" query with spring jdbc like this:

String sql = "SELECT bg.goodsid FROM beiker_goods bg WHERE bg.goodsid IN (:goodsid)";

List ids = Arrays.asList(new Integer[]{12496,12497,12498,12499});
Map<String, List> paramMap = Collections.singletonMap("goodsid", ids);
NamedParameterJdbcTemplate template = 
    new NamedParameterJdbcTemplate(getJdbcTemplate().getDataSource());

List<Long> list = template.queryForList(sql, paramMap, Long.class);

Upvotes: 69

Mahmood Omari
Mahmood Omari

Reputation: 209

If you get an exception for : Invalid column type

Please use getNamedParameterJdbcTemplate() instead of getJdbcTemplate()

 List<Foo> foo = getNamedParameterJdbcTemplate().query("SELECT * FROM foo WHERE a IN (:ids)",parameters,
 getRowMapper());

Note that the second two arguments are swapped around.

Upvotes: 20

Related Questions