From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Curt Huffman <curt(dot)huffman(at)gmail(dot)com> |
Cc: | rob stone <floriparob(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Inserting JSON via Java PreparedStatment |
Date: | 2016-03-08 03:35:06 |
Message-ID: | CAKFQuwZgOwipS3F0hy2SqO_138Bur639L1mfHV2L+hZpmYBDSg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On Mon, Mar 7, 2016 at 8:15 PM, Curt Huffman <curt(dot)huffman(at)gmail(dot)com> 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));";
>
>
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".
and
> pStmt.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.
From | Date | Subject | |
---|---|---|---|
Next Message | david | 2016-03-08 04:00:14 | Re: Does a call to a language handler provide a context/session, and somewhere to keep session data? |
Previous Message | Curt Huffman | 2016-03-08 03:15:36 | Re: Inserting JSON via Java PreparedStatment |