Parameterized Query: Check if field is in array of

问题:

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.

回答1:

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

回答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}" })