31

Is it possible to generate arbitrary "in ()" lists in a SQL query through Jdbc template:

example:

"select * from t where c in (#)" , However '#' could be an arbitrary list of values only known at runtime.

wsb3383
  • 3,841
  • 12
  • 44
  • 59
  • 1
    possible duplicate of [How to execute IN() SQL queries with Spring's JDBCTemplate effectivly?](http://stackoverflow.com/questions/1327074/how-to-execute-in-sql-queries-with-springs-jdbctemplate-effectivly) – Adam Feb 29 '12 at 00:50
  • 1
    Possible duplicate of [How to execute IN() SQL queries with Spring's JDBCTemplate effectivly?](https://stackoverflow.com/questions/1327074/how-to-execute-in-sql-queries-with-springs-jdbctemplate-effectivly) – Alex R Sep 28 '18 at 17:33

3 Answers3

37

Yes, it's possible in Spring if you use NamedParameterJdbcTemplate or SimpleJdbcTemplate with named parameters. List parameter can be set as a java.util.List:

List<String> list = new ArrayList<String>();

list.add("A");
list.add("B");
list.add("C");

List<SomeObject> result = simpleJdbcTemplate.query("SELECT * FROM t WHERE c in (:list)",
    new RowMapper<SomeObject>() { ... },
    Collections.singletonMap("list", list));

In this case Spring internally creates the SQL query with the required number of placeholders based on the size of the actual list when replacing named parameters with ?s.

axtavt
  • 239,438
  • 41
  • 511
  • 482
  • 5
    This does nt work if your IN(...) arguments are not Strings (text). For example if you wanna pass a List this way, you'll get Wrong data type: java.lang.NumberFormatException: For input string: "[2, 3, 4]" – Shivan Dragon Sep 28 '11 at 11:40
  • @ShivanDragon Is there a correct way to do it in that case? General note on the original answer. With java 8 you can use the lambda format `(rs, rowNum) -> {...}` in place of `new RowMapper() { ... }`. – gaoagong Jan 02 '19 at 20:29
0

SimpleJDBCTemplate is depricated now. You can use NamedParameterJdbcTemplate instead. Sample code is below. If you have multiple parameters of different kind you can use Object as key, otherwise use your List<T> 

String sqlAllEmpl = queryLoader.getProperty("allEmployeesByLevelAndPeriod");
        Map<String, Object> paramMap = new HashMap<String, Object>();
        paramMap.put("level", levelStr);
        paramMap.put("periodList", periodList);

        gridList = namedParameterJdbcTemplate.query(sqlAllEmpl, paramMap, new YourRowMapper());

your sqlAllEmpl will have two place holders, level - string and periodList - which is a list used in the IN statement of sql.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
Vins
  • 1,931
  • 16
  • 14
0

In Hibernate , you can use following sample:

if(tenors != null && tenors.length >0)
            sql.append(" and ip.tenor_id in (:tenors)");

.....


if(tenors != null && tenors.length >0){
    query.setParameterList("tenors", tenors);                                 
}
.....
SQLQuery query = (SQLQuery) getSession().createSQLQuery(sql.toString())
Andrei Sfat
  • 8,440
  • 5
  • 49
  • 69
James Zhou
  • 9
  • 1