Re: Postgresql 9.4 / JSONB / JDBC

Hello Hans,

If it's possible to bind using a single "?" parameter, that would seem
logic. However, that means (potentially) serializing a large chunk of
data; I was wondering if an API could be provided to pull data in a
streaming way, to keep memory usage under control in "under pressure"
performance scenarii.

--
Christopher

On 21 December 2014 at 19:41, Hans Klett <hansk(at)spectralogic(dot)com> wrote:

> Hello Christopher,
>
> If you’re already using Jackson, can’t you just serialize the json data
> structure and bind it as a single SQL parameter? Jackson can handle writing
> out correct JSON, and prepared statements can handle escaping.
>
> Am I missing something?
>
> Thanks,
> - Hans
>
> From: Christopher BROWN <brown(at)reflexe(dot)fr>
> Date: Saturday, December 20, 2014 at 7:14 AM
> To: Dave Cramer <pg(at)fastcrypt(dot)com>
> Cc: List <pgsql-jdbc(at)postgresql(dot)org>
> Subject: Re: [JDBC] Postgresql 9.4 / JSONB / JDBC
>
> Dave,
>
> I've only just started looking at how JSONB works in Postgresql, and am
> mainly basing ideas on what I can see here:
> http://www.postgresql.org/docs/9.4/static/datatype-json.html
>
> I suppose the basic requirement would be to be able to do stuff like
> this in JDBC:
>
> PreparedStatement stmt = connection.prepareStatement("UPDATE MYTABLE SET
> MYJSONCOL = ? WHERE MYID = ?");
> stmt.setObject(1, map);
> stmt.setInt(2, 123);
>
> ...as opposed to something like this (with a potentially large and
> variable number of "?" markers):
>
> prepareStatement("UPDATE MYTABLE SET MYJSONCOL = '{\"?\":?, \"?\":
> {\"?\":?}}' WHERE MYID = ?");
>
> ...or just creating a normal statement and rolling my own escaping to
> prevent SQL injection.
>
> It would make sense to only allow keys of strings, and values of either
> Map, Collection, Iterable, Stream, array, string, number, boolean, or null
> (although I'm a bit confused about the latter after consulting table 8-23
> from the linked documentation above).
>
> That would be the first requirement.
>
> The second would be an optimisation to avoid having to convert existing
> data structures but might need to be "a capability that you can add" rather
> than "built in". For example, I use the Jackson JSON API (there are others
> of course) and I wouldn't expect it to be bundled into the driver, however
> it'd be useful to pass a JSONNode object into "stmt.setObject" and have a
> way of telling the driver how to serialize it (I don't know if the
> getObject method of a resultset can be forced to use Map or something
> else). Jackson has useful serialization methods of course, so I could just
> write a string suffixed with "::json", but I'm guessing maybe the driver
> provides some sort of optimized binary representation which could be used
> for better performance.
>
> Thanks,
> Christopher
>
>
> On 20 December 2014 at 13:19, Dave Cramer <pg(at)fastcrypt(dot)com> wrote:
>
>> Christopher,
>>
>> No you have not missed anything, there has been nothing done with jsonb
>> and the driver. Since you are the first, perhaps you can give me your wish
>> list ?
>>
>> What would you like to see happen ?
>>
>> Dave Cramer
>>
>> dave.cramer(at)credativ(dot)ca
>> http://www.credativ.ca
>>
>> On 20 December 2014 at 04:44, Christopher BROWN <brown(at)reflexe(dot)fr>
>> wrote:
>>>
>>> Hello,
>>>
>>> I'm new to this list, and have tried searching both the mailing list
>>> archives, and internet in general, for information on how to use the new
>>> JSONB data type with JDBC. Found almost nothing in the mailing list
>>> archives, and internet searches "helpfully" assume I've misspelled "json"
>>> and give me results that are unrelated or refer to the basic "JSON" type
>>> from older Postgresql versions.
>>>
>>> How can this type be used, with simple "flat" key-value collections
>>> (maps), with tree-like maps-of-maps, and (ideally) with JSON APIs such as
>>> Jackson JSON API?
>>>
>>> Is it possible? Is it already described somewhere that I missed?
>>>
>>> Thanks,
>>> Christopher
>>>
>>
>