Best way to build dynamic SQL involving an optional limit?

Question

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...

Answer 1

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:

• Most jOOQ API interprets (Field<?>) null as a NULL bind value or NULL literal, never as an absent value. It would be very surprising if suddenly, we used null for that purpose only in LIMIT
• Even if we did, we'd have to start distinguishing between null (the internal interpretation of an absent value) and null (the value you as a user provide jOOQ with explicitly). So, we'd need some noLimit() object anyway internally, to make the distinction, in case of which, why not just expose that as API instead of letting you hack around?
• Some dialects support NULL limits. PostgreSQL interprets it an absent LIMIT (which I find very confusing, the LIMIT being "unknown"). Oracle interprets it as a LIMIT 0, which is much more reasonable. Other dialects (e.g. MySQL) reject LIMIT NULL as bad syntax, which is also reasonable. You're suggesting jOOQ overrides this behaviour and cleverly re-interprets it. I'd rather not!