Java – parameterized query: check whether the field is in the value array in the select statement

I'm trying to configure a parameterized query to have the following effects:

SELECT field1 FROM myTable WHERE field2 IN (1,2,3,4)

The database I'm using is Postgres

The query ran successfully without parameterization, but I want to use a parameterized query with jdbctemplate to populate the list with valid field2 values (this is an integer)

Try various values of VaR ("1,4", "[1,4]", "{1,4}" or "(1,4))") variants of queries I have tried:

myJdbcTemplate.query("SELECT field1 FROM field2 IN (?)",new Object[]{ var })

and

myJdbcTemplate.query("SELECT field1 FROM field2 IN (?::integer[])",new Object[]{ var })

also

myJdbcTemplate.query("SELECT field1 FROM field2 IN ?::integer[]",new Object[]{ var })

In addition, resources that describe how to parameterize queries are also useful

All these queries throw psqlexceptions indicating operator failure or type mismatch - which seems reasonable because I can't figure out how to parameterize the query

Solution

Take a look at the data access web page, especially 11.7 Section 3, where namedparameterjdbctemplate is used to construct the "in" clause

for example

NamedParameterJdbcTemplate jdbcTemplate = new NamedParameterJdbcTemplate(dataSource);
String sql = "select * from emp where empno in (:ids)";
List idList = new ArrayList(2);
idList.add(new Long(7782));
idList.add(new Long(7788));
Map parameters = new HashMap();
parameters.put("ids",idList);
List emps = jdbcTemplate.query(sql,parameters,new EmpMapper());