Search code examples
javasqlpostgresqlpostgresql-9.3jooq

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


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();

Solution

  • 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>