java - json 中的 JDBC Prepared 语句参数

Question

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

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

现在我想在数据内的对象数组中找到所有具有特定 id 的对象的行。以下查询从 PSQL 控制台完美运行:

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

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

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

在这里，当我尝试设置参数时，我得到了这个异常:

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

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

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

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

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

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

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

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

Answer 1

实际上，我通过 Github 联系了 JDBC 开发人员经过一些讨论，目前最好的解决方案似乎是准备如下语句:

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

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

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

这不是一个完美的解决方案，但由于缺乏服务器功能，这似乎是最好的解决方案。最后它还不错，因为(理论上)没有 SQL 注入(inject)的风险。

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