Use the jOOQ-Refaster Module for Automatic Migration off of Deprecated jOOQ API

Starting with jOOQ 3.13, we’re offering a new module called jOOQ Refaster, which provides refaster templates for automatic API migration.

What’s Refaster?

Refaster is a lesser known sub project of the popular Google Error Prone library, a library for static code analysis, implemented as a Java compiler plugin. We already support a few such static code analysis tools through the jOOQ Checker module, which can be used with both the Checker Framework and with Google’s Error Prone.

Refaster allows for finding “bad” API usage patterns in your code, and produce .patch files automatically, which fix such patterns, producing better code.

What are we offering in jOOQ?

Over the years, we have deprecated quite a bit of API, as new API came along. For example, there is the ResultQuery.fetchLazy(int) method, where the int parameter corresponds to the JDBC fetchSize that is being passed along to JDBC.

Long ago, we introduced better API for passing the fetchSize. You can:

  • Specify a Settings value with your configuration for a global fetchSize to apply
  • Specify ResultQuery.fetchSize(int), which can then be combined with fetchLazy(), or with any type of fetch() call, as this isn’t really specific to “lazy” fetching

The Javadoc says:

Deprecated. [#2811] – 3.3.0 – Use fetchSize(int) and fetchLazy() instead.

…and your IDE will probably warn you about deprecated API usage, but who has time to fix all these warnings and read all these Javadoc?

Instead, you can now download the refaster file for your jOOQ version from our website https://www.jooq.org/refaster, configure your Java compiler to include ErrorProne’s refaster library (as documented in the jOOQ manual), and get a patch right away.

For example, if your code reads:

public void runQuery() {
    try (Cursor<?> cursor = ctx.select(T.A)
       .from(T)
       .fetchLazy(42)) {
        // Do something
    }
}

You might be getting a patch file like this one:

--- ..\..\src\main\java\org\jooq\refaster\test\RefasterTests.java
+++ ..\..\src\main\java\org\jooq\refaster\test\RefasterTests.java
@@ -62,6 +62,5 @@
 public void runQuery() {
     try (Cursor<?> cursor = ctx.select(T.A)
-       .from(T)
-       .fetchLazy(42)) {
+       .from(T).fetchSize(42).fetchLazy()) {
         // Do something
     }

And you’ll get the improved version of your code automatically:

public void runQuery() {
    try (Cursor<?> cursor = ctx.select(T.A)
       .from(T).fetchSize(42).fetchLazy()) {
        // Do something
    }
}

In our opinion, all libraries should offer such refaster files (or another technology doing something similar) to help users upgrade more easily between versions.

What’s next?

In addition to upgrading deprecated API usage (which is a very obvious use-case for Refaster), we’re also experimenting with some other static code analysis and improvement approaches that deal with our understanding of the SQL language, and best practices. For example, as we’ve blogged before, it is not recommended to use COUNT(*) queries in SQL (or in any language), when a simple EXISTS() check would suffice.

jOOQ 3.13 already offers an introspection that replaces calls like these:

ctx.fetchCount(table) != 0
ctx.fetchCount(table) >= 1 // And many more

By equivalent, but likely much more performant expressions:

ctx.fetchExists(table)

So, stay tuned for more such goodies in the future!

jOOQ 3.13 Released with More API and Tooling for DDL Management

jOOQ 3.13 has been released with CockroachDB support, much more API and tooling for DDL management, and SQL:2011 temporal table support

Starting with this release, we will further embrace our support for parsing,
translating, executing, and now also interpreting DDL statements. The driving
force is better code generation support, but in the future, also better
integration with database change management systems like Flyway or Liquibase.

We’ve added support for a very promising new dialect: CockroachDB.

We’re starting to support one of SQL:2011’s most interesting features: temporal
tables for system and application time versioning, and we’ve generally improved
the way we build and distribute jOOQ to our paying customers.

SQL Dialects

We’ve worked together with a team of great engineers at CockroachDB to speed up
our newly added, formal CockroachDB support in the jOOQ Professional and
Enterprise Editions. While many users have successfully used the POSTGRES
dialect with CockroachDB for simple integrations, this new dialect is now fully
integration tested and supported for future jOOQ and CockroachDB versions. The
integration fixed a variety of issues and minor differences that we have
encountered in the code generator and the runtime library.

We’ve added support for a variety of features now available in Firebird 3.0+,
MariaDB 10.3+, MySQL 8+, and PostgreSQL 10+.

DDL interpretation

Our biggest investment has been made in the area of DDL interpretation. Since
jOOQ 3.9, we can parse more and more SQL syntax. A logical next step is to allow
for interpreting parsed (or ordinary, DSL constructed) DDL statements in order
to maintain an incremental, in-memory meta model of a database. This has a
variety of benefits, which we will take advantage of in this release and in the
future.

The main out of the box benefits in this version include:

  • Being able to create a diff between two versions of a schema, programmatically through Meta.migrateTo(Meta), on the command line through DiffCLI (https://www.jooq.org/doc/latest/manual/sql-building/schema-diff-cli), or online, through our website: https://www.jooq.org/diff.
  • Such a diff or the meta model itself can be used for ad-hoc introspections, or formally, for automated migrations by tools built on top of jOOQ. Future versions will offer such migrations out of the box.
  • Unfortunately, we have not yet replaced our H2 backed DDLDatabase by our new DDL interpretation capabilities. This is a high priority for jOOQ 3.14, though

In the context of these investments, we have improved a variety of
infrastructure that were needed for these features and that users can benefit as
well. These improvements include:

  • A new LiquibaseDatabase, which uses Liquibase behind the scenes to simulate a schema migration in memory (similar to the DDLDatabase for Flyway), to reverse engineer that for the code generator. If you’re using Liquibase, chances are that you no longer need to connect to an actual database to use jOOQ’s code generator. More info here: https://blog.jooq.org/2019/10/16/how-to-simulate-a-liquibase-migration-using-h2/
  • We now support the inclusion of SQL text in generated code and other parts of the runtime meta model, including for CHECK constraints and views
  • A lot of improvements to the JDBC DatabaseMetaData backed org.jooq.Meta API have been implemented.
  • A lot of additional DDL statement and syntax support has been added throughout the API, parser, and other tooling.
  • The runtime meta model has been enhanced with new information, such as sequence flags, check constraints, table type information, and more.

Temporal table support

Perhaps the SQL:2011 standard’s most exciting “new” feature are temporal tables,
an enhancement on top of Oracle’s historic flashback query feature, and much
more.

In many systems, single, or bi temporal versioning of data is essential. The two
dimensions of versioning that are now supported in jOOQ are:

  • System versioning, useful for backups and auditing, where every mutating operation results in a backup copy of the data in a managed backup table.
  • Application versioning, useful for data that has a timely limited validity, such as a price for a product, which could have a temporary discount.

jOOQ 3.13 supports org.jooq.Period and related API in Select, Update, and Delete
to manipulate and query temporal tables in dialects that ship with native
support for these features. To different degrees, these dialects include:

  • DB2
  • MariaDB
  • Oracle
  • SQL Server

Emulations of incompletely supported syntax in the above dialects, as well as in
all other dialects will be added in future jOOQ versions, making temporal
validity a problem of the past.

More details here:
https://www.jooq.org/doc/3.13/manual/sql-building/table-expressions/temporal-tables/

This is a commercially licensed only feature.

Build improvements

We’ve invested a lot of time in improving our build automation using newer
Jenkins features that allow us to publish builds more frequently, especially
snapshot builds of upcoming minor releases for early adopters, as well as patch
releases for customers needing urgent fixes:

https://www.jooq.org/download/versions

These new releases also include trial versions for Java 6, 8, 11, without the
need to register for extended trials.

In addition to the above, we’ve improved and continue to improve our
documentation of features per edition, in the future.

Procedural API improvement

In jOOQ 3.12, we’ve introduced support for procedural languages, which we are
improving in every new release. jOOQ 3.13 adds support for additional
procedural statements, including:

  • EXECUTE for dynamic SQL execution embedded in procedural blocks
  • RETURN to return from functions
  • Plain SQL statement support

In the future, we’re planning to support also the generation of functions,
procedures, and triggers through standard jOOQ API.

More details here:
https://www.jooq.org/doc/3.13/manual/sql-building/procedural-statements/

This is a commercially licensed only feature.

jOOQ-Refaster

Some of our users have had a lot of success using our checker-framework or
ErrorProne integrations, which allow for some static API usage analysis in
client code (e.g. plain SQL / SQLi risk, dialect compatiblity, etc.)

Starting from jOOQ 3.13, we’re also adding support for ErrorProne’s sub project
“refaster”, which allows for automatically rewriting bad API usage to something
better, such as for example:

  • Migrate deprecated API usage like field.trim(), to its replacement trim(field)
  • Migrate inefficient SQL usage like ctx.fetchCount(select) != 0 to ctx.fetchExists(select)

The complete list of changes can be found on our website:
https://www.jooq.org/notes