How to insert a updatable record with JSON column in PostgreSQL using JOOQ?

Question

I followed the answer in Is it possible to write a data type Converter to handle postgres JSON columns? to implement the nodeObject converter.

Then I tried to use an updatable record to insert a record, I got "org.jooq.exception.SQLDialectNotSupportedException: Type class org.postgresql.util.PGobject is not supported in dialect POSTGRES" exception."

How can I solve this?

Following is my code:

TableRecord r = create.newRecord(TABLE);
ObjectNode node = JsonNodeFactory.instance.objectNode();
r.setValue(TABLE.JSON_FIELD, node, new JsonObjectConverter());
r.store();

Prior to jOOQ 3.5 (just released), PostgreSQL's JSON type was quite hard to integrate with jOOQ. We've now changed that. Some preliminary information can be seen here on this user group thread. We'll be updating manuals, etc. soon and I'll answer this question with details. Just to be sure, you are using Jackson, right? — Lukas Eder
– Lukas Eder, Commented Nov 21, 2014 at 16:56

Lukas Eder · Accepted Answer · 2020-10-19 13:08:03Z

8

Current jOOQ versions

jOOQ has native support for JSON and JSONB data types, so you don't have to do anything specific.

Historic answer

Since jOOQ 3.5, you can register your own custom data type bindings to the code generator as is documented here:

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

Unlike a Converter, a Binding dictates how your data type is being handled at the JDBC level within jOOQ, without jOOQ knowing about your implementation. I.e., not only will you define how to convert between <T> and <U> types (T = database type, U = user type), but you will also be able to define how such types are:

Rendered as SQL
Bound to PreparedStatements
Bound to SQLOutput
Registered in CallableStatements as OUT parameters
Fetched from ResultSets
Fetched from SQLInput
Fetched from CallableStatements as OUT parameters

An example Binding for use with Jackson to produce JsonNode types is given here:

public class PostgresJSONJacksonJsonNodeBinding 
implements Binding<Object, JsonNode> {

    @Override
    public Converter<Object, JsonNode> converter() {
        return new PostgresJSONJacksonJsonNodeConverter();
    }

    @Override
    public void sql(BindingSQLContext<JsonNode> ctx) throws SQLException {

        // This ::json cast is explicitly needed by PostgreSQL:
        ctx.render().visit(DSL.val(ctx.convert(converter()).value())).sql("::json");
    }

    @Override
    public void register(BindingRegisterContext<JsonNode> ctx) throws SQLException {
        ctx.statement().registerOutParameter(ctx.index(), Types.VARCHAR);
    }

    @Override
    public void set(BindingSetStatementContext<JsonNode> ctx) throws SQLException {
        ctx.statement().setString(
            ctx.index(), 
            Objects.toString(ctx.convert(converter()).value()));
    }

    @Override
    public void get(BindingGetResultSetContext<JsonNode> ctx) throws SQLException {
        ctx.convert(converter()).value(ctx.resultSet().getString(ctx.index()));
    }

    @Override
    public void get(BindingGetStatementContext<JsonNode> ctx) throws SQLException {
        ctx.convert(converter()).value(ctx.statement().getString(ctx.index()));
    }

    // The below methods aren't needed in PostgreSQL:

    @Override
    public void set(BindingSetSQLOutputContext<JsonNode> ctx) throws SQLException {
        throw new SQLFeatureNotSupportedException();
    }

    @Override
    public void get(BindingGetSQLInputContext<JsonNode> ctx) throws SQLException {
        throw new SQLFeatureNotSupportedException();
    }
}

And the Converter that is used above can be seen here:

public class PostgresJSONJacksonJsonNodeConverter 
implements Converter<Object, JsonNode> {
    @Override
    public JsonNode from(Object t) {
        try {
            return t == null 
              ? NullNode.instance 
              : new ObjectMapper().readTree(t + "");
        }
        catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Object to(JsonNode u) {
        try {
            return u == null || u.equals(NullNode.instance) 
              ? null 
              : new ObjectMapper().writeValueAsString(u);
        }
        catch (IOException e) {
            throw new RuntimeException(e);
        }
    }

    @Override
    public Class<Object> fromType() {
        return Object.class;
    }

    @Override
    public Class<JsonNode> toType() {
        return JsonNode.class;
    }
}

You can now register the above binding via the code generator configuration:

<customType>
    <name>com.example.PostgresJSONJacksonJsonNodeBinding</name>
    <type>com.fasterxml.jackson.databind.JsonNode</type>
    <binding>com.example.PostgresJSONJacksonJsonNodeBinding</binding>
</customType>

<forcedType>
    <name>com.example.PostgresJSONJacksonJsonNodeBinding</name>
    <expression>my_schema\.table\.json_field</expression>
</forcedType>

edited Oct 19, 2020 at 13:08

answered Nov 26, 2014 at 10:34

Lukas Eder

223k138 gold badges729 silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

ChromeHearts Over a year ago

Thanks Lukas! Your solution and v3.5 comes at a perfect time!

Jayson Minard Over a year ago

@Lukas, what if we do not know the target type, we want to bind any JSON object to whatever we happen to have as the result type object? All of the examples for converters, type binding is specific DB-Type => User-Type and not DB-Type => ? (all postgres objects to whatever we want at the moment)

Lukas Eder Over a year ago

@JaysonMinard: Hard to say. I'm afraid I don't fully understand your question. Perhaps you could ask a new question on Stack Overflow or on the user group with some explanations? I'll be very happy to answer then.

nairavs Over a year ago

@LukasEder I am not using an autogenerated code, and I need GSON what can I do?

nairavs Over a year ago

@LukasEder done, stackoverflow.com/questions/55595910/…

|

Collectives™ on Stack Overflow

How to insert a updatable record with JSON column in PostgreSQL using JOOQ?

1 Answer 1

Current jOOQ versions

Historic answer

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Current jOOQ versions

Historic answer

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related