Re: Inserting JSON via Java PreparedStatment

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