在子句中使用多列参数化。

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

我在这些行中有一个查询，在这里，我尝试通过比较元组(如in子句中的SQL多列)来筛选结果集:

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

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

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

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

我希望通过Spring JDBC调用这个查询，其中的值对来自列表 >。

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 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've looked at the the hstore extension it mentions. It doesn't seem relevant to my problem.

我已经看了它提到的hstore扩展。这似乎与我的问题无关。

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

是否有一种方法可以在不动态构建SQL和参数列表的情况下完成此任务?

4 个解决方案

#1

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

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

这是Spring文档中提到的:http://docs.spring.io/spring-framework/docs/current/spring- frameworkreference/html/jdbc.html #jdbc-in-子句。

#2

Unfortunately, there isn't any easy way to bind a nested collection bind variable to PostgreSQL. You could generate the following SQL string instead

不幸的是，没有一种简单的方法可以将嵌套的集合绑定变量绑定到PostgreSQL。您可以生成以下SQL字符串。

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

That's a bit of work to keep the SQL string and the variable bindings in sync. You could, however, encode the map as JSON as such:

这是保持SQL字符串和变量绑定同步的一点工作。但是，您可以将映射作为JSON编码:

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

E.g.

如。

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 that case, you would only ever need a single VARCHAR bind variable

在这种情况下，您只需要一个VARCHAR绑定变量。

#3

If you can't get your solution to work, you could also just concatenate the key and value. Perhaps JDBC has less problems with this more basic syntax:

如果你不能让你的解决方案发挥作用，你也可以把关键和价值串联起来。也许JDBC对这种更基本的语法的问题比较少:

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

For this to work, you'd need to first convert your Java Map to a List with the key and values concatenated as well.

为此，您需要首先将Java Map转换为包含键值和值的列表。

#4

-1

It might not be an issue with the query, it might be that you don't have any hstore created/installed.

这可能不是查询的问题，可能是您没有创建/安装hstore。

I suggest the following steps to debug your problem:

我建议以下步骤来调试您的问题:

Try a very simple query, without any parameters.
尝试一个非常简单的查询，没有任何参数。
If you get the same issue, check how to create extensions: http://www.postgresql.org/docs/9.1/static/sql-createextension.html
如果您遇到相同的问题，请检查如何创建扩展:http://www.postgresql.org/docs/9.1/static/sql-createextension.html。
Otherwise, if the query executed correctly, try to use a simple parameter (with =?)
否则，如果查询执行正确，尝试使用一个简单的参数(with =?)
Finally, try named queries. Something like:
最后,尝试命名查询。喜欢的东西:

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 this discussion, I find it useful: How to execute IN() SQL queries with Spring's JDBCTemplate effectivly?

还要检查这个讨论，我发现它很有用:如何用Spring的JDBCTemplate有效地执行()SQL查询?

#1