How to pass list of values as a parameter to IN clause using jdbc template

Question

I want to pass the car names as a bind variable (changes at runtimme) How to achieve that .

Java Version 1.7

private JdbcTemplate jdbcTemplate;

 public Collection<Cars> findAll(){

 String sql =  "SELECT NAME, YEAR, TYPE FROM CARS where NAME in ('Honda','Audi','Benz')";
        List<Cars> carsList = new ArrayList<Cars>();
        List<Map<String, Object>> rows = jdbcTemplate.queryForList(sql);
        for (Map row : rows) {
            Cars car = new Cars();
            car.setName(String.valueOf(row.get("NAME")));
            car.setType(String.valueOf(row.get("TYPE")));
            car.setYear(String.valueOf(row.get("YEAR")));

            carsList.add(car);
        }
        return carsList;
    }

score 17 · Accepted Answer · answered Aug 15 '17 at 15:53

Use named parameters as explained here, e.g.:

String sql =  "SELECT NAME, YEAR, TYPE FROM CARS where NAME in (:cars)";
List<Cars> carsList = new ArrayList<Cars>();
NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(datasource);
List<String> carNames = new ArrayList<String>();
for(Car car : carList){
    carNames.add(car.getName());
}
SqlParameterSource namedParameters = new MapSqlParameterSource("cars", carNames);
namedParameterJdbcTemplate.queryForObject(sql, namedParameters, ResponseType.class);

score 2 · Answer 2 · answered Aug 15 '17 at 15:53

jdbcTemplate.queryForList(
    "SELECT NAME, YEAR, TYPE FROM CARS where NAME in (?,?,?)",
    new Object[] { "Honda", "Audi", "Benz" }
);

You should probably wrap the logic into a method that accepts the IN values and generates appropriate sequence of question marks.

Some frameworks (like MyBatis) have built-in support for this.

Also keep in mind that different DBs have different limits on how long the IN list (or query as whole) may be. If you have too many values to put in the IN clause, you'll have to handle this (break it down to "batches" of appropriate size or use a different approach altogether).

How to pass list of values as a parameter to IN clause using jdbc template

2 Answers2