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

on

|

views

and

comments

[ad_1]

One of many extra frequent questions on jOOQ is write a derived desk (or a CTE). The jOOQ guide exhibits 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.area("books"))
   .fetch();

And that’s just about it. The query normally 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 completely different). Two issues must be understood:

  1. Not like SQL, the Java language can reference an object that hasn’t been declared but, lexically, so we have now 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, a minimum of not in Java. That implies that columns of a derived desk aren’t dereferenceable in a sort protected method. You possibly can, nonetheless, reuse expressions, as proven beneath:
// Declare a area expression up entrance:
Area<Integer> 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.area(depend))
   .fetch();

Did you actually need the derived desk?

Loads 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 may 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 would possibly require some follow. 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 must depend on the much less kind protected dereferencing of columns from derived tables. Right here’s the jOOQ equal:

// We will nonetheless assign expressions to native variables
Area<Integer> 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();

Conclusion

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

The best way to write a derived desk in jOOQ?

Is perhaps modified to:

Did I would like a derived desk within the first place?

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

[ad_2]

Supply hyperlink

Share this
Tags

Must-read

Google Presents 3 Suggestions For Checking Technical web optimization Points

Google printed a video providing three ideas for utilizing search console to establish technical points that may be inflicting indexing or rating issues. Three...

A easy snapshot reveals how computational pictures can shock and alarm us

Whereas Tessa Coates was making an attempt on wedding ceremony clothes final month, she posted a seemingly easy snapshot of herself on Instagram...

Recent articles

More like this

LEAVE A REPLY

Please enter your comment!
Please enter your name here