如何使用Java preparedStatement将JSON对象插入Postgres？

Question

我正在努力将JSON对象插入我的postgres v9.4 DB。我已经将名为“evtjson”的列定义为类型json（而不是jsonb）。

我试图在Java（jdk1.8）中使用准备好的语句将Json对象（使用JEEjavax.json库构建）插入到列中，但我一直遇到SQLException错误。

我使用以下方法创建JSON对象：

JsonObject mbrLogRec = Json.createObjectBuilder().build();
…
mbrLogRec = Json.createObjectBuilder()
                .add("New MbrID", newId)
                .build();

然后，我将这个对象作为参数传递给另一个方法，以使用准备好的语句将其写入DB。（以及其他几个字段）如下所示：

pStmt.setObject(11, dtlRec);

使用此方法，我收到以下错误：

组织。postgresql。util。PSQLException：未安装hstore扩展。在org。postgresql。jdbc。PgPreparedStatement。org上的setMap（PgPreparedStatement.java:553）。postgresql。jdbc。PgPreparedStatement。setObject（PgPreparedStatement.java:1036）

我也尝试过：

pStmt.setString(11, dtlRec.toString());
pStmt.setObject(11, dtlRec.toString());

这会产生不同的错误：

事件JSON:{“New MbrID”：29}

SQLException： ERROR：列"evtjson"是json类型，但表达式类型不同

提示：您需要重写或强制转换表达式。

但是，至少这告诉我DB正在将列识别为JSON类型。我确实尝试安装了hstore扩展，但它告诉我它不是hstore对象。

OracleDocs显示了许多不同的方法来设置准备好的语句中的参数值，但是如果有人知道答案，我宁愿不尝试所有方法。（http://docs.oracle.com/javase/8/docs/api/java/sql/PreparedStatement.html）这些还引用了一个附加参数SQLType，但是我找不到任何对它们的引用。

我应该尝试setAsciiStream吗？特征流？CLOB？

Answer 1

您有两种选择：

>

使用语句。设置字符串（jsonStr），然后在sql语句中处理转换：

PreparedStatement statement = con.prepareStatement(
  "insert into table (jsonColumn) values (?::json)");
statement.setString(1, jsonStr);

另一种选择是使用PGobject创建自定义值包装。

PGobject jsonObject = new PGobject();
PreparedStatement statement = con.prepareStatement(
  "insert into table (jsonColumn) values (?)");
jsonObject.setType("json");
jsonObject.setValue(jsonStr);
statement.setObject(1, jsonObject);

我个人更喜欢后者，因为查询更干净

Answer 2

你可以这样做，你只需要json字符串：

将查询更改为：

String query = "INSERT INTO table (json_field) VALUES (to_json(?::json))"

并将参数设置为字符串。

pStmt.setString(1, json);

Answer 3

这种行为非常恼人，因为当在SQL命令中用作文字字符串时，JSON字符串可以毫无问题地被接受。

postgres驱动程序Github存储库中已经存在一个问题（即使问题似乎是服务器端处理）。

除了在sql字符串中使用cast（请参阅@a\u horse\u with_no\u name）的答案），本期作者还提供了两个额外的解决方案：

1. 在JDBC连接URL/options中使用参数stringtype=unspecified

这告诉PostgreSQL，所有text或varchar参数实际上都是未知类型的，因此可以更自由地推断其类型。

 PGobject jsonObject = new PGobject();
 jsonObject.setType("json");
 jsonObject.setValue(yourJsonString);
 pstmt.setObject(11, jsonObject);