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

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>