How to execute IN() SQL queries with Spring's JDBCTemplate effectively?
JavaSqlSpringJdbcJdbctemplateJava Problem Overview
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
Java Solutions
Solution 1 - Java
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
Solution 2 - Java
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);
Solution 3 - Java
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.
Solution 4 - Java
Refer to [here][1] [1]: https://web.archive.org/web/20181219121911/http://forum.spring.io/forum/spring-projects/batch/76237-jdbccursoritemreader-with-named-parameters
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;
Solution 5 - Java
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