Get PSQL Exception when passing List<String> as a Query parameter in "IN" clause using JDBCTemplate

Question

I am creating the Spring Boot application with a PostgreSQL database. and I am using JDBCTemplate to perform DB operations. as per my requirement, I want a count of the row from CONTRACT_VIEW_2 table where the value of LICENSE_PLATE = "xxxxxx" and ZONE_CODE is IN ("x","y","z") but I am getting PSQL Exception.

I tried using MapSQLParameterSource but still, I facing the issue.

@Override
public Integer getAllZoneForLp(String lp,List<String> zones) {
  MapSqlParameterSource zoneIds = new MapSqlParameterSource();
  zoneIds.addValue("zoneIds",zones);
  String sql = "select " +
        "count(*) " +
        "from contract_view_2 " +
        "where license_plate = ? and zone_code IN (?)";
  return jdbcTemplate.queryForObject(sql,Integer.class,lp,zoneIds);
}

I expect the row count in the result but I am getting PSQL Exception. I am attaching the image of the exception which I am getting.

Thanks in advance.

Avi Avi · Accepted Answer · 2019-07-05T06:54:05

The problem with yours is you have added Namedparameter to JdbcTemplate.

So in case you are using NamedParameterJdbcTemplate

@Override
public Integer getAllZoneForLp(String lp,List<String> zones) {
  MapSqlParameterSource parameters = new MapSqlParameterSource();
  parameters.addValue("lp", lp);
  parameters.addValue("zoneIds",zones);
  String sql = "select " +
        "count(*) " +
        "from contract_view_2 " +
        "where license_plate = :lp and zone_code IN (:zoneIds)";
  NamedParameterJdbcTemplate namedParameterJdbcTemplate = new NamedParameterJdbcTemplate(jdbcTemplate);

  return namedParameterJdbcTemplate.queryForObject(sql, parameters, Integer.class);
}

In case you want to use jdbcTemplate

@Override
public Integer getAllZoneForLp(String lp,List<String> zones) {
  String sql = "select " +
        "count(*) " +
        "from contract_view_2 " +
        "where license_plate = ? and zone_code IN (?)";

  return jdbcTemplate.queryForObject(sql, Integer.class, new Object[] { lp, zones}); 
}

Go with NameParameterJdbcTemplate so that you won't miss any params.

Get PSQL Exception when passing List<String> as a Query parameter in "IN" clause using JDBCTemplate

2 Answers