Re: Postgresql 9.4 / JSONB / JDBC - Mailing list pgsql-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@reflexe.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@fastcrypt.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@reflexe.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