The way to Write a Derived Desk in jOOQ – Java, SQL and jOOQ.

One of many extra frequent questions on jOOQ is easy methods to write a derived desk (or a CTE). The jOOQ handbook reveals a easy instance of a derived desk:

In SQL:

SELECT nested.*
FROM (
  SELECT AUTHOR_ID, depend(*) books
  FROM BOOK
  GROUP BY AUTHOR_ID
) nested
ORDER BY nested.books DESC

In jOOQ:

// Declare the derived desk up entrance:
Desk> nested =
    choose(BOOK.AUTHOR_ID, depend().as("books"))
    .from(BOOK)
    .groupBy(BOOK.AUTHOR_ID).asTable("nested");

// Then use it in SQL:
ctx.choose(nested.fields())
   .from(nested)
   .orderBy(nested.discipline("books"))
   .fetch();

And that’s just about it. The query often arises from the truth that there’s a stunning lack of sort security when working with derived tables (or CTE, which aren’t that a lot totally different). Two issues should be understood:

Not like SQL, the Java language can reference an object that hasn’t been declared but, lexically, so now we have to declare the derived desk earlier than utilizing it.
Not like generated code from the catalog, a derived desk is simply an expression, and there isn’t actually a great way so as to add attributes to this expression, primarily based on the expression’s construction, not less than not in Java. That signifies that columns of a derived desk aren’t dereferenceable in a sort protected means. You’ll be able to, nonetheless, reuse expressions, as proven under:

// Declare a discipline expression up entrance:
Subject depend = depend().as("books");

// Then use it within the derived desk:
Desk> nested =
    choose(BOOK.AUTHOR_ID, depend)
    .from(BOOK)
    .groupBy(BOOK.AUTHOR_ID).asTable("nested");

// And use it as nicely within the outer question, when dereferencing a column:
ctx.choose(nested.fields())
   .from(nested)
   .orderBy(nested.discipline(depend))
   .fetch();

Did you really want the derived desk?

Lots of instances once I reply such questions on Stack Overflow or elsewhere, it turns into obvious that the derived desk wasn’t wanted within the first place. The truth is, this very instance from the jOOQ handbook didn’t want any derived desk! The SQL question could be simplified to this:

SELECT AUTHOR_ID, depend(*) books
FROM BOOK
GROUP BY AUTHOR_ID
ORDER BY books DESC

Nothing is misplaced by this simplification. Seeing when a simplification can happen may require some apply. It’s all the time good to be conscious of the logical order of operations in SQL, to ensure the ensuing question is equal. However when it’s, then it’s a lot simpler to translate to jOOQ, as a result of now, we will use generated code once more, all over the place, and don’t should depend on the much less sort protected dereferencing of columns from derived tables. Right here’s the jOOQ equal:

// We will nonetheless assign expressions to native variables
Subject depend = depend().as("books");

// After which use them within the question:
ctx.choose(BOOK.AUTHOR_ID, depend)
   .from(BOOK)
   .groupBy(BOOK.AUTHOR_ID)
   .orderBy(depend)
   .fetch();