How to execute IN SQL queries with Spring s JDBCTemplate effectively

Question

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  lt  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

User · Accepted Answer

You want a parameter source   Set lt Integer gt  ids         MapSqlParameterSource parameters   new MapSqlParameterSource    parameters addValue  ids   ids    List lt Foo gt  foo   getJdbcTemplate   query  SELECT   FROM foo WHERE a IN   ids         parameters  getRowMapper       This only works if getJdbcTemplate   returns an instance of type NamedParameterJdbcTemplate

User · Answer

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 lt String  List gt  paramMap   Collections singletonMap  goodsid   ids   NamedParameterJdbcTemplate template        new NamedParameterJdbcTemplate getJdbcTemplate   getDataSource      List lt Long gt  list   template queryForList sql  paramMap  Long class

User · Answer

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 lt Integer gt  parameters   new ArrayList lt Integer gt     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

User · Answer

If you get  an exception for   Invalid column type  Please use getNamedParameterJdbcTemplate   instead of getJdbcTemplate     List lt Foo gt  foo   getNamedParameterJdbcTemplate   query  SELECT   FROM foo WHERE a IN   ids   parameters   getRowMapper       Note that the second two arguments are swapped around

User · Answer

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

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

Examples related to java

Examples related to sql

Examples related to spring

Examples related to jdbc

Examples related to jdbctemplate