JDBC: Pass a List to a jdbc parameter

by Bruce on June 14, 2010

If you have a List and want to pass it to a JDBC query as a parameter, try this. In this example we have a list of widget names and we want in return a list of store Id’s. For the sake of this example we are not looking to associate which stores sell which widgets. We simply want to know of any store that sells any of the widgets.

public List<Map<String,Object>> getStoresByWidgets(List<String> widgetNameList) {
    NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);
    Map<String, Object> parameterMap = new HashMap<String, Object>();
    parameterMap.put("widgetNames", widgetNameList);
    String sql =
            "SELECT store_id \n" +
            "  FROM widget_store_vw wsv \n" +
            " WHERE store_status = 'Active' \n" +
            "   AND widget_name in (:widgetNames) ";
    return namedParameterJdbcTemplate.queryForList(sql, parameterMap);
}

{ 1 comment… read it below or add one }

Aydar January 23, 2014 at 10:24 pm

I don’t know why it no works with simple jdbcTemplate, but with namedParameterJdbcTemplate it works.
Thanks!

Leave a Comment

Previous post:

Next post: