Find out how to Write a Derived Desk in jOOQ – Java, SQL and jOOQ.

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

In SQL:

SELECT nested.*
FROM (
  SELECT AUTHOR_ID, rely(*) 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, rely().as("books"))
    .from(BOOK)
    .groupBy(BOOK.AUTHOR_ID).asTable("nested");

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

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

1. Not like SQL, the Java language can reference an object that hasn’t been declared but, lexically, so we’ve to declare the derived desk earlier than utilizing it.
2. 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, based mostly 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 method. You’ll be able to, nonetheless, reuse expressions, as proven beneath:

// Declare a subject expression up entrance:
Area<Integer> rely = rely().as("books");

// Then use it within the derived desk:
Desk<?> nested =
    choose(BOOK.AUTHOR_ID, rely)
    .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.subject(rely))
   .fetch();

Did you actually need the derived desk?

Plenty of occasions after I reply such questions on Stack Overflow or elsewhere, it turns into obvious that the derived desk wasn’t wanted within the first place. In actual fact, this very instance from the jOOQ guide didn’t want any derived desk! The SQL question might be simplified to this:

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

Nothing is misplaced by this simplification. Seeing when a simplification can happen would possibly 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, in all places, and don’t need to depend on the much less kind protected dereferencing of columns from derived tables. Right here’s the jOOQ equal:

// We are able to nonetheless assign expressions to native variables
Area<Integer> rely = rely().as("books");

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

Conclusion

So, once you work with jOOQ and your question is sufficiently easy, then your query of

Find out how to write a derived desk in jOOQ?

Is perhaps modified to:

Did I want a derived desk within the first place?

That method, you may enhance each your jOOQ question and your SQL question