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

How can I Insert JSON object into Postgres using Java preparedStatement?

提问by MaybeWeAreAllRobots

I'm struggling to insert a JSON object into my postgres v9.4 DB. I have defined the column called "evtjson" as type json(not jsonb).

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

I am trying to use a prepared statement in Java (jdk1.8) to insert a Json object (built using JEE javax.json libraries) into the column, but I keep running into SQLException errors.

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

I create the JSON object using:

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

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

Then I pass this object as a parameter to another method to write it to the DB using a prepared statement. (along with several other fields) As:

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

pStmt.setObject(11, dtlRec);

Using this method, I receive the following error:

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

org.postgresql.util.PSQLException: No hstore extension installed. at org.postgresql.jdbc.PgPreparedStatement.setMap(PgPreparedStatement.java:553) at org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:1036)

org.postgresql.util.PSQLException: 没有安装 hstore 扩展。在 org.postgresql.jdbc.PgPreparedStatement.setMap(PgPreparedStatement.java:553) 在 org.postgresql.jdbc.PgPreparedStatement.setObject(PgPreparedStatement.java:1036)

I have also tried:

我也试过：

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

Which produce a different error:

产生不同的错误：

Event JSON: {"New MbrID":29}

SQLException: ERROR: column "evtjson" is of type json but expression is of type character varying

Hint: You will need to rewrite or cast the expression.

事件 JSON: {"New MbrID":29}

SQLException：错误：列“evtjson”的类型是 json 但表达式的类型是不同的

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

But, at least this tells me that the DB is recognizing the column as type JSON. I did try installing the hstore extension, but it then told me that it was not an hstore object.

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

OracleDocs shows a number of various methods to set the parameter value in the preparedStatement, but I'd rather not try them all if someone knows the answer. (http://docs.oracle.com/javase/8/docs/api/java/sql/PreparedStatement.html) These also reference an additional parameter, SQLType, but I can't find any reference to these.

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

Should I try setAsciiStream? CharacterStream? CLOB?

我应该试试setAsciiStream吗？CharacterStream? 克洛布？

回答by a_horse_with_no_name

Passing the JSON as a String is the right approach, but as the error message tells you, you need to cast the parameterin the INSERTstatement to a JSONvalue:

将 JSON 作为字符串传递是正确的方法，但正如错误消息告诉您的那样，您需要将语句中的参数转换INSERT为一个JSON值：

insert into the_table
   (.., evtjson, ..) 
values 
   (.., cast(? as json), ..)

Then you can use pStmt.setString(11, dtlRec.toString())to pass the value

然后你可以使用pStmt.setString(11, dtlRec.toString())传递值

回答by wero

This behaviour is quite annoying since JSON strings are accepted without problems when used as literal strings in SQL commands.

这种行为很烦人，因为当在 SQL 命令中用作文字字符串时，JSON 字符串被接受没有问题。

There is a already an issuefor this in the postgres driver Github repository (even if the problem seems the be the serverside processing).

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

Besides using a cast (see answer of @a_horse_with_no_name) in the sql string, the issue author offers two additional solutions:

除了在 sql 字符串中使用强制转换（参见@a_horse_with_no_name 的答案）之外，问题作者还提供了两个额外的解决方案：

1. Use a parameter stringtype=unspecifiedin the JDBC connection URL/options.

1. 使用stringtype=unspecifiedJDBC 连接 URL/选项中的参数。

This tells PostgreSQL that all text or varchar parameters are actually of unknown type, letting it infer their types more freely.

这告诉 PostgreSQL 所有 text 或 varchar 参数实际上都是未知类型，让它更自由地推断它们的类型。

1. Wrap the parameter in a org.postgresql.util.PGobject:

2. 将参数包装在 a 中org.postgresql.util.PGobject：

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

回答by Tiago

You can do it like this and you just need the json string:

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

Change the query to:

将查询更改为：

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

And set the parameter as a String.

并将参数设置为字符串。

pStmt.setString(1, json);

回答by Komal Thakur

Instead of passing json object pass its string value and cast it to json in the query. Example:

不是传递 json 对象，而是传递其字符串值并将其转换为查询中的 json。例子：

JSONObject someJsonObject=..........

String yourJsonString = someJsonObject.toString();

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

this worked for me.

这对我有用。

回答by pedram bashiri

You have two options:

您有两个选择：

1. Use statement.setString(jsonStr) and then handle the conversion in the sql statement:

1. 使用 statement.setString(jsonStr) 然后处理sql语句中的转换：

`

`

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

2. Another option is to use PGobject to create a custom value wrapper.

2. Another option is to use PGobject to create a custom value wrapper.

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

` I personally prefer the latter as the query is cleaner

` 我个人更喜欢后者，因为查询更清晰

回答by Uri Loya

if using spring boot: adding the following line to application.properties helped:

如果使用 spring boot：将以下行添加到 application.properties 有帮助：

spring.datasource.hikari.data-source-properties.stringtype=unspecified

as Wero wrote:

正如韦罗所写：

This tells PostgreSQL that all text or varchar parameters are actually of unknown type

这告诉 PostgreSQL 所有 text 或 varchar 参数实际上都是未知类型