Re: Postgresql 9.4 / JSONB / JDBC

With reference to your the message below, and to the JavaDoc for
"javax.json" (JSR-353):
https://json-processing-spec.java.net/nonav/releases/1.0/fcs/javadocs/index.html

I think it's possible to have unambiguous support for setObject(int, ...)
using the standard API in such a way that you can also theoretically wrap
any other JSON API (Jackson or anything else). This would require a
compile-time dependency on the interfaces of the "javax.json" API but would
not require bundling any particular implementation. It would be slightly
more involved with regards to the getObject() call, but I'll get to that.

First off, the "javax.json" API is defined almost entirely in terms of
interfaces, apart from the "javax.json.Json" factory class. I'm not
suggesting the the JDBC driver implements these interfaces at all, it need
only consume these APIs, so nothing to worry about there. The API defines
both a streaming API and an object model API. Therefore, as far as
"setObject(...)" is concerned, it would be good for it to accept either a
JsonArray or a JsonObject as a parameter, in the first case using the
pull-parser paradigm to read in data, and the second case iterating over
keys of the JsonObject (noting of course that values can also be
JsonArray/JsonObject instances). JsonObject also implements the Map
interface, which would conflict with the "hstore" usage of Map already
mentioned by Alexis, but it should be straightforward to differentiate by
ordering "instanceof" checks within the "setObject(...)" implementation.

How would that fit with Jackson or any other API? Jackson does all sorts
of stuff, like databinding, but I think that's way out of scope for the
JDBC driver's core features. However, it doesn't look particularly
complicated to create an implementation of a subset of "javax.json" (the
JsonParser or the JsonStructure objects) that are simple wrappers around a
specific implementation, such as Jackson. That way, the driver could stick
the standards, and anyone could feed data in just by wrapping there own
JSON implementation (or anything else with an equivalent structure) in that
API.

As for "getObject(...)", it's desirable to get stuff out in a similar
format to what went in, but that would require that the driver has access
to any implementation of "javax.json.Json". If "getString(...)" is invoked
on the ResultSet, then a serialized string should be returned, if
"getObject(...)" is invoked, you should probably return a JsonGenerator (to
encourage efficiency by default). Maybe a "PGjsonb" driver-specific class
could provide a more direct approach to get out the data as either a
JsonGenerator or JsonStructure.

I would be favorable to have a PGjsonb object with an API that I could call
into to explicitly provide the Json factory object; I'd like an alternative
to the service loader API as I'm a heavy user of OSGi and sometimes these
two approaches don't play well together when reloading bundles (but I
wouldn't mind it if the driver called the suggested API (explicit setting
of the factory) if it finds something via the service loader API. Lots of
options via system properties or whatever would be nice :-)

Hope that helps,
Christopher

On 21 December 2014 at 11:36, Alexis Meneses <alexis(dot)meneses(at)gmail(dot)com>
wrote:

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