One of many extra frequent questions on jOOQ is how one can write a derived desk (or a CTE). The jOOQ guide exhibits a easy instance of a derived desk:
SELECT nested.* FROM ( SELECT AUTHOR_ID, depend(*) books FROM BOOK GROUP BY AUTHOR_ID ) nested ORDER BY nested.books DESC
// 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 totally different). Two issues should be understood:
- In contrast to 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.
- In contrast to 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, at the least not in Java. That signifies that columns of a derived desk aren’t dereferenceable in a kind secure method. You may, nonetheless, reuse expressions, as proven beneath:
// Declare a area expression up entrance: Discipline<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 effectively within the outer question, when dereferencing a column: ctx.choose(nested.fields()) .from(nested) .orderBy(nested.area(depend)) .fetch();
Did you really want the derived desk?
Quite a lot of instances 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 may require some observe. It’s at all times 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 are able to use generated code once more, in all places, and don’t should depend on the much less kind secure dereferencing of columns from derived tables. Right here’s the jOOQ equal:
// We will nonetheless assign expressions to native variables Discipline<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();
So, whenever you work with jOOQ and your question is sufficiently easy, then your query of
write a derived desk in jOOQ?
Is likely to be 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