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

Question

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).

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?

Answer 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:

1. 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.

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

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

Answer 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);