This is experimental functionality, and as such subject to change. Use at your own risk!
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
:
<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)</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.
// The jOOQ-codegen-gradle plugin has been introduced in version 3.19 only.
// The jOOQ-codegen-gradle plugin has been introduced in version 3.19 only.
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 generatedorg.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 aDataType
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!