Parameterized IN clause using multiple columns

Question

I have a query on these lines where I am trying to filter out a result set by comparing tuples (for example, several SQL columns in the IN section ):

select * from mytable where (key, value) in (values ('key1', 'value1'), ('key2', 'value2'), ... ); 

This is a valid syntax and works fine in my Postgres 9.3 database.

I want to call this query through Spring JDBC, where the pairs of in values ​​are taken from List<Map<String,String>> .

It would be nice to do something like this:

 List<Map<String, String>> valuesMap = ...; String sql = "select * from mytable where (key, value) in (values :valuesMap)"; SqlParameterSource params = new MapSqlParameterSource("valuesMap", valuesMap); jdbcTemplate.query(sql, params, rowMapper); 

When I try to do this, I get:

 org.postgresql.util.PSQLException: No hstore extension installed. at org.postgresql.jdbc2.AbstractJdbc2Statement.setMap(AbstractJdbc2Statement.java:1707) ~[postgresql-9.3-1101-jdbc41.jar:na] at org.postgresql.jdbc2.AbstractJdbc2Statement.setObject(AbstractJdbc2Statement.java:1910) ~[postgresql-9.3-1101-jdbc41.jar:na] at org.postgresql.jdbc3g.AbstractJdbc3gStatement.setObject(AbstractJdbc3gStatement.java:36) ~[postgresql-9.3-1101-jdbc41.jar:na] at org.postgresql.jdbc4.AbstractJdbc4Statement.setObject(AbstractJdbc4Statement.java:47) ~[postgresql-9.3-1101-jdbc41.jar:na] at org.springframework.jdbc.core.StatementCreatorUtils.setValue(StatementCreatorUtils.java:427) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE] at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValueInternal(StatementCreatorUtils.java:235) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE] at org.springframework.jdbc.core.StatementCreatorUtils.setParameterValue(StatementCreatorUtils.java:150) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE] at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.setValues(PreparedStatementCreatorFactory.java:287) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE] at org.springframework.jdbc.core.PreparedStatementCreatorFactory$PreparedStatementCreatorImpl.createPreparedStatement(PreparedStatementCreatorFactory.java:244) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE] at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:623) ~[spring-jdbc-4.2.3.RELEASE.jar:4.2.3.RELEASE] 

I looked at the hstore extension that he mentions. This is not like my problem.

Is there a way to accomplish this without dynamically building the SQL list and parameters?

Answer 1

All you have to do is pass a list of arrays, where each array contains a key and a value, for example:

 HashMap<String , String > map = new HashMap<>(); map.put("key0", "value0"); map.put("key1", "value1"); Set<String> keys = map.keySet(); List<String[]> valuesMap = new ArrayList<>(); for(String key:keys){ String[] entry = {key,map.get(key)}; valuesMap.add(entry); } String sql = "select * from mytable where (key, value) in (values :valuesMap)"; SqlParameterSource params = new MapSqlParameterSource("valuesMap", valuesMap); jdbcTemplate.query(sql, params, rowMapper); 

This is mentioned in the Spring documentation: http://docs.spring.io/spring-framework/docs/current/spring-framework-reference/html/jdbc.html#jdbc-in-clause

Answer 2

Unfortunately, there is no easy way to bind a bind variable of a nested collection to PostgreSQL. Instead, you can create the following SQL row

 SELECT * FROM mytable WHERE (key, value) IN ( (?, ?), (?, ?), ... ); 

This is a small job, allowing you to synchronize the SQL string and variable bindings. However, you could encode the map as JSON as such:

 SELECT * FROM mytable WHERE (key, value) IN ( SELECT t->>'key', t->>'value' FROM json_array_elements(?) AS t(v) ) 

eg.

 SELECT * FROM mytable WHERE (key, value) IN ( SELECT t->>'key', t->>'value' FROM json_array_elements( '[{"key":"key1","value":"value1"}, {"key":"key2","value":"value2"}]' ) AS t(v) ) 

In this case, you only need one VARCHAR binding variable

Answer 3

If you cannot make your decision work, you can also simply combine the key and the value. JDBC may have fewer problems with this more basic syntax:

 select * from mytable where (key||value) in ( ('key1value1'), ('key2value2'), ... ); 

To do this, you first need to convert the Java map to a list with the key and values ​​that have been combined.

Answer 4

Perhaps this is not a problem with the request, perhaps you do not have hstore created or installed.

I suggest the following steps to debug your problem:

• Try a very simple query without any parameters.
• If you have the same problem, check out how to create extensions: http://www.postgresql.org/docs/9.1/static/sql-createextension.html
• Otherwise, if the request is executed correctly, try using a simple parameter (c =? )
• Finally, try named queries. Sort of:

 ParsedSql parsedSql = NamedParameterUtils.parseSqlStatement(namedSql); List<Integer> parameters = new ArrayList<Integer>(); for (A a : paramBeans) parameters.add(a.getId()); MapSqlParameterSource parameterSource = new MapSqlParameterSource(); parameterSource.addValue("placeholder1, parameters); // create SQL with ?'s String sql = NamedParameterUtils.substituteNamedParameters(parsedSql, parameterSource); return sql; 

Also check out this discussion, I find it useful: How to execute SQL queries () of SQL queries using