Re: Inserting JSON via Java PreparedStatment

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(? as json) 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?

Thanks again!
-Curt

On Tue, Mar 8, 2016 at 4:18 AM, rob stone <floriparob(at)gmail(dot)com> wrote:

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