Using Jooq, I would like to bind a custom type for Interval, the dbms is PostgreSQL. In order to do so, I was following this thread, which is trying to achieve the same thing. Unfortunately, my rendered SQL is not valid.

I have a forced binding:

new ForcedType()
    .withUserType(Interval.class.getName())
    .withIncludeTypes("interval")
    .withBinding(IntervalBinding.class.getName())

A custom Interval:

@Data
@Builder
public final class MyInterval implements .jooq.types.Interval {
  private final Period period;
  private final Duration duration;

I then need an IntervalConverter to map String <> PGInterval:

public class IntervalConverter implements Converter<String, Interval> {

  @Override
  public Interval from(String object) {
    if (object == null) {
      return null;
    }

    PGInterval pgInterval = new PGInterval(object);
    return new MyInterval(
        Period.of(pgInterval.getYears(), pgInterval.getMonths(), pgInterval.getDays()),
        Duration.ofHours(pgInterval.getHours())
            .plusMinutes(pgInterval.getMinutes())
            .plusSeconds(pgInterval.getWholeSeconds()));
  }

  @Override
  public String to(Interval userInterval) {
    if (userInterval == null) {
      return null;
    }

    final Period period = Period.ofDays((int)userInterval.toDuration().toDaysPart());
    final Duration duration = userInterval.toDuration();

    return new PGInterval(
        period.getYears(),
        period.getMonths(),
        period.getDays(),
        (int) duration.toHours(),
        duration.toMinutesPart(),
        duration.toSecondsPart()
    ).toString();
  }

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

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

and more interestingly, the IntervalBinding:

public class IntervalBinding implements Binding<YearToSecond, Interval> {

  private final Converter<String, Interval> converter = new IntervalConverter();

  @Override
  public Converter<YearToSecond, Interval> converter() {
    return new Converter<>() {
      @Override
      public Interval from(final YearToSecond databaseObject) {
        return null;
      }

      @Override
      public YearToSecond to(final Interval userObject) {
        return null;
      }

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

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

  @Override
  public void sql(final BindingSQLContext<Interval> ctx) {
    ctx.render().sql("interval ").visit(DSL.val(ctx.convert(converter).value()));
  }

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

  // Converting the NullableBoundInterval to a String value and setting that on a JDBC PreparedStatement
  @Override
  public void set(final BindingSetStatementContext<Interval> 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 NullableBoundInterval
  @Override
  public void get(final BindingGetResultSetContext<Interval> 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 NullableBoundInterval
  @Override
  public void get(final BindingGetStatementContext<Interval> ctx) throws SQLException {
    ctx.convert(converter).value(ctx.statement().getString(ctx.index()));
  }

  // Setting a value on a JDBC SQLOutput
  @Override
  public void set(final BindingSetSQLOutputContext<Interval> ctx) throws SQLException {
    throw new SQLFeatureNotSupportedException();
  }

  // Getting a value from a JDBC SQLInput
  @Override
  public void get(final BindingGetSQLInputContext<Interval> ctx) throws SQLException {
    throw new SQLFeatureNotSupportedException();
  }
}

It is also basically the same as the one in the mentioned thread, but I think the IntervalBinding is causing my problem. So I thought I'd include it here.

If I now try to save an entity owning an Interval, say, with the value P1DT1H, then the rendered SQL, right before calling ctx.statement().executeQuery() in AbstractDMLQuery, looks like this:

context.statement():
insert into "XX"."YY" ([...], "my_interval") 
values ([...], interval ('0 years 0 mons 1 days 1 hours 0 mins 0.0 secs'))

The rendered brackets around the interval value are not valid. If I omit them and insert the value via a query console, the statement works.

Interestingly, according to my debug session, the value in ctx.query() does not have the extra backets, so that statement works "out of the box" for me:

interval '0 years 0 mons 1 days 1 hours 0 mins 0.0 secs'

So, how can I fix the rendering of my query? It must be related to the way the IntervalBinding#sql is implemented, but I can not figure it out.

Using Jooq, I would like to bind a custom type for Interval, the dbms is PostgreSQL. In order to do so, I was following this thread, which is trying to achieve the same thing. Unfortunately, my rendered SQL is not valid.

I have a forced binding:

new ForcedType()
    .withUserType(Interval.class.getName())
    .withIncludeTypes("interval")
    .withBinding(IntervalBinding.class.getName())

A custom Interval:

@Data
@Builder
public final class MyInterval implements .jooq.types.Interval {
  private final Period period;
  private final Duration duration;

I then need an IntervalConverter to map String <> PGInterval:

public class IntervalConverter implements Converter<String, Interval> {

  @Override
  public Interval from(String object) {
    if (object == null) {
      return null;
    }

    PGInterval pgInterval = new PGInterval(object);
    return new MyInterval(
        Period.of(pgInterval.getYears(), pgInterval.getMonths(), pgInterval.getDays()),
        Duration.ofHours(pgInterval.getHours())
            .plusMinutes(pgInterval.getMinutes())
            .plusSeconds(pgInterval.getWholeSeconds()));
  }

  @Override
  public String to(Interval userInterval) {
    if (userInterval == null) {
      return null;
    }

    final Period period = Period.ofDays((int)userInterval.toDuration().toDaysPart());
    final Duration duration = userInterval.toDuration();

    return new PGInterval(
        period.getYears(),
        period.getMonths(),
        period.getDays(),
        (int) duration.toHours(),
        duration.toMinutesPart(),
        duration.toSecondsPart()
    ).toString();
  }

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

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

and more interestingly, the IntervalBinding:

public class IntervalBinding implements Binding<YearToSecond, Interval> {

  private final Converter<String, Interval> converter = new IntervalConverter();

  @Override
  public Converter<YearToSecond, Interval> converter() {
    return new Converter<>() {
      @Override
      public Interval from(final YearToSecond databaseObject) {
        return null;
      }

      @Override
      public YearToSecond to(final Interval userObject) {
        return null;
      }

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

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

  @Override
  public void sql(final BindingSQLContext<Interval> ctx) {
    ctx.render().sql("interval ").visit(DSL.val(ctx.convert(converter).value()));
  }

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

  // Converting the NullableBoundInterval to a String value and setting that on a JDBC PreparedStatement
  @Override
  public void set(final BindingSetStatementContext<Interval> 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 NullableBoundInterval
  @Override
  public void get(final BindingGetResultSetContext<Interval> 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 NullableBoundInterval
  @Override
  public void get(final BindingGetStatementContext<Interval> ctx) throws SQLException {
    ctx.convert(converter).value(ctx.statement().getString(ctx.index()));
  }

  // Setting a value on a JDBC SQLOutput
  @Override
  public void set(final BindingSetSQLOutputContext<Interval> ctx) throws SQLException {
    throw new SQLFeatureNotSupportedException();
  }

  // Getting a value from a JDBC SQLInput
  @Override
  public void get(final BindingGetSQLInputContext<Interval> ctx) throws SQLException {
    throw new SQLFeatureNotSupportedException();
  }
}

It is also basically the same as the one in the mentioned thread, but I think the IntervalBinding is causing my problem. So I thought I'd include it here.

If I now try to save an entity owning an Interval, say, with the value P1DT1H, then the rendered SQL, right before calling ctx.statement().executeQuery() in AbstractDMLQuery, looks like this:

context.statement():
insert into "XX"."YY" ([...], "my_interval") 
values ([...], interval ('0 years 0 mons 1 days 1 hours 0 mins 0.0 secs'))

The rendered brackets around the interval value are not valid. If I omit them and insert the value via a query console, the statement works.

Interestingly, according to my debug session, the value in ctx.query() does not have the extra backets, so that statement works "out of the box" for me:

interval '0 years 0 mons 1 days 1 hours 0 mins 0.0 secs'

So, how can I fix the rendering of my query? It must be related to the way the IntervalBinding#sql is implemented, but I can not figure it out.

• java
• jooq

• I cannot tell from your description alone where the parentheses are coming from. You may have omitted a detail. If you could provide a complete reproducer (e.g. following this template: github/jOOQ/jOOQ-mcve), I might be able to help – Lukas Eder Commented Mar 6 at 13:09
• I guess if you could just share the IntervalConverter implementation, that might help, too. – Lukas Eder Commented Mar 6 at 13:31
• Thank you @LukasEder I edited the IntervalConverter to my question. – Nikolas Commented Mar 6 at 14:32
• @LukasEder I managed to reproduce the issue in an mcve, github/jOOQ/jOOQ-mcve/issues/53 – Nikolas Commented Mar 6 at 18:58

1 Answer 1

I don't know what exactly you're doing to get those excess parentheses. In the reproducer that you've provided here, there aren't any excess parentheses generated. Instead, you're generating this invalid SQL:

insert into "mcve"."test" ("cd", "my_interval") values (?, interval ?)

There is no INTERVAL literal in PostgreSQL that accepts a bind variable placeholder marker as you did above. You could generate this, instead: ?::interval, or something similar:

public void sql(BindingSQLContext<Interval> ctx) {
    if (ctx.render().paramType() == ParamType.INLINED)
        ctx.render().sql("interval ").visit(DSL.val(ctx.convert(converter).value()));
    else
        ctx.render().visit(DSL.val(ctx.convert(converter).value())).sql("::interval");
}