posgtresql json data type

[John]

Hi, 

I just started using JOOQ in one of the project, i am not able to find out a way to insert a row which has a column of postgresql's json data type. Any help on this regard? 

Thanks,

John

[Lukas Eder]

Hi John,

The last time this was discussed on the user group, we came to the conclusion that it is not that simple to integrate PostgreSQL's JSON support in jOOQ because of the limitations in the PostgreSQL JDBC driver. I'm not up to date with this evolution, though. How would you insert your JSON data through JDBC directly?

Cheers

Lukas

Relevant discussions / issues:

- https://groups.google.com/forum/#!msg/jooq-user/GDlOU0e8B9A/U4EcdtxJT7AJ

- https://github.com/jOOQ/jOOQ/issues/2788

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

[yehoh...@gmail.com]

I am using the below approach to directly insert json through spring jdbc templates.

PGobject jsonObject = new PGobject();

jsonObject.setType("json");

jsonObject.setValue(new Gson().toJson(attributes));

String sql = "INSERT INTO table (id, details) values (?, ?)";

new JdbcTemplate(dataSource).update(sql, "id", jsonObject);

Thanks,

John

[Lukas Eder]

Hi John,

Thanks, that's what I had thought. This PGobject seems to be the PostgreSQL's JDBC driver's default dump for all sorts of vendor-specific types.

I suspect the way forward for jOOQ here is to

- Introduce support for a new SQLDataType.JSON type

- Include a default, ASL 2.0 licensed JSON implementation in jOOQ (preferably one that implements javax.json)

- Allow for overriding this implementation through converters

This solution should also enable JSON storage in other databases, that do not natively support this data type.

Cheers

Lukas

--

[f...@changeyourflight.com]

Hi Lukas.

Is there any news in this regards?

(Sorry if it was already discussed on the group)

Thanks

[Lukas Eder]

Hello,

Thanks for your enquiry. I see that you've already discovered the relevant documentation about how to integrate PostgreSQL JSON data types into jOOQ.

https://github.com/jOOQ/jOOQ/issues/3248#issuecomment-76142832

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

Right now, this is the only example that we have in the manual. We'll be adding more examples in the near future. If you have specific questions, just ask. We'll be more than happy to help

Best Regards,

Lukas

[Facundo Farias]

Yeah, sorry for the spam :/

I do have one more question: What about if I want to used without code generation!? Where should I register the bindings?

Thanks

--
You received this message because you are subscribed to a topic in the Google Groups "jOOQ User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/jooq-user/lE0TLxXEsCY/unsubscribe.
To unsubscribe from this group and all its topics, send an email to jooq-user+...@googlegroups.com.

[Lukas Eder]

2015-02-26 10:19 GMT+01:00 Facundo Farias <f...@changeyourflight.com>:

Yeah, sorry for the spam :/

No problem at all. It's good for future readers to find cross-links that eventually lead to the right place.

 

I do have one more question: What about if I want to used without code generation!? Where should I register the bindings?

This is a good question. I realise that we'll need some documentation about that... I have registered an issue for this:

https://github.com/jOOQ/jOOQ/issues/4099

Essentially, you can use the same API as jOOQ does, internally, to create a DataType that references a Binding:

http://www.jooq.org/javadoc/latest/org/jooq/DataType.html#asConvertedDataType-org.jooq.Binding-

That DataType can then be used to construct new Fields, e.g. via:

http://www.jooq.org/javadoc/latest/org/jooq/impl/DSL.html#field-java.lang.String-org.jooq.DataType-

Let me know if this helps

Lukas