java – 在JOOQ自定义绑定中生成SQL以访问MySQL JSON函数时访问字段名称或别名-Java开发

使用JOOQ 3.5.2和MySQL 5.7,我试图完成以下…MySQL有一组JSON函数,允许在较大文档中对属性进行路径目标操作.我正在尝试使用JOOQ进行利用这一点的抽象.我首先创建了JSON可序列化文档模型,它跟踪变化,然后为它实现了...

使用JOOQ 3.5.2和MySQL 5.7,我试图完成以下…

MySQL有一组JSON函数,允许在较大文档中对属性进行路径目标操作.

我正在尝试使用JOOQ进行利用这一点的抽象.我首先创建了JSON可序列化文档模型,它跟踪变化,然后为它实现了JOOQ自定义绑定.

在此绑定中,我拥有生成对这些MySQL JSON函数的调用所需的所有状态信息,但正在更新的列的限定名称或别名除外.对于此名称的引用是就地更新现有JSON文档所必需的.

我一直无法找到从Binding界面中可用的* Context类型访问此名称的方法.

我一直在考虑实现一个VisitListener来捕获这些字段名称并将它们传递给Scope自定义数据映射,但该选项似乎非常脆弱.

访问我的Binding实现中的字段名称或别名的最佳方法是什么？

– 编辑 –

好的,为了帮助澄清我的目标,请采取以下DDL：

create table widget (
  widget_id bigint(20) NOT NULL,
  jm_data json DEFAULT NULL,
  primary key (widget_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

现在让我们假设jm_data将保存java.util.Map< String,String>的JSON表示.为此,JOOQ通过实现和注册自定义数据类型绑定(在本例中使用Jackson)提供了一个非常好的扩展API：

public class MySQLJSONJacksonMapBinding implements Binding<Object, Map<String, String>> {
    private static final ObjectMapper mapper = new ObjectMapper();

    // The converter does all the work
    @Override
    public Converter<Object, Map<String, String>> converter() {
        return new Converter<Object, Map<String, String>>() {
            @Override
            public Map<String, String> from(final Object t) {
                try {
                    return t == null ? null
                            : mapper.readValue(t.toString(),
                                    new TypeReference<Map<String, String>>() {
                                    });
                } catch (final IOException e) {
                    throw new RuntimeException(e);
                }
            }

            @Override
            public Object to(final Map<String, String> u) {
                try {
                    return u == null ? null
                            : mapper.writer().writeValueAsString(u);
                } catch (final JsonProcessingException e) {
                    throw new RuntimeException(e);
                }
            }

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

            @Override
            public Class toType() {
                return Map.class;
            }
        };
    }

    // Rending a bind variable for the binding context's value and casting it to the json type
    @Override
    public void sql(final BindingSQLContext<Map<String, String>> ctx) throws SQLException {
        // Depending on how you generate your SQL, you may need to explicitly distinguish
        // between jOOQ generating bind variables or inlined literals. If so, use this check:
        // ctx.render().paramType() == INLINED
        ctx.render().visit(DSL.val(ctx.convert(converter()).value()));
    }

    // Registering VARCHAR types for JDBC CallableStatement OUT parameters
    @Override
    public void register(final BindingRegisterContext<Map<String, String>> ctx)
        throws SQLException {
        ctx.statement().registerOutParameter(ctx.index(), Types.VARCHAR);
    }

    // Converting the JsonElement to a String value and setting that on a JDBC PreparedStatement
    @Override
    public void set(final BindingSetStatementContext<Map<String, String>> ctx) throws SQLException {
        ctx.statement().setString(ctx.index(),
                Objects.toString(ctx.convert(converter()).value(), null));
    }

    // Getting a String value from a JDBC ResultSet and converting that to a Map
    @Override
    public void get(final BindingGetResultSetContext<Map<String, String>> ctx) throws SQLException {
        ctx.convert(converter()).value(ctx.resultSet().getString(ctx.index()));
    }

    // Getting a String value from a JDBC CallableStatement and converting that to a Map
    @Override
    public void get(final BindingGetStatementContext<Map<String, String>> ctx) throws SQLException {
        ctx.convert(converter()).value(ctx.statement().getString(ctx.index()));
    }

    // Setting a value on a JDBC SQLOutput (useful for Oracle OBJECT types)
    @Override
    public void set(final BindingSetSQLOutputContext<Map<String, String>> ctx) throws SQLException {
        throw new SQLFeatureNotSupportedException();
    }

    // Getting a value from a JDBC SQLInput (useful for Oracle OBJECT types)
    @Override
    public void get(final BindingGetSQLInputContext<Map<String, String>> ctx) throws SQLException {
        throw new SQLFeatureNotSupportedException();
    }
}

…这个实现在构建时由代码生成器附加,如下所示：

<customTypes>
  <customType>
    <name>JsonMap</name>
    <type>java.util.Map&lt;String,String&gt;</type>
    <binding>com.orbiz.jooq.bindings.MySQLJSONJacksonMapBinding</binding>
  </customType>
</customTypes>
<forcedTypes>
  <forcedType>
    <name>JsonMap</name>
    <expression>jm_.*</expression>
    <types>json</types>
  </forcedType>
</forcedTypes>

…所以有了这个,我们有一个很好的,强类型的Java Map,我们可以在我们的应用程序代码中操作.但是绑定实现,即使只插入,更新或删除了一个映射条目,它总是将整个映射内容写入JSON列.此实现将MySQL JSON列视为普通的VARCHAR列.

该方法根据用途提出了两个具有不同重要性的问题.

>仅更新具有数千个条目的大型地图的一部分会产生不必要的SQL线路流量作为副作用.
>如果地图的内容是用户可编辑的,并且有多个用户同时编辑内容,则一个人的更改可能会被另一个人覆盖,即使他们没有冲突.

MySQL 5.7引入了JSON数据类型,以及一些用于在SQL中操作文档的函数.这些函数可以解决JSON文档的内容,允许单个属性的目标更新.继续我们的例子……：

insert into DEV.widget (widget_id, jm_data) 
values (1, '{"key0":"val0","key1":"val1","key2":"val2"}');

…如果我要将java Map“key1”值更改为等于“updated_value1”并在记录上调用更新,则上面的Binding实现会生成这样的SQL：

update DEV.widget 
set DEV.widget.jm_data = '{"key0":"val0","key1":"updated_value1","key2":"val2"}' 
where DEV.widget.widget_id = 1;

…注意正在更新整个JSON字符串. MySQL可以使用json_set函数更有效地处理这个问题：

update DEV.widget 
set DEV.widget.jm_data = json_set( DEV.widget.jm_data, '$."key1"', 'updated_value1' ) 
where DEV.widget.widget_id = 1;

因此,如果我想生成这样的SQL,我需要首先跟踪从最初从数据库读取到更新时对我的Map所做的更改.然后,使用此更改信息,我可以生成对json_set函数的调用,这将允许我仅更新已修改的属性.

最后得到我的实际问题.你会注意到在我希望生成的SQL中,正在更新的列的值包含对列本身的引用json_set(DEV.widget.jm_data,….这个列(或别名)名称似乎不是Binding API可用.我有办法在Binding实现中识别要更新的别名列的名称吗？

解决方法:

您的Binding实现是寻找此问题的解决方案的错误位置.你真的不想改变你的列的绑定,以某种方式神奇地知道这个json_set函数,它执行json数据的增量更新而不是完全替换.当您使用UpdatableRecord.store()(您似乎正在使用它)时,期望任何Record.field(x)完全反映数据库行的内容,而不是delta.当然,您可以在绑定的sql()方法中实现类似的东西,但是要做到正确并且绑定不适用于所有用例都很困难.

因此,为了实现您想要实现的目标,只需使用jOOQ编写显式UPDATE语句,使用plain SQL templating增强jOOQ API.

// Assuming this static import
import static org.jooq.impl.DSL.*;

public static Field<Map<String, String>> jsonSet(
    Field<Map<String, String>> field,
    String key,
    String value
) {
    return field("json_set({0}, {1}, {2})", field.getDataType(), field, inline(key), val(value));
}

然后,使用您的库方法：

using(configuration)
    .update(WIDGET)
    .set(WIDGET.JM_DATA, jsonSet(WIDGET.JM_DATA, "$.\"key1\"", "updated_value1"))
    .where(WIDGET.WIDGET_ID.eq(1))
    .execute();

如果这太重复了,我相信你可以在你的某些API中分解出公共部分.

基础教程推荐