使用多列的参数化IN子句 [英] parameterized IN clause using multiple columns

问题描述

我有这样的查询，我试图通过比较元组来过滤结果集（比如 IN子句中的SQL多列）：

I have a query along these lines, where I am trying to filter the result set by comparing tuples (like SQL multiple columns in IN clause):

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

这是有效的语法，在我的Postgres 9.3数据库上运行正常。

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

我想通过Spring JDBC调用此查询，其中值值对来自列表< Map< String，String> ;> 。

I want to invoke this query through Spring JDBC where the in value pairs come from a List<Map<String,String>>.

做这样的事情会很好：

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 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]

我看过它提到的hstore扩展名。它似乎与我的问题无关。

I've looked at the the hstore extension it mentions. It doesn't seem relevant to my problem.

有没有办法在不动态构建SQL和参数列表的情况下完成此操作？

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

推荐答案

您所要做的就是传递一个数组列表，其中每个数组都包含一个键和值，如下所示：

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

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

这在Spring文档中提到： http://docs.spring.io/spring-framework/docs/ current / spring-framework-reference / html / jdbc.html #jdbc-in-clause

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

这篇关于使用多列的参数化IN子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！