Re: Postgresql 9.4 / JSONB / JDBC

Concerning the first requirement, it's not so trivial within the scope of
the Jdbc interface.
On Jdbc side, it's impossible to guess that MYJSONCOL is a Jsonb datatype
and unfortunately PreparedStatement.setObject(int, Map) is currently bound
to the Hstore datatype.

Moreover, Map isn't enough to represent a Jsonb data as it does not fit a
Json array for example. This would lead to bind many classes in setObject
to the jsonb data-type.

A way to handle it without breaking things is maybe to add some driver
specific methods to provide Jsonb data [eg. PGStatement.setJsonb(int, Map)
and so on] that would require one to cast the Jdbc PreparedStatement into
PGStatement.

Concerning the second, it's what I was thinking of and discussing in my
previous post (
http://postgresql.nabble.com/Postgresql-9-4-JSONB-JDBC-tp5831557p5831576.html).
A "builtin" feature could be provided if based on Java standards.

Alexis

2014-12-20 15:14 GMT+01:00 Christopher BROWN <brown(at)reflexe(dot)fr>:

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