Postgres HSTORE data type

[Jacob Gilley]

Hello,

We're currently using JOOQ on a project and I am very excited to have
it in our technology stack. I was wondering if you could provide some
information on using JOOQ with the Postgres HSTORE data type? All of
the HSTORE manipulation functions are generated in JOOQ just fine. But
when selecting records of this type, JOOQ simply returns a
java.lang.Object. Any advice on how to get this working? Thanks in
advance and thanks again for this wonderful API.

Regards,
Jake

[Lukas Eder]

Hello Jake,

I'm moving this discussion over to the jOOQ-user group...

jOOQ currently doesn't support Postgres' HSTORE data type. There is a
pending feature request for this on the roadmap:
https://sourceforge.net/apps/trac/jooq/ticket/994

Unfortunately, I have no experience with that data type. From the
documentation, it looks as though an integration is quite complex.
The most important question for jOOQ supporting it, is how does the
Postgres JDBC driver support and transport that type? Is there a
common Java representation for that type, that jOOQ could use to bind
for <T>?

Cheers
Lukas

2012/1/23 Jacob Gilley <jgi...@interlegis.com>:

[Lukas Eder]

Hi Jake,

This looks interesting. I'll see what I can do. I already incorporated
a PGobject class that I've found somewhere undocumented in the JDBC
driver. I wrote a parser to deserialise those objects:

https://github.com/lukaseder/jOOQ/blob/master/jOOQ/src/main/java/org/jooq/util/postgres/PGobjectParser.java

Maybe something similar can be done for PGHstore

Cheers
Lukas

2012/1/24 Jacob Gilley <jgi...@interlegis.com>:
> You can download the PGHStore data type implementation from
> http://archives.postgresql.org/pgsql-jdbc/2009-12/txt0g3zCFNPnA.txt. The
> comments header states that it will be added to the driver later, but not
> sure if it has been or not. Once you have the class loaded, you can register
> the type with an open Postgres JDBC connection like so:
>
> ((org.postgresql.PGConnection)
> myPostgreJdbcConnection).addDataType("hstore",
> org.postgresql.util.PGHStore.class);
> Since JOOQ generates the column field type as java.lang.Object, you'll need
> to cast it to PGHStore so you can access the underlying map:
>
> PGHStore htoreField = (PGHStore) JOOQRecord.getMyHStoreField();
>
> hstoreField.put("key", "value");
>
> String mapValue = (String) hstoreField.get("key");
>
> It's kinda hokey, but it works. Hope that helps!
>
> Regards,
> Jake
> On Tue, Jan 24, 2012 at 2:37 PM, Lukas Eder <lukas...@gmail.com> wrote:
>>
>> Hi Jake,
>>
>> > Thanks for the reply, Lukas. I found a workaround by simply registering
>> > a
>> > custom data type handler to the Postgres JDBC Connection. Then just cast
>> > the
>> > record field to the custom data type object class. Seems to work like a
>> > charm.
>>
>> Great! If you get this working more stably, I'd be curious to see some
>> sample code. This might be an interesting thing to document in the
>> "advanced" section of the jOOQ manual, as other users might profit
>> from that information as well.
>>
>> > It doesn't look like Postgres has direct support for this type yet,
>> > but I believe it's on their radar.
>>
>> Unfortunately, the Postgres JDBC driver lacks a lot of implementation.
>> For instance, regular user defined types are not really supported
>> either, unless you're ready to parse their string representation
>> manually...
>>
>> > Great work, by the way, on this API. It
>> > took a little convincing on my part to get our team off of the Hibernate
>> > bandwagon, but everyone is really starting to take a liking to how well
>> > thought-out the implementation is. Kudos for bringing some sanity back
>> > to
>> > Java!
>>
>> Thank you! I'm sure when you start using stored functions and
>> Postgres' advanced features, you'll soon get to the limits of
>> Hibernate.
>> Feel free to publish a success story on your blog, if you have time.
>> Also, if you find something is missing, feel free to create another
>> feature request.
>>
>> Cheers
>> Lukas
>
>

[dmitryk...@gmail.com]

hi, Jacob! Could you post an example of using hstore in JOOQ?

[Lukas Eder]

Some time has passed since this thread has started. An example of how to use PostgreSQL specific data types is documented in the manual:

http://www.jooq.org/doc/3.5/manual/code-generation/custom-data-type-bindings/

Cheers,

Lukas

--
You received this message because you are subscribed to the Google Groups "jOOQ Developer Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to jooq-develope...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.