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