3.17.5

Version 3.17.5 - November 4, 2022

This is a 3.17 patch release with minor improvements and bug fixes

Features and Improvements

#13997 - Improve documentation on Fields lookup methods
#14072 - Add parser support for undocumented AS keyword in SQL Server procedure parameter list

Bug Fixes

#13966 - HSQLDB arrays can't be projected from nested rows
#13983 - ParsingConnection doesn't correctly relay Statement::getResultSet, Statement::getUpdateCount, or Statement::getMoreResults on static Statement
#13994 - Wrong value returned from NullCondition::isNullable
#14015 - Slow query against SYS.ALL_INDEXES in OracleDatabase
#14018 - Moderately slow query on ALL_TAB_COLS due to cartesian product in OracleTableDefinition
#14022 - Moderately slow query on ALL_ARGUMENTS due to EXTENDED DATA LINK FULL in OracleTableDefinition
#14024 - Postgres array of single-field UDT reads UDT's field as null instead of actual value
#14028 - ClobBinding and BlobBinding shouldn't delegate sql() generation to internal DefaultStringBinding or DefaultBytesBinding
#14031 - ORA-01704 when generating large CLOB inline values in Oracle
#14035 - Upgrade to Scala 2.13.9 to mitigate CVE-2022-36944
#14037 - KotlinGenerator produces directory names with backticks when catalog names contain special characters
#14054 - Upgrade jackson-databind dependency to 2.13.4 to mitigate CVE-2022-42004
#14056 - Upgrade protobuf-java to 3.16.3 to mitigate CVE-2021-22569
#14061 - Work around Derby's Error [30000] [54002]: A string constant starting with '...' is too long
#14068 - ClobBinding and BlobBinding shouldn't bind a null value on PreparedStatement::setClob and ::setBlob in Firebird, HSQLDB
#14087 - Support parsing SELECT .. INTO FROM ..
#14101 - Table as SelectField native support relies on actual column order, not generated order
#14109 - Upgrade jackson-databind dependency to 2.13.4.2 to mitigate CVE-2022-42003
#14117 - ClassCastException when nesting array(select row) projections in PostgreSQL
#14134 - Code generator shouldn't generate JPA Column precision on non-decimal types
#14142 - Generated text blocks for view sources should escape
#14164 - Wrong transformation for transformPatternsTrivialPredicates when DISTINCT predicate operand is NULL

3.17.4

Version 3.17.4 - September 6, 2022

This is a 3.17 patch release with minor improvements and bug fixes

Features and Improvements

#13886 - Parse length on unknown data types
#13888 - Parse and ignore SQLite DDL conflict-clause
#13931 - Show deprecation warning for javax.persistence annotations also for method and field annotations

Bug Fixes

#13874 - PostgreSQL arrays don't deserialise correctly when using XML MULTISET emulation
#13877 - ArrayGet should not produce parentheses when used as a store assignment target, e.g. in UPDATE
#13883 - Parser should parse and ignore PRIMARY KEY UNIQUE
#13899 - The maven codegen plugin does not load syntheticObjects from external configurationFile
#13908 - Logger name repeated twice
#13909 - Code generation fails with H2 2.1.214 when using CASE_INSENSITIVE_IDENTIFIERS
#13921 - PostgreSQL generated code doesn't maintain precision / scale on NUMERIC or TIME, TIMETZ, TIMESTAMP, TIMESTAMPTZ types
#13923 - Convert truncates microseconds from OffsetDateTime when converting to Instant
#13929 - NullPointerException thrown instead of DetachedException, when executing detached query
#13943 - Cannot use Table as SelectField from derived table where nesting records is supported natively
#13953 - IsNotDistinctFrom predicate doesn't declare non-nullability

3.17.3

Version 3.17.3 - August 16, 2022

This is a 3.17 patch release with minor improvements and bug fixes

Bug Fixes

#13792 - Data type rewrites to BOOLEAN don't work in Oracle JSON_OBJECT
#13797 - Virtual client side computed columns must be excluded from generated DDL statements
#13799 - GenerationOption.DEFAULT should act as STORED in client side computed columns
#13819 - MySQL DEFAULT CURRENT_TIMESTAMP column is generated as computed column
#13831 - NullPointerException when calling DBMS_AQ.dequeue
#13852 - Wrong DDL generated for computed columns in MEMSQL
#13855 - Syntax error in MemSQL code generation
#13858 - Upgrade pgjdbc to mitigate CVE-2022-31197
#13861 - Fix typo in code generation log message
#13863 - AbstractSpringDAOImpl imported from wrong package when generating multiple schemas
#13864 - Wrong kotlin code generated for SQL Server stored procedures
#13868 - MemSQL doesn't support the SET @@var = @other_var syntax

3.17.2

Version 3.17.2 - July 7, 2022

This is a 3.17 patch release with minor improvements and bug fixes

Bug Fixes

#13731 - Generated sources emits "reference not accessible" warning for org.jooq.impl.AbstractTable#convertFrom
#13733 - Wrong Javadoc in generated AbstractSpringDAOImpl
#13735 - Work around JDK-8289221, where javadoc aren't allowed to contain ellipses in a code tag in a method header
#13739 - Reactive transactions don't call Connection::close
#13740 - Connection::close call is skipped when R2DBC query fails
#13743 - R2DBC implementation may hang when there's an exception in the rendering logic
#13754 - GROUP BY

does not use table alias
#13757 - Returning null from transactionCoroutine throws NoSuchElementException
#13765 - DefaultRecordBinding.pgRenderRecordCast and others don't apply schema mapping
#13767 - Upgrade log4j to 2.18.0 to mitigate CVE-2022-33915
#13772 - Parser doesn't support parsing parenthesised GROUP BY expression
#13777 - maven-deploy and maven-install bash and batch scripts should terminate on mvn command error
#13782 - ERROR: unknown function: nameconcatoid() in CockroachDBDatabase on CockroachDB 22

3.17.1

Version 3.17.1 - June 27, 2022

This is a 3.17 patch release with minor improvements and bug fixes

Features and Improvements

#13707 - OnError.FAIL should log a message about the user's option how to handle the problem
#13719 - Log warning should show jOOQ-meta type to help disambiguate objects that share the same name

Bug Fixes

#13710 - Ill formatted Javadoc in DSLContext
#13714 - Cannot parse computed columns in the presence of identity column
#13724 - Regression: Zero-length delimited identifier is generated for enum columns when using
#13726 - Regression: Client code no longer compiles when calling DSLContext.fetchSingle(Table, Condition...)

3.17.0

Version 3.17.0 - June 22, 2022

Client side computed columns

A ground breaking new core feature available in all commercial distributions is
the new client side computed columns feature, building on top of jOOQ 3.16's
commercial support for readonly columns and server side computed columns.

Not all RDBMS support computed columns (e.g. using the standard SQL syntax
GENERATED ALWAYS AS), and if they do, they might not support them
in both STORED (computed on write) and VIRTUAL
(computed on read) variants. jOOQ can now emulate both features at the client
side, by transforming your SQL queries:

• STORED affects INSERT, UPDATE,
  DELETE, and MERGE
• VIRTUAL affects SELECT and the
  RETURNING clause of DML statements. To make use of these,
  combine them with the new synthetic column generation feature.

Unlike their server side counterparts, these client side features can produce
arbitrary expressions, including:

• Implicit joins
• Scalar subqueries
• MULTISET subqueries
• Much more

Think of this as "views" written in jOOQ, on a per-column basis. An expecially
useful feature combination is to combine these computed columns with the new
visibility modifier that allows for keeping computed columns (or the underlying
base columns) private and thus invisible to user code.

More about this feature here:

• https://www.jooq.org/doc/3.17/manual/code-generation/codegen-advanced/codegen-config-database/codegen-database-forced-types/codegen-database-forced-types-computed/

Audit columns

A special case of STORED client side computed columns are audit
columns, whose most basic implementation comes in the form of:

• CREATED_AT
• CREATED_BY
• MODIFIED_AT
• MODIFIED_BY

Other approaches to auditing exist, including soft deletion, additional meta
data, (bi)temporal versioning, but these columns are among the most popular
approaches, making this commercial only convenience feature very useful to a lot
of customers.

More about this feature here:

• https://www.jooq.org/doc/3.17/manual/code-generation/codegen-advanced/codegen-config-database/codegen-database-forced-types/codegen-database-forced-types-audit/

Java 17 baseline for the jOOQ Open Source Edition

Java 17 has been the latest LTS, and it includes a lot of really cool features,
including:

• sealed types (essential for pattern matching)
• records
• instanceof pattern matching
• text blocks
• switch expressions

jOOQ 3.16's experimental new Query Object Model (QOM) API experiments with
sealed types, which will be adopted more generally once the QOM API is
finalized.

To get broader user feedback on these improvements, as well as to embrace Java's
new LTS update cadence, we've decided to make Java 17 the baseline for the jOOQ
3.17 Open Source Edition, continuing our Java 8 and 11 support in the commercial
jOOQ distributions.

The following older jOOQ releases will continue to receive upgrades for a while:

• jOOQ 3.14: The last release with Java 8 support in the jOOQ Open Source
  Edition and Java 6 support in the jOOQ Enterprise Edition
• jOOQ 3.15 and 3.16: The last releases with Java 11 support in the jOOQ Open
  Source Edition.

PostgreSQL data type support

The jooq-postgres-extensions module, which contained support for the
HSTORE type, now has a lot more support for PostgreSQL specific
data types, including array types of each of:

• CIDR
• CITEXT
• LTREE
• HSTORE
• INET
• RANGE (including all the specialisations for INT4,
  INT8, etc.)

In order to profit from these data types, just add the
org.jooq:jooq-postgres-extensions module to your code generation
and runtime dependencies, and the types are generated automatically.

Implicit JOIN improvements

In this release, we experimented with a few new implicit JOIN features,
including support for implicit JOIN in DML statements. The current
implementation produces correlated subqueries where JOIN isn't supported in DML
statements.

We've also experimented with creating a "convenience syntax" for other commonly
used correlated subqueries, such as EXISTS(...) subqueries or
MULTISET(...) subqueries. The experiment has been very interesting.
The prototype, however, was rejected. See the discussions here:

• #13063
• #13069

Future jOOQ versions will implement the desired convenience in the form of more
implicit JOIN functionality, offering the feature also as an implicit to-many
JOIN.

A leftover from the prototype is the fact that you can now more easily project
expressions other than classic Field in your SELECT
clause, namely:

• Table now extends SelectField
• Condition now extends Field

This means you can write a query like this:

Result> result =
ctx.select(
        // Project a CustomerRecord directly
        CUSTOMER,
        // Project an AddressRecord from an implicit JOIN
        CUSTOMER.address(),
        // Project a boolean expression, instead of wrapping it with DSL.field()
        exists(
            selectOne()
            .from(PAYMENT)
            .where(PAYMENT.CUSTOMER_ID.eq(CUSTOMER.CUSTOMER_ID))
        )
   .from(CUSTOMER)
   .fetch();

Pattern matching SQL Transformations

SQL transformations have been a strategic feature set to recent jOOQ releases,
offering additional compatibility between SQL dialects to commercial customers,
such as, for example:

• Transforming Oracle's ROWNUM into equivalent window functions or
  LIMIT clauses.
• Turning table lists including Oracle's (+) operator into ANSI
  JOIN syntax.

This release ships with a new commercial only feature that directly transforms
the new Query Object Model (QOM)'s expression tree prior to rendering. It does
so by applying pattern matching to the expression tree. Some assorted examples
include:

• LTRIM(RTRIM(x)) into TRIM(x)
• x != a AND x != b into x NOT IN (a, b)
• x IN (a, b, c) AND x IN (b, c, d) into x IN (b, c)
• NOT (NOT (x = 1)) into x = 1
• NOT (x = 1) into x != 1

And much more. The primary use-cases for this functionality are:

• SQL linting, e.g. as part of an ExecuteListener
• SQL auto cleanup, including in a ParsingConnection
• Dialect migration, when upgrading database versions, or moving between dialects
• Patching specific SQL features

For more information about the feature, see:

• https://www.jooq.org/doc/3.17/manual/sql-building/queryparts/sql-transformation/transform-patterns/

Note that this feature is also available for free online:

• https://www.jooq.org/translate

Reactive and kotlin coroutine support

A lot of minor improvements have been implemented. A few more significant ones
include:

• R2DBC 0.9.1.RELEASE is now supported
• A new reactive transaction API has been added, which offers the same nested
  transaction semantics as the existing blocking transaction API, see also:
  https://blog.jooq.org/nested-transactions-in-jooq/
• jOOQ's reactive streams bindings via the Publisher SPI are now
  bridged automatically to kotlin coroutines in the new
  org.jooq:jooq-kotlin-coroutines module using the usual utilites
  org.jetbrains.kotlinx:kotlinx-coroutines-core and
  org.jetbrains.kotlinx:kotlinx-coroutines-reactor
• The org.jooq:jooq-kotlin extensions module now has additional
  extension functions for more MULTISET and other nesting related
  convenience.
• The entire blocking execution API is now annotated with
  org.jetbrains.annotations.Blocking to help reactive jOOQ users
  avoid accidentally blocking on a query, when using IntelliJ. In addition, we
  now annotate experimental and internal API with the ApiStatus
  annotation from the same package.

For a complete list other, minor improvements, see the below change notes.

Features and Improvements

#1592 - Add formal support for "audit" fields, such as CREATED_AT, CREATED_BY, MODIFIED_AT, MODIFIED_BY
#2092 - Add support for the Postgres INET data type (java.net.InetAddress)
#2333 - Add DSL.noField() for "conditional" LIMIT, OFFSET, GROUP BY, ORDER BY support when creating dynamic SQL
#2968 - Add support for PostgreSQL RANGE types
#3497 - Add methods to Context to distinguish between derived tables and other types of subqueries
#4727 - Let Table extend SelectField
#5214 - Add support for PostgreSQL's EXCLUDED table
#5641 - Support overriding code generation configuration via system properties
#5695 - Allow for expressions to be put in LIMIT / OFFSET
#5934 - Add support for the Postgres CITEXT type
#5970 - Add Setting to auto-inline all bind variables that participate in predicates on certain fields
#6454 - Support converting from java.sql.Array to Object[] types
#6489 - Code gen...