Jooq Code Generation for select statement

Is it possible to define a binding/converter/something to automatically convert a column on code generation into a certain format / data type on select?

E.g., Say we have the column valid_date_range with tstzrange postgres data type.

And we want to select it in the following form:

select json_build_array(lower(valid_date_range), upper(valid_date_range)) as date_range from whatever

Is it possible to define a converter/binding that automatically does this on code generation? As far as I can tell, implementing the Binding type will only do this for bind values but not select statement fields?

(I know this can be done manually by creating a field, but i'd like to always convert these tstzrange fields on every select query.)

Solution

jOOQ virtual client side computed columns

You can do this using virtual client side computed columns. You can configure it like this in your code generator config:

<configuration>
  <generator>
    <database>
      <syntheticObjects>
        <columns>
          <column>
            <tables>WHATEVER</tables>
            <name>DATE_RANGE</name>
            <type>JSON</type>
          </column>
        </columns>
      </syntheticObjects>

      <forcedTypes>
        <forcedType>
          <generator>ctx -&gt; org.jooq.impl.DSL.jsonArray(
              DSL.field("lower({0})", VALID_DATE_RANGE),
              DSL.field("upper({0})", VALID_DATE_RANGE)
          )</generator>
          <includeExpression>(?i:WHATEVER.DATE_RANGE)</includeExpression>
        </forcedType>
      </forcedTypes>
    </database>
  </generator>
</configuration>

It declares a synthetic column and the generator logic to use when fetching the synthetic column.

PostgreSQL computed columns

The above client side computed columns work just like "server side" computed columns, i.e. columns computed directly in PostgreSQL, which you can use as well, of course:

ALTER TABLE whatever 
  ADD date_range json
  GENERATED ALWAYS AS (
    json_build_array(lower(valid_date_range), upper(valid_date_range))
  ) STORED;

Though, PostgreSQL does not yet support a VIRTUAL option on these columns, they're always STORED. jOOQ supports both at the client side:

Use a client side function function

You could also extract your logic into a function as follows:

public static Field<JSON> dateRange(Whatever table) {
    return jsonArray(
      field("lower({0})", table.VALID_DATE_RANGE),
      field("upper({0})", table.VALID_DATE_RANGE)
    );
}

And now use it in your query:

ctx.select(WHATEVER.ID, dateRange(WHATEVER))
   .from(WHATEVER)
   .fetch();

You can also store this function in PostgreSQL as a stored function.

Views

Of course, views are always an (overlooked!) option for these things:

CREATE VIEW whatever_plus
AS 
SELECT 
  whatever.*,
  json_build_array(lower(valid_date_range), upper(valid_date_range)) AS date_range
FROM whatever

jOOQ's code generator will pick up this view like any other table and then you can query it. It's probably even updatable if you don't join things to it, so if you declare synthetic constraints on it, you might be able to join the view to the base table, or use UpdatableRecord types on it.