jooq

Best way to build dynamic SQL involving an optional limit?

What is the best way to optionally apply a LIMIT to a query in JOOQ? I want to run:

SelectSeekStepN<Record> readyToFetch = dslContext.select(selectFields).
    from(derivedTable).
    where(conditions).
    orderBy(orderForward);
if (length != Integer.MAX_VALUE)
    readyToFetch = readyToFetch.limit(length);

limit() returns SelectLimitPercentStep<Record> which is not a sub-class of SelectSeekStepN<Record> so I get a compiler error.

If, on the other hand, I change the return type of readyToFetch from SelectSeekStepN<Record> to Select<Record> which is compatible with the return type of limit() then I cannot invoke limit() on Select<Record>. I would need to explicitly cast it to SelectSeekStepN<Record>.

Is there a better way to do this?

Maybe JOOQ should treat Integer.MAX_VALUE as a special value (no limit) to make this kind of code easier to write...

Solution

  • Offering a no-op to pass to clauses like LIMIT

    There's a dummy Field expression that can be created using DSL.noField(DataType), for example, as documented here

     dslContext.select(selectFields).
    from(derivedTable).
    where(conditions).
    orderBy(orderForward).
    limit(length != Integer.MAX_VALUE ? length : noField(INTEGER)).
    fetch();

    Getting the types right with dynamic SQL

    Your own question already contains the solution. It's just a minor typing problem. You probably chose to assign your intermediary step to SelectSeekStepN because your IDE suggested this type. But you can use any super type, instead.

    Select<Record> readyToFetch;
SelectLimitStep<Record> readyToLimit;

readyToFetch = readyToLimit = dslContext.select(selectFields).
    from(derivedTable).
    where(conditions).
    orderBy(orderForward);
if (length != Integer.MAX_VALUE)
    readyToFetch = readyToLimit.limit(length);
readyToFetch.fetch();

    You can take some inspiration by the ParserImpl logic. It does this all over the place. Assignment expressions are a blessing!

    Alternative using type inference on conditional expressions:

    SelectLimitStep<Record> limit = dslContext.select(selectFields).
    from(derivedTable).
    where(conditions).
    orderBy(orderForward);

Result<?> result = (length != Integer.MAX_VALUE ? limit.limit(length) : limit).fetch();

    Using null as a way to explicitly indicate the absence of LIMIT

    Using null as a way to indicate the absence of a LIMIT is a very bad idea for at least 3 reasons: