Обсуждение: Inserting JSON via Java PreparedStatment

On 03/07/2016 05:25 AM, Curt Huffman wrote:
> Hello!
>
> 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'm using JDBC 9.4.1208
>
> I create the JSON object using:
>
> |JsonObjectmbrLogRec =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);|

You lost me here, I thought the object you are building is mbrLogRec?

>
> Using this method, I receive the following error:
>
>     at 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 did try installing the hstore extension, but it then told me that it
> was not an hstore object.
>
> 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.
>
> 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 refernce to these.
> Should I try setAsciiStream? CharacterStream? CLOB? ???
>
> I couldn't find any help or tutes on postgres or the web.
>
> Thanks for any help.
>
> -Curt
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On Mon, 2016-03-07 at 09:32 -0700, David G. Johnston wrote:
>
> > 
> > Hint: You will need to rewrite or cast the expression.
> >
> Take the hint, literally.  You never did show the SQL but usually the
> least complex way to solve this is to indeed transfer the data as a
> string/text and then instruction PostgreSQL to convert (i.e., cast)
> it to json.
>
> SELECT (?)::json;  <-- that ? parameter is seen as text; then you
> convert it.  The parentheses should be optional but I use them to
> emphasize the point.
>
> then
>
> pStmt.setString(1, dtlRec.toString());
>
> David J.
>

For some reason there is no java.sql.Type = JSON. There is ARRAY
though.

I would have written this:-

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

as

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

pStmt.setObject(11, mbrLogRec);

If you pass a string to your prepared statement and want to cast it in
your INSERT/UPDATE statement, you will probably have to include the
double quotes, colons and commas. Never tried it, just a guess.
Could become complicated when you have multiple pairs of JSON
attributes.

E.g.

JsonObject mbrLogRec = Json.createObjectBuilder().add("New MbrID",
newId).add("Old MbrID","fred");


I'm sorry but I don't have time at the moment to knock up a test
program and verify any of this. I'm not an expert on JSON objects in
Java.

Just my two bob's worth.

HTH,
Rob

On 03/07/2016 07:15 PM, Curt Huffman wrote:
> Thanks Rob & David!
>
> I got it to work using the following:
>
>
> String qry = "INSERT INTO event "
> + "(spotid, qid, userid, persid, ...., "
> + "evtvalue, evtdt, evtjson) "
> + "VALUES(?,?,?,?,?,?,?,?,?,?,to_json(?::json));";
>
> and
> pStmt.setString (11,dtlRec.toString());
>
> (another suggestion was to use: cast(?asjson)   which I haven't tried yet.)
>
> This worked with an ultra-simple, 1-pair json object. {"New MbrID":34}
>   I'll try it with more complex structures next.
>
> Any opinions on using the postgres function, to_json, over the cast?
>
> However, from my (limited) understanding, I think I am now just
> incurring additional processing overhead from all of this.
> I think that I am stuffing text into a JSON object, then converting it
> into a string for the preparedStatment, which then passes it to the JDBC
> driver to re-convert it into a JSON object, and gets ultimately stored
> as a text string in the column?  Is that correct?
> I suspect I'll have to reverse the process to read it back out, yes?
>
> Follow-up questions:
> 1) Since I'm not (yet) using JSONB, but just regular JSON column, is
> there much point to even using a JSON column?
> 2) Will this method also work for JSONB column types?
>
> Finally, I humbly request a small addition to the postgres doco that
> illustrates this and the 'best' way to insert, manipulate, and retrieve
> JSON in postgres.  Maybe even a small tutorial?

The below?:

http://www.postgresql.org/docs/9.5/interactive/datatype-json.html#JSON-KEYS-ELEMENTS

http://www.postgresql.org/docs/9.5/interactive/functions-json.html


>
> Thanks again!
> -Curt
>


--
Adrian Klaver
adrian.klaver@aklaver.com