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

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?

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

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

You have two options:

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

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

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.

As others have mentioned, your SQL string needs to explicitly cast the bind value to the PostgreSQL json or jsonb type:

insert into t (id, j) values (?, ?::json)

Now you can bind the string value. Alternatively, you can use a library that can do it, for example jOOQ (works out of the box) or Hibernate (using a third party UserType registration). The benefits of this is that you don't have to think about this every time you bind such a variable (or read it). A jOOQ example:

ctx.insertInto(T)
   .columns(T.ID, T.J)
   .values(1, JSON.valueOf("[1, 2, 3]"))
   .execute();

Behind the scenes, the same cast as above is always generated, whenever you work with this JSON (or JSONB) data type.

(Disclaimer: I work for the company behind jOOQ)

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

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

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

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

String yourJsonString = someJsonObject.toString();

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

this worked for me.