Parameterized Query: Check if field is in array of values in SELECT statement

Question

I'm trying to configure a parameterized query to the effect of:

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

The database I'm using is Postgres.

This query run successfully unparameterized, but I'd like to use a parameterized query with a JdbcTemplate to fill in the list for valid field2 values (which are integers).

Trying various values for var ("1,2,3,4", "[1,2,3,4]", "{1,2,3,4}", or "(1,2,3,4)") I've tried variations on the query:

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

and

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

and also

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

On a side note, resources that describe how to parameterize queries would also be really helpful.

All of these queries throw PSQLExceptions that indicate the operator fails or that there's a type mismatch -- which seems reasonable as I can't figure out how to parameterize the query.

Answer 1

I'm think you can't use parameters in this way, only dynamic query.

 query = "SELECT field1 FROM field2 IN ("+paramStr+")"

or, if you has a perfomance or a query len issues, you can fill temporary table with your params and then

 create tempTable (param int)
 insert into tempTable values (1)
 insert into tempTable values (2)
 insert into tempTable values (3)
 insert into tempTable values (4)
 query = "SELECT field1 FROM field2 IN (select param from tempTable)"

Answer 2

I took another look at the manual, it looks like to search arrays there's an alternative syntax, something like:

SELECT field1 FROM myTable WHERE field2 = ANY(ARRAY[1,2,3,4])

Which can be parameterized as:

myJdbcTemplate.query("SELECT field1 FROM myTable WHERE field2 = ANY(?::integer[])"), new Object[]{ "{1,2,3,4}" })

Answer 3

Take a look at the Spring Data Access web page, particularly section 11.7.3 where using the NamedParameterJdbcTemplate to build an 'IN' clause is covered.

e.g.

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