jOOQ Statement to fetch all references of a dataset

user_9337251 0
Viewed 213

I'm (very!) new to jOOQ and I want to write a statement that gets me the number off all references of a certain id in different tables.

So for example I have a dataset from the table book of the schema book (-> book.book) with the PK 45. This books PK is referred in different tables in different schemas:

  • table author in schema author (-> author.author)
  • table publisher in schema publisher (-> publisher.publisher)
  • table series in schema series (-> series.series)

I tried to use UNION ALL to combine the different results but after hours trying around I still just cant get the hang of it. Especially with the via the "as()" function created field "cnt". This is what I have so far:

final Result<T> record = context
            .select(DSL.sum())
            .from(
                context
                    .select(DSL.count(AUTHOR_TABLE.BOOK_REF).as("cnt"))
                    .from(AUTHOR_TABLE)
                    .where(AUTHOR_TABLE.BOOK_REF.eq(bookRef))
                    .unionAll(
                        context
                            .select(DSL.count(PUBLISHER_TABLE.BOOK_REF))
                            .from(PUBLISHER_TABLE)
                            .where(PUBLISHER_TABLE.BOOK_REF.eq(bookRef))
                    )
                    .unionAll(
                        context
                            .select(DSL.count(SERIES_TABLE.BOOK_REF))
                            .from(SERIES_TABLE)
                            .where(SERIES_TABLE.BOOK_REF.eq(bookRef))
                    )
            )
            .fetchOne();

My SQL statement that I used as a reference is working just fine and looks like that:

     SELECT SUM(cnt) FROM (
 SELECT COUNT(*) as cnt FROM author.author WHERE book_ref = 45
 UNION ALL
 SELECT COUNT(*) FROM publisher.publisher WHERE book_ref = 45
 UNION ALL
 SELECT COUNT(*) FROM series.series WHERE book_ref = 45
) a

Can anyone help me to complete this statement? Thanks in advance :)

jooq union postgresql reference java
1 Answers

An easier way to solve this

You don't need the derived table with unions, which is correct but a bit more complicated than it needs to be, both in SQL and in jOOQ. A simpler approach would be to use scalar subqueries. I'd do it like this:

SELECT
  (SELECT count(*) FROM author.author WHERE book_ref = 45)
+ (SELECT count(*) FROM publisher.publisher WHERE book_ref = 45)
+ (SELECT count(*) FROM series.series WHERE Book_ref = 45)

With jOOQ:

int sum =
context.select(
  field(select(count())
    .from(AUTHOR_TABLE)
    .where(AUTHOR_TABLE.BOOK_REF.eq(bookRef)))
  .plus(field(select(count())
    .from(PUBLISHER_TABLE)
    .where(PUBLISHER_TABLE.BOOK_REF.eq(bookRef))))
  .plus(field(select(count())
    .from(SERIES_TABLE)
    .where(SERIES_TABLE.BOOK_REF.eq(bookRef))))
).fetchOne().value1();

As always, this is assuming the following static import:

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

To import both DSL.field(Select<? extends Record1<T>>) (to turn a query into a scalar subquery) and DSL.select() to create a subquery, which isn't attached to the context

How to get the derived table / union approach to work

Due to the nature of jOOQ's DSL, it's not always straightforward to create derived tables or CTE. Unlike in SQL, where identifiers can be defined in subqueries and referenced from the outer query, in jOOQ (which has to follow the rules of the Java language), all identifiers need to be declared up front.

So, the type safe way to do your query would be this:

// Declare this expression up fron to reference it multiple times
Field<Integer> cnt = count(AUTHOR_TABLE.BOOK_REF).as("cnt");

// Declare the subquery up front, if you want to dereference a column from it:
Table<?> subquery = table(
    select(cnt)
    .from(AUTHOR_TABLE)
    .where(AUTHOR_TABLE.BOOK_REF.eq(bookRef))
    .unionAll(
        select(count(PUBLISHER_TABLE.BOOK_REF))
        .from(PUBLISHER_TABLE)
        .where(PUBLISHER_TABLE.BOOK_REF.eq(bookRef))
    )
    .unionAll(
        select(count(SERIES_TABLE.BOOK_REF))
        .from(SERIES_TABLE)
        .where(SERIES_TABLE.BOOK_REF.eq(bookRef))
    )
);

// Option 1, work with the unqualified alias only, which works in this case
Result<?> record1 = context
            .select(sum(cnt))
            .from(subquery)
            .fetchOne();

// Option 2, dereference a.cnt, the qualified alias, which is better in general
Result<?> record2 = context
            .select(sum(subquery.field(cnt)))
            .from(subquery)
            .fetchOne();

The third, pragmatic option, would be to just create an unsafe expression like this to get a handle for the a.cnt column reference, without the formal connection illustrated above:

// Option 3, pragmatic approach:
Result<?> record2 = context
            .select(sum(field(name("cnt"), SQLDataType.INTEGER)))
            .from(...)
            .fetchOne();

See also this section of the manual.

Using views

It's always good to note that while jOOQ is excellent when it comes to offering type safe dynamic embedded SQL functionality, you don't always have to use jOOQ for every query. From a jOOQ perspective, if you move this particular query into your database, e.g. using a view or stored function, that works just as well, and you can use the generated object for that view or stored function instead.

user_5217990
Related