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));";
This is redundant (though possibly the to_json become a no-op in this case, idk). Either cast (which is what you are doing when you say "?::json") so pass the text through the to_json function. What you are saying here to "please convert this json value I am handing you to....json".
andpStmt.setString (11,dtlRec.toString());(another suggestion was to use: cast(? as json) which I haven't tried yet.)
This has the benefit of being standard conforming, the "::" syntax is a PostgreSQL-ism.
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?
I'm reasonably certain there is no actual difference between the two so whatever syntax seems more natural.
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?
Yes, you are using "text" as a serialization feature and by using a typed json column you are validating/constraining the text being sent to conform to JSON structure. Unless you have solid and overwhelming proof that using JSON is unacceptably slow you should use it from a "proper model" perspective.
2) Will this method also work for JSONB column types?
This is how you do type conversion in PostgreSQL, there is nothing here (aside from the unnecessary to_json function call) that is json/jsonb specific.
David J.
