jOOQ 3.11 Released With 4 New Databases, Implicit Joins, Diagnostics, and Much More

Spread the love
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  
  •  

Today, jOOQ 3.11 has been released with support for 4 new databases, implicit joins, diagnostics, and much more
New Databases Supported
At last, 4 new SQL dialects have been added to jOOQ! These are:
jOOQ Professional Edition
Aurora MySQL Edition
Aurora PostgreSQL Edition
Azure SQL Data Warehouse
jOOQ Enterprise Edition
Teradata
Implicit Joins
One of the really cool features in ORMs like Hibernate, Doctrine, and others, is
the capability of using a relationship graph notation to access another entity’s
columns through what is often called “implicit joins”.
Instead of explicitly joining a to-one relationship to access its columns:

SELECT author.first_name, author.last_name, book.title
FROM book
JOIN author ON book.author_id = author.id

We would like to be able to access those columns directly, using this notation:

SELECT book.author.first_name, book.author.last_name, book.title
FROM book

The join is implied and should be added implicitly. jOOQ now allows for this to
happen when you use the code generator:

ctx.select(BOOK.author().FIRST_NAME, BOOK.author().LAST_NAME, BOOK.TITLE)
.from(BOOK)
.fetch();

When rendering this query, the implicit join graph will be calculated on the fly
and added behind the scenes to the BOOK table. This works for queries of
arbitrary complexity and on any level of nested SELECT.
More details in this blog post:https://blog.jooq.org/2018/02/20/type-safe-implicit-join-through-path-navigation-in-jooq-3-11/
DiagnosticsListener SPI
A new DiagnosticsListener SPI has been added to jOOQ:https://github.com/jOOQ/jOOQ/issues/5960
The purpose of this SPI is to sanitise your SQL language, JDBC and jOOQ API
usage. Listeners can listen to events such as:
duplicateStatements (similar SQL is executed, bind variables should be used)
repeatedStatements (identical SQL is executed, should be batched or rewritten)
tooManyColumnsFetched (not all projected columns were needed)
tooManyRowsFetched (not all fetched rows were needed)
The great thing about this SPI is that it can be exposed to clients through the
JDBC API, in case of which the diagnostics feature can reverse engineer your
JDBC or even JPA generated SQL. Ever wanted to detect N+1 queries from
Hibernate? Pass those Hibernate-generated queries through this SPI.
Want to find missing bind variables leading to cursor cache contention or SQLi?
Let jOOQ find similar SQL statements and report them. E.g.
SELECT name FROM person WHERE id = 1
SELECT name FROM person WHERE id = 2
Or also:
SELECT name FROM person WHERE id IN (?, ?)
SELECT name FROM person WHERE id IN (?, ?, ?)
Anonymous blocks
Many databases support anonymous blocks to run several statements in a single
block scope. For example, Oracle:

DECLARE
l_var NUMBER(10);
BEGIN
l_var := 10;
dbms_output.put_line(l_var);
END;

jOOQ now supports the new org.jooq.Block API to allow for wrapping DDL and DML
statements in such a block. This is a first step towards a future jOOQ providing
support for:
Abstractions over procedural languages
CREATE PROCEDURE and CREATE FUNCTION statements
Trigger support
And much more
Parser
jOOQ’s parser support is an ongoing effort. This release has added support for
a lot of new SQL clauses and functions from various vendors and in various DDL
and DML statements.
The parser is now also exposed through a public website and API, where SQL can
be translated from one dialect to another:https://www.jooq.org/translate
This website will help further drive jOOQ API development by helping to find
missing functionality that is used in real-world SQL.
Another way to access this API is through the new org.jooq.ParserCLI command
line tool. For example, run:

$ java -cp jooq-3.11.0.jar org.jooq.ParserCLI -f -t ORACLE -s “SELECT * FROM (VALUES(1),(2)) AS t(a)”

To get:

select *
from (
(
select null a
from dual
where 1 = 0
)
union all (
select *
from (
(
select 1
from dual
)
union all (
select 2
from dual
)
) t
)
) t;

Formal Java 10 Support
jOOQ 3.11 is the first release that is formally integration tested with Java 10.
To use jOOQ with Java 10, use the Java 8 distribution which has not yet been
modularised, but contains Automatic-Module-Name specification to be forward
compatible with future, modularised jOOQ distributions.
Additionally, package names between jOOQ, jOOQ-meta, and jOOQ-codegen have been
cleaned up to prevent duplicate package names, and the JAXB dependency has been
added explicitly to the various artefacts.
Other great improvements
Finally, asterisks (SELECT * or SELECT t.*) are formally supported in the API.
Collations can now be specified on a variety of syntax elements
The org.jooq.Comment type has been added, and DDL statements for it
The DefaultBinding implementation has been rewritten for better peformance
Several performance improvements in jOOQ’s internals
Many more DDL statements are supported including GRANT and REVOKE
Support for the EXPLAIN statement
FETCH n PERCENT ROWS and TOP n PERCENT clauses are supported
Better org.jooq.Name and org.jooq.Named API for identifier handling
Support for PostgreSQL 10
Support for SQL Server 2017
Support for DB2 11
Upgraded MariaDB support for window functions, inv dist functions, WITH
jOOU dependency updated to 0.9.3
jOOR dependency updated to 0.9.8
Server output (e.g. DBMS_OUTPUT) can now be fetched automatically, by jOOQ
Code generation support for PL/SQL TABLE types
SQL Keywords Can Now Be Rendered In Pascal Style If You Must
Emulate PostgreSQL’s ON CONFLICT clause using MERGE
The complete list can be seen here:https://www.jooq.org/notes/?version=3.11

X ITM Cloud News

Emily

Next Post

How to Patch Your IDE to Fix an Urgent Bug

Sun Nov 24 , 2019
Spread the love          Clock’s ticking. JDK 11 will remove a bunch of deprecated modules through JEP 320, which includes the Java EE modules, which again includes JAXB, a dependency of many libraries, including jOOQ. Thus far, few people have upgraded to Java 9 or 10, as these aren’t LTS releases. Unlike […]
X- ITM

Cloud Computing – Consultancy – Development – Hosting – APIs – Legacy Systems

X-ITM Technology helps our customers across the entire enterprise technology stack with differentiated industry solutions. We modernize IT, optimize data architectures, and make everything secure, scalable and orchestrated across public, private and hybrid clouds.

This image has an empty alt attribute; its file name is x-itmdc.jpg

The enterprise technology stack includes ITO; Cloud and Security Services; Applications and Industry IP; Data, Analytics and Engineering Services; and Advisory.

Watch an animation of  X-ITM‘s Enterprise Technology Stack

We combine years of experience running mission-critical systems with the latest digital innovations to deliver better business outcomes and new levels of performance, competitiveness and experiences for our customers and their stakeholders.

X-ITM invests in three key drivers of growth: People, Customers and Operational Execution.

The company’s global scale, talent and innovation platforms serve 6,000 private and public-sector clients in 70 countries.

X-ITM’s extensive partner network helps drive collaboration and leverage technology independence. The company has established more than 200 industry-leading global Partner Network relationships, including 15 strategic partners: Amazon Web Services, AT&T, Dell Technologies, Google Cloud, HCL, HP, HPE, IBM, Micro Focus, Microsoft, Oracle, PwC, SAP, ServiceNow and VMware

.

X ITM