Re: json type

On 05/22/2012 12:57 AM, Mikko Tiihonen wrote:

> Unfortunately there is no standard json type on java side so adding
> support for json to postgres jdbc is not straight forward.
While true, you can accept it as a java.lang.String and feed it into
your JSON library of choice. Most people will be doing that directly or
via a facade that takes care of it for them, like a custom JPA mapping type.

Personally I wouldn't want PgJDBC trying to convert the serialised JSON
representations for me, I'd just land up fighting it the first time I
wanted to do something it hadn't anticipated.

>
> I've been thinking that we could have a code that allows the postgres
> jdbc user to set the json class to use (gson, org.json, json-simple,
> jackson) and add some reflection code into the jdbc driver to return
> path so that it works nicely. And of course the reverse reflection
> code for sending json to database.

IMO, reflection + database access = ick.

Also, remember that people often won't want PgJDBC to convert the result
into a smart Java JSON object representation like a Jackson JSONNode.
They'll want to use data binding to convert the JSON to/from an object
tree. For that purpose the best thing PgJDBC can do is get out of the
way and give the user access to the raw JSON data to feed into
Jackson/whatever.

Reflection isn't as slow as it was, but it's still unusable under
SecurityManager control and can't be inlined/JIT'd properly. I think
reflection is the wrong answer here. We'd be better off bundling
implementations of an adapter interface and allowing which one to be
specified at runtime on a system-wide or per-connection basis. The
desired adapter would be instantiated (as a static member for the global
setting controlled by system properties, or as a Connection member for
the per-connection version) and would be used for all conversions
between JSON text received from Pg and the user's desired JSON type. The
default implementation if no other one was selected would be to use
java.lang.String as the JSON type, or maybe to return an InputStream or
a Reader to allow streaming access.

The only worry I have with an adapter interface approach also exists
with a reflection-based approach, and that's the inability to stream
JSON in and out for large values. The adapter interface should probably
also accept stream-oriented access, but that'll require a bit more
investigation into the streaming interfaces of the various JSON
libraries currently available.

Think for a simple non-streaming version:

public interface PgJSONAdapter<JSONType> {
public JSONType toJSON(String jsonText);
public String fromJSON(JSONType jsonObject);
}

public class PgJSONAdapterString implements PgJSONAdapter<String> {
public String toJSON(String jsonText) { return jsonText; }
public String fromJSON(String jsonObject) { return jsonObject; }
}

public class PgJSONAdapterJacksonTree implements PgJSONAdapter<JsonNode> {
public JsonNode toJSON(String jsonText) {
return new ObjectMapper().readValue(jsonText, JsonNode.class);
}
public String fromJSON(JsonNode jsonObject) {
return jsonObject.toString();
}
}

You wouldn't get specific param and return types on the get/set json
methods, but you don't get that with a reflection approach either, and
the JDBC API doesn't really allow us to extend Connection, Statement,
ResultSet, etc nicely anyway (not least as these are often wrapped by
connection pools). There's nothing wrong with using
getObject()/setObject() for JSON IMO.

Look at http://wiki.fasterxml.com/JacksonInFiveMinutes and you'll
quickly see how hard it'd be to come up with any scheme that'll satisfy
all the ways people are going to want to work with JSON data. Much
easier to just let Pg provide and accept strings and streams, plus
*maybe* a simple translator adapter. I'm honestly not even convinced
that's worth doing until/unless a future JSR promotes a JSON library to
a standard API and part of the JDK.

--
Craig Ringer