如何使用JOOQ在PostgreSQL中插入带有JSON列的可更新记录？

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

我跟着答案是否有可能写一个数据类型转换器来处理postgres JSON列?实现nodeObject转换器。

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."

然后我尝试使用可更新的记录来插入记录,我得到了“org.jooq.exception.SQLDialectNotSupportedException:在方言POSTGRES中不支持类型类org.postgresql.util.PGobject”异常。

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

1 个解决方案

#1

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

从jOOQ 3.5开始,您可以将自己的自定义数据类型绑定注册到代码生成器,如下所示:

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:

与转换器不同,Binding指示如何在jOOQ内的JDBC级别处理数据类型,而不知道您的实现。即,您不仅要定义如何在和类型之间进行转换(T =数据库类型,U =用户类型),而且还可以定义这些类型的方式:

Rendered as SQL

呈现为SQL

Bound to PreparedStatements

绑定到PreparedStatements

Bound to SQLOutput

绑定到SQLOutput

Registered in CallableStatements as OUT parameters

在CallableStatements中注册为OUT参数

Fetched from ResultSets

从ResultSet获取

Fetched from SQLInput

从SQLInput获取

Fetched from CallableStatements as OUT parameters

从CallableStatements获取为OUT参数

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

这里给出了一个与Jackson一起使用以生成JsonNode类型的示例:

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>

#1