Available in versions: Dev (3.20) | Latest (3.19) | 3.18 | 3.17

Client side computed columns

Applies to ❌ Open Source Edition   ✅ Express Edition   ✅ Professional Edition   ✅ Enterprise Edition

Computed columns are a powerful feature on the server side, where a column can be guaranteed to always contain a value according to an expression based on the other columns of a table. Many dialects support them in at least one of two flavours:

  • STORED: The computed value is calculated on write, and stored in the table
  • VIRTUAL: The computed value is calculated on read (although it may still be stored in indexes)

There are various reasons why you may wish to compute columns, but not on the server side, i.e. not in the database directly, but let jOOQ do that for you on the client side. The forced type configuration can be used again to match columns, and apply an org.jooq.Generator:

XML (standalone and maven)
Programmatic
Gradle (Kotlin)
Gradle (Groovy)
Gradle (third party)
<configuration>
  <generator>
    <database>
      <forcedTypes>
        <forcedType>

          <!-- Provide an expression of type org.jooq.Generator, or a class reference
               com.example.X for a class of type org.jooq.Generator, which can be
               instantiated using new com.example.X() -->
          <generator>ctx -&gt; org.jooq.impl.DSL.val(1)</generator>
          <includeExpression>(?i:ALWAYS_ONE)</includeExpression>
        </forcedType>
      </forcedTypes>
    </database>
  </generator>
</configuration>

See the configuration XSD, standalone code generation, and maven code generation for more details.

new org.jooq.meta.jaxb.Configuration()
  .withGenerator(new Generator()
    .withDatabase(new Database()
      .withForcedTypes(
        new ForcedType()

          // Provide an expression of type org.jooq.Generator, or a class reference
          // com.example.X for a class of type org.jooq.Generator, which can be
          // instantiated using new com.example.X()
          .withGenerator("ctx -> org.jooq.impl.DSL.val(1)")
          .withIncludeExpression("(?i:ALWAYS_ONE)")
      )
    )
  )

See the configuration XSD and programmatic code generation for more details.

import org.jooq.meta.jaxb.*


configuration {
  generator {
    database {
      forcedTypes {
        forcedType {

          // Provide an expression of type org.jooq.Generator, or a class reference
          // com.example.X for a class of type org.jooq.Generator, which can be
          // instantiated using new com.example.X()
          generator = "ctx -> org.jooq.impl.DSL.val(1)"
          includeExpression = "(?i:ALWAYS_ONE)"
        }
      }
    }
  }
}

See the configuration XSD and gradle code generation for more details.

configuration {
  generator {
    database {
      forcedTypes {
        forcedType {

          // Provide an expression of type org.jooq.Generator, or a class reference
          // com.example.X for a class of type org.jooq.Generator, which can be
          // instantiated using new com.example.X()
          generator = "ctx -> org.jooq.impl.DSL.val(1)"
          includeExpression = "(?i:ALWAYS_ONE)"
        }
      }
    }
  }
}

See the configuration XSD and gradle code generation for more details.

generationTool {
  generator {
    database {
      forcedTypes {
        forcedType {

          // Provide an expression of type org.jooq.Generator, or a class reference
          // com.example.X for a class of type org.jooq.Generator, which can be
          // instantiated using new com.example.X()
          generator = "ctx -> org.jooq.impl.DSL.val(1)"
          includeExpression = "(?i:ALWAYS_ONE)"
        }
      }
    }
  }
}

See the configuration XSD and gradle code generation for more details.

The behaviour is as follows:

  • If the forced type matches an actual column from your database, then the semantics is that of STORED computed columns, i.e. all of your DML statements will be transformed by jOOQ to ensure the correct computation of the value, which will be written to your schema. Other database clients, including non-jOOQ based ones, can then see those computed values.
  • If the forced type matches a synthetic column, then the semantics is that of VIRTUAL computed columns, i.e. the column does not exist in your schema, but the computation will be added to all of your SELECT statements, if you include the column in your projections and other clauses.

Unlike server side computed columns, these computational expressions can include any type of column expression, including scalar subqueries (even correlated ones), or implicit joins, making client side computed columns an extremely powerful jOOQ feature!

The exact time a computation takes place is not specified for both VIRTUAL and STORED client side computed columns. While it may seem reasonable to expect the computation to take place when the column is rendered to SQL, future implementations may defer or even cache the computation. As such, a computation is recommended to be pure and side effect free. While side effects, such as producing a "current timestamp" are not forbidden, it is important to take into account the non-determinism of when the computation takes place. For example, caching a pre-computed transaction timestamp may be more reliable than computing the timestamp from within the Generator.

Limitations

The computed column expression is a property of the generated org.jooq.DataType. As such, the property can only be enforced on expressions which make this flag available to jOOQ. For example, if you're using plain SQL templates without passing along a DataType with the computed expression enabled, then the feature cannot be enforced. See also features requiring code generation for more details.

Feedback

Do you have any feedback about this page? We'd love to hear it!

The jOOQ Logo