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