JSON中的JDBC Prepared语句参数 [英] JDBC Prepared statement parameter inside json

问题描述

我有一个表，该表的数据列的结构类似于以下内容:

I have a table which has a data column with an structure similar to following:

{"title": "some title", "objects": [{"id": "id1"}, {"id": "id2"}]}

现在，我想在数据中的对象数组中查找所有具有特定ID的对象的行.以下查询可从PSQL控制台完美运行:

now I want to find all rows that have an object with specific id in their objects array inside data. Following query works perfectly from PSQL console:

SELECT id, data FROM table_name WHERE data->'objects' @> '[{"id": "id1"}]'

但是，我无法使它作为JDBC驱动程序上的准备语句工作. id的值应该是一个参数，因此我尝试将其作为传递给connection.prepareStatement(query);的字符串:

however I can not get this to work as a prepared statement over JDBC driver. the value for id should be a parameter, so I tried this as the string that is passed to connection.prepareStatement(query);:

"SELECT id, data FROM table_name WHERE data->'objects' @> '[{\"id\": ?}]'"

在这里尝试设置参数时，出现此异常:

here when I try to set arguments I get this exception:

org.postgresql.util.PSQLException: The column index is out of range: 1, number of columns: 0.

当我尝试以下任何一项时，参数设置正确:

when I try any of the following the argument is set correctly:

"SELECT id, data FROM table_name WHERE data->'objects' @> [{\"id\": ?}]"
"SELECT id, data FROM table_name WHERE data->'objects' @> [{'id': ?}]"

但结果显然不是正确格式的查询:

but the result is obviously not a properly formatted query:

SELECT id, data FROM table_name WHERE data->'objects' @> [{"id": 'id1'}]
SELECT id, data FROM table_name WHERE data->'objects' @> [{'id': 'id1'}]

在两种情况下，我都会遇到以下异常:

in both cases I get following exception:

org.postgresql.util.PSQLException: ERROR: syntax error at or near "["

在JSON中设置参数的正确语法是什么? 我正在使用PostgreSql 9.5

What is the correct syntax to set a parameter inside JSON? I'm using PostgreSql 9.5

推荐答案

我实际上是通过

I actually reached out to JDBC developers over at Github and after some discussions it seems that currently the best solution is to have the prepared statement as follows:

String query = "SELECT id, data FROM table_name WHERE data->'objects' @> ?::jsonb";

并将整个搜索条件作为字符串化的JSON对象传递给参数:

and pass the whole search criteria as stringified JSON object for the parameter:

PreparedStatement st = connection.prepareStatement(query);
st.setString(1, "[\"id\":" + "id1" + "]");
st.executeQuery();

那不是一个完美的解决方案，但是由于缺乏服务器功能，这似乎是最好的解决方案.最后，它还没有理论上的SQL注入风险那么糟糕.

thats not a perfect solution but seems the best possible due to lack of server capabilities. At the end its not so bad as there is (theoretically) no risk of SQL injection.

有关链接的Github问题的更多详细信息.

More details on the linked Github issue.

这篇关于JSON中的JDBC Prepared语句参数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！