Обсуждение: Patch: Add support for hstore extension - and map it to/from java.util.Map
Hi, Here is a patch to enable initial support for hstore in the jdbc driver. With the patch using ResultSet.getObject() on a hstore column and it returns a HashMap<String, String> Similarly a Statement.setObject(Map<Object,Object> will send the data to backend using hstore format. The patch supports both text and binary transfer modes. However the current patch does not automatically enable the fasterbinary transfer more, but instead user has to currently add binaryTransferEnable=<hstore-oid> to connection parameters. I'm still pondering how to enable the binary transfers automatically without the overhead of fetching the hstore oid on eachconnection startup. - can it be cached to some static cache based on connection url+login information? - if not then a way to lazy evaluate it needs to be implemented -Mikko
Mikko, I have applied this patch to master, thanks! Dave Cramer dave.cramer(at)credativ(dot)ca http://www.credativ.ca On Thu, May 10, 2012 at 5:17 AM, Mikko Tiihonen <mikko.tiihonen@nitorcreations.com> wrote: > Hi, > > Here is a patch to enable initial support for hstore in the jdbc driver. > > With the patch using ResultSet.getObject() on a hstore column and it returns > a HashMap<String, String> > Similarly a Statement.setObject(Map<Object,Object> will send the data to > backend using hstore format. > > The patch supports both text and binary transfer modes. However the current > patch does not automatically enable the faster binary transfer more, but > instead user has to currently add binaryTransferEnable=<hstore-oid> to > connection parameters. > > I'm still pondering how to enable the binary transfers automatically without > the overhead of fetching the hstore oid on each connection startup. > - can it be cached to some static cache based on connection url+login > information? > - if not then a way to lazy evaluate it needs to be implemented > > -Mikko > > > -- > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc >
Re: Patch: Add support for hstore extension - and map it to/from java.util.Map
От
Craig Ringer
Дата:
On 05/10/2012 05:17 PM, Mikko Tiihonen wrote: > Hi, > > Here is a patch to enable initial support for hstore in the jdbc driver. > > With the patch using ResultSet.getObject() on a hstore column and it > returns a HashMap<String, String> > Similarly a Statement.setObject(Map<Object,Object> will send the data > to backend using hstore format. That's absolutely brilliant, thankyou! Of course, now there's a `json' type that folks will probably use much of the time instead, not least because it should be easier to work with from JDBC. I doubt it'll be as efficient and fast as hstore for single-level key/value info though. -- Craig Ringer
On 05/21/2012 11:18 AM, Craig Ringer wrote: > On 05/10/2012 05:17 PM, Mikko Tiihonen wrote: >> Hi, >> >> Here is a patch to enable initial support for hstore in the jdbc driver. >> >> With the patch using ResultSet.getObject() on a hstore column and it returns a HashMap<String, String> >> Similarly a Statement.setObject(Map<Object,Object> will send the data to backend using hstore format. > > That's absolutely brilliant, thankyou! > > Of course, now there's a `json' type that folks will probably use much of the time instead, not least because it shouldbe easier to work with from JDBC. I > doubt it'll be as efficient and fast as hstore for single-level key/value info though. Unfortunately there is no standard json type on java side so adding support for json to postgres jdbc is not straight forward. 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 forsending json to database. -Mikko
On 05/22/2012 12:57 AM, Mikko Tiihonen wrote:
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.
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
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
Is it path for 9.1 or 9.2? -Shijun On 5/21/12 4:18 AM, "Craig Ringer" <ringerc@ringerc.id.au> wrote: >On 05/10/2012 05:17 PM, Mikko Tiihonen wrote: >> Hi, >> >> Here is a patch to enable initial support for hstore in the jdbc driver. >> >> With the patch using ResultSet.getObject() on a hstore column and it >> returns a HashMap<String, String> >> Similarly a Statement.setObject(Map<Object,Object> will send the data >> to backend using hstore format. > >That's absolutely brilliant, thankyou! > >Of course, now there's a `json' type that folks will probably use much >of the time instead, not least because it should be easier to work with >from JDBC. I doubt it'll be as efficient and fast as hstore for >single-level key/value info though. > >-- >Craig Ringer > >-- >Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) >To make changes to your subscription: >http://www.postgresql.org/mailpref/pgsql-jdbc