How can I Insert JSON object into Postgres using Java preparedStatement?
Solution 1
This behaviour is quite annoying since JSON strings are accepted without problems when used as literal strings in SQL commands.
There is a already an issue for this in the postgres driver Github repository (even if the problem seems the be the serverside processing).
Besides using a cast (see answer of @a_horse_with_no_name) in the sql string, the issue author offers two additional solutions:
- Use a parameter
stringtype=unspecified
in the JDBC connection URL/options.
This tells PostgreSQL that all text or varchar parameters are actually of unknown type, letting it infer their types more freely.
- Wrap the parameter in a
org.postgresql.util.PGobject
:
PGobject jsonObject = new PGobject();
jsonObject.setType("json");
jsonObject.setValue(yourJsonString);
pstmt.setObject(11, jsonObject);
Solution 2
You can do it like this and you just need the json string:
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);
Solution 3
You have two options:
- Use statement.setString(jsonStr) and then handle the conversion in the sql statement:
`
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.
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
Solution 4
Passing the JSON as a String is the right approach, but as the error message tells you, you need to cast the parameter in the INSERT
statement to a JSON
value:
insert into the_table
(.., evtjson, ..)
values
(.., cast(? as json), ..)
Then you can use pStmt.setString(11, dtlRec.toString())
to pass the value
Solution 5
Most answers here defines ways of inserting into postgres json field with jdbc in a non-standard way, ie. it is db implementation specific. If you need to insert a java string into a postgres json field with pure jdbc and pure sql use:
preparedStatement.setObject(1, "{}", java.sql.Types.OTHER)
This will make the postgres jdbc driver (tested with org.postgresql:postgresql:42.2.19) convert the java string to the json type. It will also validate the string as being a valid json representation, something that various answers using implicit string casts does not do - resulting in the possibility of corrupt persisted json data.
MaybeWeAreAllRobots
Updated on July 08, 2022Comments
-
MaybeWeAreAllRobots almost 2 years
I’m struggling to insert a JSON object into my postgres v9.4 DB. I have defined the column called "evtjson" as type
json
(notjsonb
).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.
I create the JSON object using:
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)
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.
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.
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.
Should I try
setAsciiStream
?CharacterStream
? CLOB?