jOOQ 3.14 has been released with support for SQL/XML, SQL/JSON, Kotlin code generation, embeddable types, and domain types, synthetic constraints, better MERGE support, and more SQL transformations.
In this release, we’ve sorted our github issues according to user feedback and
finally implemented some of the most wanted features, which include better
Kotlin support, embeddable types, and domain type support.
In addition to this, we believe that our newly added XML and JSON operation
support will be a leading game changer in how ORMs interact with SQL databases
in the future.
XML and JSON
Standard SQL has long supported SQL/XML, and since recently, most RDBMS also
support standard SQL/JSON or vendor specific variants thereof. While ORDBMS
extensions have never seen the adoption they deserve, XML and JSON do. It makes
sense to occasionally denormalise data and store documents in the database
directly. However, this is not what we’re most excited about.
We’re excited about our support for all the fancy operators like:
- JSON_TABLE to turn JSON values into SQL tables
- JSON_ARRAY, JSON_OBJECT, JSON_VALUE to construct JSON data from values
- JSON_ARRAYAGG, JSON_OBJECTAGG to aggregate data into nested JSON documents
- JSON_EXISTS to query documents with JSON path
Similar functions are available for XML, like XMLTABLE, XMLELEMENT, XMLAGG, etc.
All editions of jOOQ 3.14 support standard SQL XML and JSON operators, and
emulate them where non-standard support is available (e.g. PostgreSQL and SQL
Server).
The commercial editions also support SQL Server’s very convenient FOR XML and
FOR JSON APIs, emulating that syntax using standard operators elsewhere. See:
But that’s not all of it! If you have Gson, Jackson, or JAXB on the classpath,
we can use that to map org.jooq.XML, org.jooq.JSON, org.jooq.JSONB types from
your query results to your nested data structures, automatically. See:
https://blog.jooq.org/2020/10/09/nesting-collections-with-jooq-3-14s-sql-xml-or-sql-json-support/
These approaches are extremely powerful. In many cases, you might just skip
most of your middleware layer and bind a REST or similar endpoint directly to a
jOOQ / SQL query, producing JSON for your frontend:
Kotlin support
We’ve long supported some Scala extensions and a ScalaGenerator. Kotlin is an
additional very promising JVM language where a new jOOQ-kotlin module as well as
a KotlinGenerator will add a lot of value for your jOOQ/Kotlin integration.
The KotlinGenerator offers, among other things:
- Data class support for generated POJOs
- Property support for generated POJOs, interfaces and records
- Better nullability support
The jOOQ-kotlin module offers some useful extension functions to further improve
the experience of the jOOQ/Kotlin integration.
In addition to the above, we’ve annotated the entire jOOQ API with nullability
annotations from JetBrains:
- org.jetbrains.annotations.Nullable
- org.jetbrains.annotations.NotNull
This will remove many of the annoying T! types in your Kotlin/jOOQ code,
turning them into T or T? types instead, giving you more confidence.
With these improvements, we’ve also critically reviewed our existing
ScalaGenerator, fixing a lot of bugs.
Embeddable types
One of the biggest new features in jOOQ 3.14 is inspired by JPA, which ships
with embeddable types. An embeddable type is an emulation of a database user-
defined type (UDT), which are supported natively only in Oracle and PostgreSQL.
The biggest gain of such types is to create more semantic, composite data types
in your database schema, and profit from the additional type safety.
Our interpretation of the feature is mostly in the source code generator, whose
output now becomes even more valuable. All jOOQ editions support the basic
infrastructure to pattern-match column sets and turn them into synthetic
embeddable types.
In addition to the above, our commercial editions offer some auto configuration
of embeddable types in cases where they really shine:
- For primary/unique constraints and their matching foreign keys
- For DOMAIN types (see below)
- Handling overlapping embeddable types
- Allowing for embeddable types to replace their underlying columns
We took the concept a step further than JPA. In jOOQ, embeddable types can act
as views on the underlying columns, without replacing them, or as a replacement
like in JPA. jOOQ respects all levels of relational modelling, including
overlapping constraints and thus allowing for two embeddable types to overlap.
For more information, please refer to:
https://www.jooq.org/doc/3.14/manual/code-generation/codegen-embeddable-types/
DOMAIN types
Speaking of types, some database dialects support standard SQL DOMAIN types,
which are a simpler form of UDTs. Instead of working with low level technical
types like VARCHAR(10), why not give your single-column types a name, and add
a few re-usable CHECK constraints to them?
That’s what a DOMAIN type is:
- A named type
- Aliasing a technical type, like VARCHAR(10)
- Possibly adding a DEFAULT expression
- Possibly adding a NOT NULL constraint
- Possibly adding a set of CHECK constraints
All of the above is reusable across your schema and if you’re commercially
licensed, you can even have the code generator auto-generate embeddable types
for all of your domains to profit from the additional type safety in Java.
For more information, please refer to:
- https://www.jooq.org/doc/3.14/manual/code-generation/codegen-domains/
- https://www.jooq.org/doc/3.14/manual/code-generation/codegen-embeddable-types/codegen-embedded-domains/
Synthetic constraints
Related to the above improved code generator output are synthetic objects,
such as the previously supported synthetic primary keys, and now also synthetic
unique and foreign keys.
If you invest heavily in security and re-usable components within your database,
you will make heavy use of SQL views. Unfortunately, views do not have any meta
data like foreign key constraints – despite the meta data being “obvious” to you
the database designer. With synthetic constraints, you can tell jOOQ about your
knowledge of underlying constraints.
The main benefits of meta data being available to jOOQ being:
- You can now use implicit joins on views as well
- You can now use the JOIN .. ON KEY syntax on views as well
- You can use embeddable key types from before on views just like on tables
For more information, please refer to:
- https://www.jooq.org/doc/3.14/manual/sql-building/sql-statements/select-statement/implicit-join/
- https://www.jooq.org/doc/3.14/manual/code-generation/codegen-advanced/codegen-config-database/codegen-database-synthetic-objects/
Better MERGE support
We’ve finally tackled support for more advanced MERGE statement clauses and now
support:
- Multiple WHEN MATCHED AND condition THEN UPDATE clauses
- Multiple WHEN MATCHED AND condition THEN DELETE clauses
- UpdatableRecord.merge() and all the related goodies to simplify record merging
Transformations
With the parser and our translator tool (https://www.jooq.org/translate), we’ll
invest more and more in new use-cases for putting jOOQ to use other than as an
embeddable DSL in Java.
Our translation capabilities have already been strong, and with a new set of SQL
transformations, they become even stronger helping customers migrate off RDBMS A
towards RDBMS B (and back, if they made a mistake).
While our website translator is free of charge, the jOOQ library can always be
used programmatically, or as a command line utility:
https://www.jooq.org/doc/3.14/manual/sql-building/sql-parser/sql-parser-cli/
To make this use-case even more useful, new transformation features have been
added, including:
- ROWNUM to LIMIT or to ROW_NUMBER()
- Table lists to ANSI JOIN (including Oracle (+) support)
- Unnecessary arithmetic expressions
This is an exciting area that we’ll be exploring for our commercially licensed
customers in the future, while even the jOOQ Open Source Edition profits from
these improvements. For example, the infrastructure created for transformations
finally enabled emulating PostgreSQL’s DISTINCT ON clause, elsewhere.
For more information, please refer to:
- https://www.jooq.org/doc/3.14/manual/sql-building/queryparts/sql-transformation/
- https://github.com/jOOQ/jOOQ/issues/10054 (future work)
Better manual
We’ve taken a step back and reviewed a few important parts of our documentation.
We’re now offering:
- Sticky tabs for code generator techniques (XML, Programmatic, Gradle): If
you’re using Gradle with jOOQ’s code generator, you don’t want to look at the
XML configuration again, in the manual. These tabs finally hide unneeded
information. - Subsections for each function: We’ve started documenting each SQL function
individually, explaining how it works in SQL, and providing some examples and
example results. - Generate example vendor specific rendering of SQL: We’re using jOOQ when
generating the manual, translating some jOOQ API usage to all of our supported
dialects, and displaying how the function renders in each dialect. - Show imports button and display context sensitive imports: All the examples in
the manual can be overwhelming. We’re assuming a lot of (static) imports,
which we’re finally documenting in an expandable “show imports” section of
each code fragment. - We’ve rewritten some sections to be much more complete with examples, such as
the data import section. - A new API diff page displays what has changed between each minor release in
terms of list of Javadoc links: https://www.jooq.org/api-diff
from Java, SQL and jOOQ. https://ift.tt/3dKpug8
via IFTTT
No comments:
Post a Comment