Jooq- Updating a field to NULL makes it "null" and not NULL

Question

So this was my query that previously worked in jooq 3.11.

I am updating a JSON field, however it is mapped to a String in my model using a JsonBinding which I will post down below

        dsl.update(TASK)
            .set(TASK.JSON_SOLUTION, (String) null).            
        .where(TASK.TENANT.eq(getCurrentTenant()))
        .and(TASK.TASK_TEMPLATE_ID.in(taskTemplateIds));execute()

This now no longer works after upgrading to jooq 3.13.2. I also had to change my sql dialect to be mysql even though I am working with a mysql 5_7 database, this may be the issue?

I have also tried this and it is still the same

        dsl.update(TASK)
            .setNull(TASK.JSON_SOLUTION).            
         .where(TASK.TENANT.eq(getCurrentTenant()))
        .and(TASK.TASK_TEMPLATE_ID.in(taskTemplateIds));execute()

JsonBinding.class

public class JsonBinding implements Binding<JSON, String> {

  @Override
  public Converter<JSON, String> converter() {
    return new JsonConverter();
  }

  @Override
  public void sql(BindingSQLContext<String> bindingSQLContext) {
    if (bindingSQLContext.render().paramType() == ParamType.INLINED) {
      bindingSQLContext
          .render()
          .visit(DSL.inline(bindingSQLContext.convert(converter()).value()))
          .sql("::json");
    } else {
      bindingSQLContext.render().sql("?");
    }
  }

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

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

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

  @Override
  public void get(BindingGetResultSetContext<String> bindingGetResultSetContext)
      throws SQLException {
    bindingGetResultSetContext
        .convert(converter())
        .value(
            JSON.valueOf(
                bindingGetResultSetContext
                    .resultSet()
                    .getString(bindingGetResultSetContext.index())));
  }

  @Override
  public void get(BindingGetStatementContext<String> bindingGetStatementContext)
      throws SQLException {
    bindingGetStatementContext
        .convert(converter())
        .value(
            JSON.valueOf(
                bindingGetStatementContext
                    .statement()
                    .getString(bindingGetStatementContext.index())));
  }

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

JsonConverter.class

public class JsonConverter implements Converter<JSON, String> {

  @Override
  public String from(JSON object) {
    return object != null ? object.toString() : null;
  }

  @Override
  public JSON to(String string) {
    return JSON.valueOf(string);
  }

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

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

Here is the query jooq runs with .setNull()

update `tasks_service`.`task` set `tasks_service`.`task`.`json_solution` = 'null'::json where (`tasks_service`.`task`.`tenant` = 'skynet' and `tasks_service`.`task`.`task_template_id` in ('55', '33'))

Before the upgrade on jooq 3.11 the query comes out as this

update `tasks_service`.`task` set `tasks_service`.`task`.`json_solution` = null::json where (`tasks_service`.`task`.`tenant` = 'skynet' and `tasks_service`.`task`.`task_template_id` in ('55', '33'))

So before its set 'json_solution' = null and after the upgrade it seems to be set 'json_solution' = 'null'

Not quite sure why this is occurring?

Edit: So from what I can tell this solely seems to be from the upgrade in JOOQ and not the sql-dialect. Using Jooq 3.11.5 with both mysql and mysql_5_7 as the dialects, the query is built as set 'json_solution' = null, if I upgrade JOOQ to 3.13.2 its set 'json_solution' = 'null'

This quirk also seems to only happen on the JSON field, I tried setting another varchar String field to be null on the same table, and I get the correct set "field_name' = null

The problem may be with my JsonBinding/JsonConverter ? I had to modify it slightly to work with the new JSON object in JOOQ, as previously JOOQ mapped JSON as Object

Answer 1

JSON.valueOf(null) vs (JSON) null

The answer is in the Javadoc of org.jooq.JSON:

A CAST(NULL AS JSON) value is represented by a null reference of type JSON, not as data() == null. This is consistent with jOOQ's general way of returning NULL from Result and Record methods.

So, the mistake is in JsonConverter's usage of JSON.valueOf(). Write this instead:

  public JSON to(String string) {
    return string == null ? null : JSON.valueOf(string);
  }

Or, just use Converter.ofNullable(), which handles the null-to-null mapping for you:

Converter<JSON, String> converter = Converter.ofNullable(
  JSON.class,
  String.class,
  JSON::data,
  JSON::json
);

Side note on using a Binding

You don't really need the binding anymore, now that the JSON type is suppported natively by jOOQ. If you want to convert JSON to String, your Converter will be sufficient.