How to generate a dynamic "in (...)" sql list through Spring JdbcTemplate?

Is it possible to generate arbitrary "in (开发者_Go百科)" 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.

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.

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

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.