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.

pd40 · Accepted Answer · 2012-06-02 15:19:59Z

5

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

edited Jun 2, 2012 at 15:19

pd40

3,2553 gold badges23 silver badges29 bronze badges

answered Nov 1, 2009 at 17:19

Brian Agnew

273k38 gold badges342 silver badges443 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Mark Elliot · Accepted Answer · 2009-11-01 17:05:32Z

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

answered Nov 1, 2009 at 17:05

Mark Elliot

77.4k23 gold badges147 silver badges162 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related