jOOQ 3.10 Supports Exciting MySQL 8.0 Features

Posted on by lukaseder
In recent months, there had been some really exciting news from the MySQL team: These two SQL standard language features are among the most powerful SQL features that are available from most other databases. I frequently include them in conference talks about SQL (see my article about 10 SQL Tricks That You Didn’t Think Were Possible), and as well in the Data Geekery SQL Masterclass. With MySQL 8.0 now supporting these exciting features, the masterclass will be including MySQL as well (along with Oracle, SQL Server, PostgreSQL, and DB2). And, of course, these features are now supported in the upcoming jOOQ 3.10 as well. Want to try it out yourself? Just run: 
docker pull mysql:8.0.2
docker run --name MYSQL802 --net=host -p 3306:3306 -e MYSQL_ROOT_PASSWORD=test -d mysql:8.0.2
Then, connect to this instance and run this nice little query in it: 

WITH RECURSIVE t(a, b) AS (
  SELECT 1, CAST('a' AS CHAR(15))
  UNION ALL
  SELECT t.a + 1, CONCAT(t.b, 'a')
  FROM t
  WHERE t.a < 10
)
SELECT a, SUM(a) OVER (ORDER BY a) AS ∑, b
FROM t
And get this result: 
a       ∑       b
--------------------------
1       1       a
2       3       aa
3       6       aaa
4       10      aaaa
5       15      aaaaa
6       21      aaaaaa
7       28      aaaaaaa
8       36      aaaaaaaa
9       45      aaaaaaaaa
10      55      aaaaaaaaaa
Would you believe this is MySQL?

Bonus

A nice “hidden” feature is the support of new pessimistic locking clauses, in particular FOR UPDATE SKIP LOCKED. This has been available in Oracle for ages and since recently in PostgreSQL as well, and now in MySQL. A very useful feature when implementing simple message queues or reservation systems. More details in this article here:
MySQL 8.0.1: Using SKIP LOCKED and NOWAIT to handle hot rows
Of course, SKIP LOCKED (and NOWAIT) will be supported in jOOQ 3.10 as well.

Published by lukaseder

I made jOOQ

4 thoughts on “jOOQ 3.10 Supports Exciting MySQL 8.0 Features”

  1. Hi, thank you for sharing.

    How do you express this query in jooq ?

    WITH RECURSIVE t(a, b) AS (
  SELECT 1, CAST('a' AS CHAR(15))
  UNION ALL
  SELECT t.a + 1, CONCAT(t.b, 'a')
  FROM t
  WHERE t.a < 10
)
SELECT a, SUM(a) OVER (ORDER BY a) AS ∑, b
FROM t
    Reply

    1. Like this:

      Field<Integer> a = field(name("a"), INTEGER);
Field<Integer> b = field(name("b"), INTEGER);
CommonTableExpression<Record2<Integer, String>> t =
name("t").fields("a", "b").as(
    select(
        inline(1),
        inline("a").cast(VARCHAR(15))
    )
    .unionAll(
        select(
            a.add(inline(1)),
            b.concat(inline("a")).cast(VARCHAR(15))
        )
        .from(table(name("t")))
        .where(a.lt(inline(10)))
    )
);

Result result=
create().withRecursive(t)
        .select()
        .from(t)
        .fetch();

      The usual static imports are assumed here:

      import static org.jooq.impl.DSL.*;
import static org.jooq.impl.SQLDataType.*;

      Note, there’s a known issue to get this working on MySQL: https://github.com/jOOQ/jOOQ/issues/6431

      Reply

      1. The variables a and b are not defined in the code sample. How to define them so that they reference t?

        Reply

Leave a Reply